基于VBA的都江堰灌区蓄水量查询程序编写
2020-03-11
(四川省都江堰管理局，四川 都江堰，611830)
1 程序编写的背景
日常用水管理中，对灌区蓄水量的统计、管理以及数据查询是一项必备的工作，特别是经常需要对某一个或几个管理处的蓄水量数据进行某一年、多年分析。由于蓄水量统计表以旬为单位进行统计整理，因此，常规蓄水量数据统计查询的做法是将各Excel文件中的相关数据拷贝到一起再进行分析。这样做的缺点显而易见，由于事先不知道查询任务，因而只有等到任务下达了才能进行相关操作，查询效率较低且易出错；另一方面由于原始数据资料已用Excel文件逐旬编辑成固定格式，若要更换为新格式，工作人员需要熟悉，且如何把已有的大量数据统一为新格式也是一个繁琐的过程。因此，如何基于已有的Excel数据文件进行高效数据查询，就是一个必须面对的问题。Office办公软件提供了基于VBA的二次开发环境，可以将编写好的程序做成宏或者以Excel文件进行发布，充分利用了Office办公软件自身的特性，且不需要用户使用前再安装新的程序。
2 程序运行的流程
编写一个程序，首先要明确该程序所希望的运行流程，然后再按照流程内的不同功能，逐一分析编写对应的程序代码。这样做的好处是，可以将一个程序按实现其功能的不同分别编写代码，也就是说将一个大命题通过流程图的形式划分为相互联系的若干小命题加以解决。在对各代码验证时，只需要赋予特定的值即可，这样既便于代码的编写、后期维护，同时也有效缩短了程序编写的总时间。
本程序运行后，需要实现以下功能：(1)指定相应的原始数据文件；(2)指定查询的时间段；(3)输出查询结果。
这里涉及到几个问题：
(1)在指定原始数据文件的过程中，是否采用提前固定原始数据存放的路径。如果采用，那么这不太符合“以人为本”的原则，因为用户在最终使用时，程序员不得不告诉其原始数据存放的固定路径，而这一预设的固定路径有可能正是用户所不希望的。因此，原始数据存放的路径事先不能固定，应在程序运行时由用户指定。那么新的问题是，人都会不小心、会犯错误，如果用户在指定原始数据存放路径时，不小心选择错误，那么程序应能检测出这一错误，并给出明确的中文提示而非冷冰冰的编译错误提示。
(2)既然原始数据存放路径在程序运行时由用户指定，那么就必须有一个用户窗体用以实现程序和用户的交互。
(3)根据指定的原始数据文件所代表的时间范围，选择查询的时间。程序应能自动判断用户输入的起点时间是否迟于终点时间，如果发生，那么程序能提示用户操作错误，并帮助用户更正。
(4)把查询的数据输出到新建Excel文件中，这就涉及到对Workbook、Worksheet以及Range的操作。
程序运行流程如图1。
图 1程序运行流程
3 程序编写的主要技术要点
按上文所述，指定蓄水量数据文件、指定查询时段、输出查询结果为程序的三个主要部分。
图2 程序在WPS-Sheet中运行
3.1 指定蓄水量数据文件
首先定义String类型的全局变量sPath0用以保存指定蓄水量数据文件的存放路径。同时定义Object类型的全局型变量xlApp，并用CreateObject方法创建对Excel.Application对象的引用：
set xlApp=CreateObject(“Excel.Application”)
在Excel对象模型中Application对象位于顶层。本程序采用Excel2003自带的VBA开发环境编写，这样做的好处是：由于企事业单位中常用的WPS办公软件与Office办公软件对应的对象模型相同，因此，在Excel2003中编写的程序也能在Sheet中运行，不用考虑程序运行环境迁移带来的问题。创建好对Excel.Application对象的引用以便由此创建其他对象引用(如：Workbook，Worksheet，Range等)。
为了更好地体现人机交互，使用Application的FileDialog属性返回一个代表文件对话框的对象实例，语法格式为：
expression.FileDialog(fileDialogType)
其中fileDialogType参数代表文件对话框类型。msoFileDialogFolderPicker表示允许用户选择一个文件夹。用Do-Loop循环判断并提示用户选择蓄水量数据正确的存放位置。主要程序代码如下：
Do
With xlApp.FileDialog(msoFileDialogFolderPicker)
If.Show=True Then
sPath0=.SelectedItems(1)
n=Len(sPath0)
Do
s=Mid(sPath0，n，1)
n=n-1
Loop While s<>""
IfRight(sPath0，Len(sPath0)-n-1)<>"都江堰灌区蓄水量统计报表"Then
m=MsgBox("非数据存放文件夹！"，vbCritical+vbRetryCancel，"错误")
Else
m=vbOK
EndIf
Else
m=MsgBox("存放数据的根文件夹没有选择！"，vbCritical+vbRetryCancel，"错误")
EndIf
EndWith
LoopWhilem=vbRetry
3.2 指定查询时段
在上述过程中程序并没有返回代表蓄水量数据文件的Workbook对象实例。在指定查询时段中，用Set语句和Add方法返回一个基于xlApp的Workbook对象，用GetObject方法返回指定蓄水量数据文件路径所确定的Workbook对象。代码如下：
SetxlWorkBook0=xlApp.Workbooks.Add
SetxlWorkBook0=GetObject(sPath0)
为避免指定查询时段出现起始时间晚于终止时间的问题，用DateDiff函数计算相差的时间。语法格式为：
DateDiff(interval，date1，date2[，firstdayofweek[，firstweekofyear]])
如果date1晚于date2，DateDiff返回负值。其中interval参数代表计算的时间间隔以什么单位返回。本程序中设置interval参数的值为字符串d，这意味着DateDiff函数计算的时间间隔是以“日”为单位。
3.3 输出查询结果
输出查询结果主要涉及对Range集合的操作。如何知道所需的Range集合的属性和方法，一个简便的方法是采用录制宏。可以在进行相关操作前打开录制宏功能，再对Range集合进行相应操作，结束宏录制后，一段代码自动生成。此时，再对生成的录制宏代码稍加研究，即可找到所需的Range集合的属性和方法。
本程序对单元格字体格式的设置使用Character对象的Font属性，示例代码如下：
Setrng=xlWorkSheet.Range("A1")
Withrng
.Value="都江堰灌区蓄水量统计表"
With.Characters.Font
.Name="宋体"
.Bold=True
.Size=20
EndWith
EndWith
使用With语句可加强代码的可读性，同时提高程序的运行效率(程序运行时遇到“.”就会花费时间去解析这个引用，因此，“.”越少，代码执行的速度越快)。
对多个单元格合并操作的实现采用Range集合的Merge方法，并用HorizontalAlignment与VerticalAlignment属性分别设置单元格中文字的水平居中与垂直居中，示例代码如下：
Setrng=xlWorkSheet.Range("A1:I1")
Withrng
.Merge
.HorizontalAlignment=xlHAlignCenter
.VerticalAlignment=xlVAlignCenter
EndWith
对单元格绘制边框的实现采用Range集合的Borders集合，可由此设置单元格四周边框的线条样式与线条颜色。示例代码如下：
Withrng.Borders(xlEdgeLeft)
.Weight=xlThin
.LineStyle=xlContinuous
.ColorIndex=xlColorIndexAutomatic
EndWith
由于数据在单元格中的写入有规律可循，可以用循环语句配合Range集合的Offset属性实现数据输出。语法格式为：
expression.Offset(RowOffset，ColumnOffset)
其中，RowOffset、ColumnOffset分别为行偏移与列偏移。对RowOffset而言，当值为正时表示向下偏移，为负值时表示向上偏移；对ColumnOffset而言，当值为正时表示向右偏移，为负值时表示向左偏移。
4 结论
本文针对日常用水管理中蓄水量查询工作，利用其数据以Excel文件格式化存放的特点，使用Excel自带的VBA编译环境进行了二次开发，编写了都江堰灌区蓄水量查询程序。在实际工作中，该程序能快速输出蓄水量数据查询结果，有效提高了工作效率，具有很强的实用性。
