用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