用Excel VBA實(shí)現(xiàn)物業(yè)收據(jù)的打印

代遠(yuǎn)大+鐘鳴+李建平

摘要：以一覽表形式逐行建立的數(shù)據(jù)記錄，便于集中瀏覽。但在實(shí)際工作中，通常要以記錄為單位，按預(yù)定的格式，進(jìn)行獨(dú)立頁面打印輸出。利用Excel VBA編寫簡單程序代碼，可以輕松實(shí)現(xiàn)。

關(guān)鍵詞：Excel VBA；數(shù)據(jù)一覽表；物業(yè)收據(jù)；打印

中圖分類號：TP37 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號：1009-3044（2017）35-0227-02

當(dāng)前，物業(yè)管理行業(yè)的收費(fèi)名目繁多，收據(jù)格式五花八門，沒有通用的收據(jù)打印軟件。部分物業(yè)公司還在采用手工填寫方式，勞神費(fèi)時(shí)，容易出錯(cuò)；有的使用從網(wǎng)上下載的免費(fèi)軟件，感覺又不能滿足公司的特殊需要，用起來不順手。面對這種狀況，如何利用現(xiàn)有應(yīng)用軟件，設(shè)計(jì)滿足自身需要的物業(yè)收據(jù)打印系統(tǒng)，是一個(gè)值得探討的問題。

1 需求分析

各物業(yè)小區(qū)業(yè)主收費(fèi)信息，以一覽表的形式分別存放在各個(gè)工作表中，每個(gè)業(yè)主的數(shù)據(jù)形成一行記錄，需要實(shí)現(xiàn)以下功能：

可以按預(yù)定的格式，分別不同的物業(yè)小區(qū)，向前、向后逐個(gè)瀏覽各業(yè)主的物業(yè)費(fèi)用信息，對顯示的當(dāng)前業(yè)主物業(yè)收據(jù)進(jìn)行打印，對指定范圍內(nèi)的業(yè)主物業(yè)收據(jù)進(jìn)行批量的、連續(xù)的打印。

2 開發(fā)環(huán)境

Microsoft Office Excel 2007中文版。

3 設(shè)計(jì)過程

1） 新建一個(gè)Excel2007工作簿文檔，保存為啟用宏的工作簿“Property.xlsm”。

2） 新建兩個(gè)工作表，表標(biāo)簽名稱分別以各物業(yè)小區(qū)名稱命名，分別為“白鶴印象”、“蔚藍(lán)天空”，用于存放兩個(gè)物業(yè)小區(qū)收費(fèi)信息，表列內(nèi)容包含房號、姓名、水電氣數(shù)據(jù)、物業(yè)費(fèi)、其它等（如圖1所示）。

3） 新建一個(gè)工作表，表標(biāo)簽名稱為“收據(jù)打印”（如圖2所示），用于瀏覽顯示、打印輸出預(yù)定格式物業(yè)收據(jù)。將工作表上部的B2：L11單元格區(qū)域設(shè)置為打印區(qū)域，下部的B12：L13 單元格區(qū)域?yàn)榇蛴】刂茀^(qū)，其內(nèi)容不打印輸出。

4） 對“收據(jù)打印“工作表物業(yè)小區(qū)后邊的D4單元格進(jìn)行數(shù)據(jù)有效性設(shè)置：在允許下拉列表框中選擇“序列”，在來源下面的文本框中輸入“白鶴印象，蔚藍(lán)天空”。

5） 對“收據(jù)打印”工作表的相關(guān)單元格設(shè)置公式：

No. K3： =YEAR（TODAY（））&MONTH（TODAY（）） （將當(dāng)前年月自動(dòng)冠于收據(jù)編號前）

L3： =$F$12 （將當(dāng)前記錄號自動(dòng)作為收據(jù)編號）

房號G4： =OFFSET（INDIRECT（$D$4&"！A4"），$F$12，）

姓名J4：=OFFSET（INDIRECT（D4&"！B4"），$F$12，）

水表上月讀數(shù)E6：=OFFSET（INDIRECT（D4&"！C4"），$F$12，）

電表上月讀數(shù)E7：=OFFSET（INDIRECT（D4&"！F4"），$F$12，）

氣表上月讀數(shù)E8：=OFFSET（INDIRECT（D4&"！I4"），$F$12，）

水表本月讀數(shù)G6：=OFFSET（INDIRECT（D4&"！D4"），$F$12，）

電表本月讀數(shù)G7：=OFFSET（INDIRECT（D4&"！G4"），$F$12，）

氣表本月讀數(shù)G8：=OFFSET（INDIRECT（D4&"！J4"），$F$12，）

水實(shí)用數(shù)I6= =G6-E6

電實(shí)用數(shù)I7= =G7-E7

氣實(shí)用數(shù)I8= =G8-E8

水單價(jià)K6： =OFFSET（INDIRECT（D4&"！E4"），$F$12，）

電單價(jià)K7： = OFFSET（INDIRECT（D4&"！H4"），$F$12，）

氣單價(jià)K8： = =OFFSET（INDIRECT（D4&"！K4"），$F$12，）

水費(fèi)L6： =ROUND（I6*K6，2）

電費(fèi)L7： =ROUND（I7*K7，2）

氣費(fèi)L8： =ROUND（I8*K8，2）

物業(yè)費(fèi)E9： =OFFSET（INDIRECT（D4&"！L4"），$F$12，）

其他費(fèi)I9：=OFFSET（INDIRECT（D4&"！M4"），$F$12，）

