用Excel VBA實現物業收據的打印

代遠大+鐘鳴+李建平

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

關鍵詞：Excel VBA；數據一覽表；物業收據；打印

中圖分類號：TP37 文獻標識碼：A 文章編號：1009-3044（2017）35-0227-02

當前，物業管理行業的收費名目繁多，收據格式五花八門，沒有通用的收據打印軟件。部分物業公司還在采用手工填寫方式，勞神費時，容易出錯；有的使用從網上下載的免費軟件，感覺又不能滿足公司的特殊需要，用起來不順手。面對這種狀況，如何利用現有應用軟件，設計滿足自身需要的物業收據打印系統，是一個值得探討的問題。

1 需求分析

各物業小區業主收費信息，以一覽表的形式分別存放在各個工作表中，每個業主的數據形成一行記錄，需要實現以下功能：

可以按預定的格式，分別不同的物業小區，向前、向后逐個瀏覽各業主的物業費用信息，對顯示的當前業主物業收據進行打印，對指定范圍內的業主物業收據進行批量的、連續的打印。

2 開發環境

Microsoft Office Excel 2007中文版。

3 設計過程

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

2） 新建兩個工作表，表標簽名稱分別以各物業小區名稱命名，分別為“白鶴印象”、“蔚藍天空”，用于存放兩個物業小區收費信息，表列內容包含房號、姓名、水電氣數據、物業費、其它等（如圖1所示）。

3） 新建一個工作表，表標簽名稱為“收據打印”（如圖2所示），用于瀏覽顯示、打印輸出預定格式物業收據。將工作表上部的B2：L11單元格區域設置為打印區域，下部的B12：L13 單元格區域為打印控制區，其內容不打印輸出。

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

5） 對“收據打印”工作表的相關單元格設置公式：

No. K3： =YEAR（TODAY（））&MONTH（TODAY（）） （將當前年月自動冠于收據編號前）

L3： =$F$12 （將當前記錄號自動作為收據編號）

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

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

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

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

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

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

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

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

水實用數I6= =G6-E6

電實用數I7= =G7-E7

氣實用數I8= =G8-E8

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

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

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

水費L6： =ROUND（I6*K6，2）

電費L7： =ROUND（I7*K7，2）

氣費L8： =ROUND（I8*K8，2）

物業費E9： =OFFSET（INDIRECT（D4&"！L4"），$F$12，）

其他費I9：=OFFSET（INDIRECT（D4&"！M4"），$F$12，）

物業費與其他費小計L9： =E9+I9

收據總金額（小寫）L10：=SUM（L6：L9）

收據總金額（大寫）H10： =SUBSTITUTE（SUBSTITUTE（TEXT（TRUNC（FIXED（L10）），"[>0][dbnum2]；[<0]負[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） 在“收據打印”工作表中添加一個數值控件（用于控制選擇當前記錄號），右擊控件，在菜單中選擇“設置控件格式”，在設置窗口中，將當前值、最小值、步長值均設置為1，最大值根據需要設為足夠大的一個整數（以保證能控制各物業小區的最多業主數量），單元格鏈接為$F$12（與當前記錄號單元格建立關聯）。當按下控件的向上、向下箭頭按鈕時，當前記錄號單元格$F$12值發生加1、減1的變化，引發與之相關的所有公式單元格的值發生變化，形成聯動效應，從各物業信息工作表中適時獲取對應的數據，顯示在“收據打印”工作表的單元格中。endprint

7） 在”收據打印”工作表中添加兩個按鈕控件，標題分別為“打印當前頁”、“打印指定頁”

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

Sub PrintCurPage_Click（）

n = MsgBox（"確定打印當前業主收據嗎？"， vbOKCancel）

If n = vbOK Then ActiveSheet.PrintOut

End Sub

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

Sub PrintRange_Click（）

startNum = Range（"F13"）

endNum = Range（"H13"）

'——-起始頁員小于或等于終止頁號時進行打印，否則提示錯誤信息后退出

If startNum <= endNum Then

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

If n = vbOK Then

Range（"F12"） = startNum '——-將當前頁號設置為指定的初始頁號

'——-開始從指定頁號到終止頁號的循環打印

For i = startNum To endNum

ActiveSheet.PrintOut '——-打印當前記錄

Range（"F12"） = Range（"F12"） + 1 '——-將下一記錄轉為當前記錄

Next i

End If

Else

MsgBox "錯誤：起始頁號應小于或等于終止頁號，請重新輸入！"

End If

End Sub

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

4 運行測試

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

5 設計結論

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

參考文獻：

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

[2] 姚文濤.Excel VBA 應用開發經典實例[M].北京：清華大學出版社，2009.

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