物業(yè)費(fèi)與其他費(fèi)小計(jì)L9： =E9+I9

收據(jù)總金額（小寫）L10：=SUM（L6：L9）

收據(jù)總金額（大寫）H10： =SUBSTITUTE（SUBSTITUTE（TEXT（TRUNC（FIXED（L10）），"[>0][dbnum2]；[<0]負(fù)[dbnum2]；；"）&TEXT（RIGHT（FIXED（L10），2），"元[dbnum2]0角0分；；"&IF（ABS（L10）>1%，"元整"，）），"零角"，IF（ABS（L10）<1，，"零"）），"零分"，"整"）

制票日期F11：=YEAR（TODAY（））&"年"&MONTH（TODAY（））&"月"&DAY（TODAY（））&"日"

6） 在“收據(jù)打印”工作表中添加一個(gè)數(shù)值控件（用于控制選擇當(dāng)前記錄號），右擊控件，在菜單中選擇“設(shè)置控件格式”，在設(shè)置窗口中，將當(dāng)前值、最小值、步長值均設(shè)置為1，最大值根據(jù)需要設(shè)為足夠大的一個(gè)整數(shù)（以保證能控制各物業(yè)小區(qū)的最多業(yè)主數(shù)量），單元格鏈接為$F$12（與當(dāng)前記錄號單元格建立關(guān)聯(lián)）。當(dāng)按下控件的向上、向下箭頭按鈕時(shí)，當(dāng)前記錄號單元格$F$12值發(fā)生加1、減1的變化，引發(fā)與之相關(guān)的所有公式單元格的值發(fā)生變化，形成聯(lián)動(dòng)效應(yīng)，從各物業(yè)信息工作表中適時(shí)獲取對應(yīng)的數(shù)據(jù)，顯示在“收據(jù)打印”工作表的單元格中。endprint

7） 在”收據(jù)打印”工作表中添加兩個(gè)按鈕控件，標(biāo)題分別為“打印當(dāng)前頁”、“打印指定頁”

8） 為“打印當(dāng)前頁”按鈕指定宏，名稱為PrintCurPage_click，用VBA編寫以下代碼：

Sub PrintCurPage_Click（）

n = MsgBox（"確定打印當(dāng)前業(yè)主收據(jù)嗎？"， vbOKCancel）

If n = vbOK Then ActiveSheet.PrintOut

End Sub

9） 為“打印指定頁”按鈕指定宏，名稱為PrintRange_click，用VBA編寫以下代碼：

Sub PrintRange_Click（）

startNum = Range（"F13"）

endNum = Range（"H13"）

'——-起始頁員小于或等于終止頁號時(shí)進(jìn)行打印，否則提示錯(cuò)誤信息后退出

If startNum <= endNum Then

n = MsgBox（"確定打印第 " & startNum & "～" & endNum & " 頁號嗎？"， vbOKCancel）

If n = vbOK Then

Range（"F12"） = startNum '——-將當(dāng)前頁號設(shè)置為指定的初始頁號

'——-開始從指定頁號到終止頁號的循環(huán)打印

For i = startNum To endNum

ActiveSheet.PrintOut '——-打印當(dāng)前記錄

Range（"F12"） = Range（"F12"） + 1 '——-將下一記錄轉(zhuǎn)為當(dāng)前記錄

Next i

End If

Else

MsgBox "錯(cuò)誤：起始頁號應(yīng)小于或等于終止頁號，請重新輸入！"

End If

End Sub

10） 將“收據(jù)打印”工作表中當(dāng)前頁號、指定頁F12、F13、H13以外的全部單元格鎖定，并對該工作表設(shè)置保護(hù)，防止對其它非編輯單元格（特別是打印區(qū)域單元格）的誤操作。

4 運(yùn)行測試

在“收據(jù)打印”工作表中，點(diǎn)擊物業(yè)小區(qū)D4右邊的下拉箭頭，可以對物業(yè)小區(qū)進(jìn)行切換顯示。點(diǎn)按數(shù)值控件的向上、向下箭頭，分別向前、向后翻頁顯示各業(yè)主的物業(yè)收據(jù)。接通打印機(jī)，點(diǎn)擊“打印當(dāng)前頁”按鈕，對當(dāng)前業(yè)主的物業(yè)收據(jù)進(jìn)行了正確的打印輸出；輸入起始記錄號和終止記錄號，點(diǎn)擊“打印指定頁”按鈕，指定范圍內(nèi)的全部業(yè)主收據(jù)實(shí)現(xiàn)了批量的、連續(xù)的打印輸出。運(yùn)行測試正常，滿足設(shè)計(jì)需求。

5 設(shè)計(jì)結(jié)論

在Excel工作表中，以一覽表形式存儲(chǔ)的數(shù)據(jù)記錄，可以進(jìn)行按預(yù)定格式的顯示和打印輸出。利用Excel VBA 編寫簡單的代碼，可以輕松實(shí)現(xiàn)復(fù)雜邏輯操作過程的批處理，極大地提高工作效率。

參考文獻(xiàn)：

[1] 黃海. Excel公式函數(shù)圖表VBA一本通[M].北京：中國青年出版社，2008.

[2] 姚文濤.Excel VBA 應(yīng)用開發(fā)經(jīng)典實(shí)例[M].北京：清華大學(xué)出版社，2009.

[3] Excel Home. Excel2007應(yīng)用大全[M].北京：人民郵電出版社，2012.endprint