基于VBA的都江堰灌區(qū)蓄水量查詢程序編寫

(四川省都江堰管理局，四川 都江堰，611830)

1 程序編寫的背景

日常用水管理中，對(duì)灌區(qū)蓄水量的統(tǒng)計(jì)、管理以及數(shù)據(jù)查詢是一項(xiàng)必備的工作，特別是經(jīng)常需要對(duì)某一個(gè)或幾個(gè)管理處的蓄水量數(shù)據(jù)進(jìn)行某一年、多年分析。由于蓄水量統(tǒng)計(jì)表以旬為單位進(jìn)行統(tǒng)計(jì)整理，因此，常規(guī)蓄水量數(shù)據(jù)統(tǒng)計(jì)查詢的做法是將各Excel文件中的相關(guān)數(shù)據(jù)拷貝到一起再進(jìn)行分析。這樣做的缺點(diǎn)顯而易見，由于事先不知道查詢?nèi)蝿?wù)，因而只有等到任務(wù)下達(dá)了才能進(jìn)行相關(guān)操作，查詢效率較低且易出錯(cuò)；另一方面由于原始數(shù)據(jù)資料已用Excel文件逐旬編輯成固定格式，若要更換為新格式，工作人員需要熟悉，且如何把已有的大量數(shù)據(jù)統(tǒng)一為新格式也是一個(gè)繁瑣的過程。因此，如何基于已有的Excel數(shù)據(jù)文件進(jìn)行高效數(shù)據(jù)查詢，就是一個(gè)必須面對(duì)的問題。Office辦公軟件提供了基于VBA的二次開發(fā)環(huán)境，可以將編寫好的程序做成宏或者以Excel文件進(jìn)行發(fā)布，充分利用了Office辦公軟件自身的特性，且不需要用戶使用前再安裝新的程序。

2 程序運(yùn)行的流程

編寫一個(gè)程序，首先要明確該程序所希望的運(yùn)行流程，然后再按照流程內(nèi)的不同功能，逐一分析編寫對(duì)應(yīng)的程序代碼。這樣做的好處是，可以將一個(gè)程序按實(shí)現(xiàn)其功能的不同分別編寫代碼，也就是說將一個(gè)大命題通過流程圖的形式劃分為相互聯(lián)系的若干小命題加以解決。在對(duì)各代碼驗(yàn)證時(shí)，只需要賦予特定的值即可，這樣既便于代碼的編寫、后期維護(hù)，同時(shí)也有效縮短了程序編寫的總時(shí)間。

本程序運(yùn)行后，需要實(shí)現(xiàn)以下功能：(1)指定相應(yīng)的原始數(shù)據(jù)文件；(2)指定查詢的時(shí)間段；(3)輸出查詢結(jié)果。

這里涉及到幾個(gè)問題：

(1)在指定原始數(shù)據(jù)文件的過程中，是否采用提前固定原始數(shù)據(jù)存放的路徑。如果采用，那么這不太符合“以人為本”的原則，因?yàn)橛脩粼谧罱K使用時(shí)，程序員不得不告訴其原始數(shù)據(jù)存放的固定路徑，而這一預(yù)設(shè)的固定路徑有可能正是用戶所不希望的。因此，原始數(shù)據(jù)存放的路徑事先不能固定，應(yīng)在程序運(yùn)行時(shí)由用戶指定。那么新的問題是，人都會(huì)不小心、會(huì)犯錯(cuò)誤，如果用戶在指定原始數(shù)據(jù)存放路徑時(shí)，不小心選擇錯(cuò)誤，那么程序應(yīng)能檢測(cè)出這一錯(cuò)誤，并給出明確的中文提示而非冷冰冰的編譯錯(cuò)誤提示。

(2)既然原始數(shù)據(jù)存放路徑在程序運(yùn)行時(shí)由用戶指定，那么就必須有一個(gè)用戶窗體用以實(shí)現(xiàn)程序和用戶的交互。

(3)根據(jù)指定的原始數(shù)據(jù)文件所代表的時(shí)間范圍，選擇查詢的時(shí)間。程序應(yīng)能自動(dòng)判斷用戶輸入的起點(diǎn)時(shí)間是否遲于終點(diǎn)時(shí)間，如果發(fā)生，那么程序能提示用戶操作錯(cuò)誤，并幫助用戶更正。

(4)把查詢的數(shù)據(jù)輸出到新建Excel文件中，這就涉及到對(duì)Workbook、Worksheet以及Range的操作。

程序運(yùn)行流程如圖1。

圖 1程序運(yùn)行流程

3 程序編寫的主要技術(shù)要點(diǎn)

按上文所述，指定蓄水量數(shù)據(jù)文件、指定查詢時(shí)段、輸出查詢結(jié)果為程序的三個(gè)主要部分。

圖2 程序在WPS-Sheet中運(yùn)行

3.1 指定蓄水量數(shù)據(jù)文件

首先定義String類型的全局變量sPath0用以保存指定蓄水量數(shù)據(jù)文件的存放路徑。同時(shí)定義Object類型的全局型變量xlApp，并用CreateObject方法創(chuàng)建對(duì)Excel.Application對(duì)象的引用：

set xlApp=CreateObject(“Excel.Application”)

在Excel對(duì)象模型中Application對(duì)象位于頂層。本程序采用Excel2003自帶的VBA開發(fā)環(huán)境編寫，這樣做的好處是：由于企事業(yè)單位中常用的WPS辦公軟件與Office辦公軟件對(duì)應(yīng)的對(duì)象模型相同，因此，在Excel2003中編寫的程序也能在Sheet中運(yùn)行，不用考慮程序運(yùn)行環(huán)境遷移帶來的問題。創(chuàng)建好對(duì)Excel.Application對(duì)象的引用以便由此創(chuàng)建其他對(duì)象引用(如：Workbook，Worksheet，Range等)。

為了更好地體現(xiàn)人機(jī)交互，使用Application的FileDialog屬性返回一個(gè)代表文件對(duì)話框的對(duì)象實(shí)例，語(yǔ)法格式為：

expression.FileDialog(fileDialogType)

其中fileDialogType參數(shù)代表文件對(duì)話框類型。msoFileDialogFolderPicker表示允許用戶選擇一個(gè)文件夾。用Do-Loop循環(huán)判斷并提示用戶選擇蓄水量數(shù)據(jù)正確的存放位置。主要程序代碼如下：

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)<>"都江堰灌區(qū)蓄水量統(tǒng)計(jì)報(bào)表"Then

m=MsgBox("非數(shù)據(jù)存放文件夾！"，vbCritical+vbRetryCancel，"錯(cuò)誤")

Else

m=vbOK

EndIf

Else

m=MsgBox("存放數(shù)據(jù)的根文件夾沒有選擇！"，vbCritical+vbRetryCancel，"錯(cuò)誤")

EndIf

EndWith

LoopWhilem=vbRetry

3.2 指定查詢時(shí)段

在上述過程中程序并沒有返回代表蓄水量數(shù)據(jù)文件的Workbook對(duì)象實(shí)例。在指定查詢時(shí)段中，用Set語(yǔ)句和Add方法返回一個(gè)基于xlApp的Workbook對(duì)象，用GetObject方法返回指定蓄水量數(shù)據(jù)文件路徑所確定的Workbook對(duì)象。代碼如下：

SetxlWorkBook0=xlApp.Workbooks.Add

SetxlWorkBook0=GetObject(sPath0)

為避免指定查詢時(shí)段出現(xiàn)起始時(shí)間晚于終止時(shí)間的問題，用DateDiff函數(shù)計(jì)算相差的時(shí)間。語(yǔ)法格式為：

DateDiff(interval，date1，date2[，firstdayofweek[，firstweekofyear]])

如果date1晚于date2，DateDiff返回負(fù)值。其中interval參數(shù)代表計(jì)算的時(shí)間間隔以什么單位返回。本程序中設(shè)置interval參數(shù)的值為字符串d，這意味著DateDiff函數(shù)計(jì)算的時(shí)間間隔是以“日”為單位。

3.3 輸出查詢結(jié)果

輸出查詢結(jié)果主要涉及對(duì)Range集合的操作。如何知道所需的Range集合的屬性和方法，一個(gè)簡(jiǎn)便的方法是采用錄制宏?？梢栽谶M(jìn)行相關(guān)操作前打開錄制宏功能，再對(duì)Range集合進(jìn)行相應(yīng)操作，結(jié)束宏錄制后，一段代碼自動(dòng)生成。此時(shí)，再對(duì)生成的錄制宏代碼稍加研究，即可找到所需的Range集合的屬性和方法。

本程序?qū)卧褡煮w格式的設(shè)置使用Character對(duì)象的Font屬性，示例代碼如下：

Setrng=xlWorkSheet.Range("A1")

Withrng

.Value="都江堰灌區(qū)蓄水量統(tǒng)計(jì)表"

With.Characters.Font

.Name="宋體"

.Bold=True

.Size=20

EndWith

EndWith

使用With語(yǔ)句可加強(qiáng)代碼的可讀性，同時(shí)提高程序的運(yùn)行效率(程序運(yùn)行時(shí)遇到“.”就會(huì)花費(fèi)時(shí)間去解析這個(gè)引用，因此，“.”越少，代碼執(zhí)行的速度越快)。

對(duì)多個(gè)單元格合并操作的實(shí)現(xiàn)采用Range集合的Merge方法，并用HorizontalAlignment與VerticalAlignment屬性分別設(shè)置單元格中文字的水平居中與垂直居中，示例代碼如下：

Setrng=xlWorkSheet.Range("A1:I1")

Withrng

.Merge

.HorizontalAlignment=xlHAlignCenter

.VerticalAlignment=xlVAlignCenter

EndWith

對(duì)單元格繪制邊框的實(shí)現(xiàn)采用Range集合的Borders集合，可由此設(shè)置單元格四周邊框的線條樣式與線條顏色。示例代碼如下：

Withrng.Borders(xlEdgeLeft)

.Weight=xlThin

.LineStyle=xlContinuous

.ColorIndex=xlColorIndexAutomatic

EndWith

由于數(shù)據(jù)在單元格中的寫入有規(guī)律可循，可以用循環(huán)語(yǔ)句配合Range集合的Offset屬性實(shí)現(xiàn)數(shù)據(jù)輸出。語(yǔ)法格式為：

expression.Offset(RowOffset，ColumnOffset)

其中，RowOffset、ColumnOffset分別為行偏移與列偏移。對(duì)RowOffset而言，當(dāng)值為正時(shí)表示向下偏移，為負(fù)值時(shí)表示向上偏移；對(duì)ColumnOffset而言，當(dāng)值為正時(shí)表示向右偏移，為負(fù)值時(shí)表示向左偏移。

4 結(jié)論

本文針對(duì)日常用水管理中蓄水量查詢工作，利用其數(shù)據(jù)以Excel文件格式化存放的特點(diǎn)，使用Excel自帶的VBA編譯環(huán)境進(jìn)行了二次開發(fā)，編寫了都江堰灌區(qū)蓄水量查詢程序。在實(shí)際工作中，該程序能快速輸出蓄水量數(shù)據(jù)查詢結(jié)果，有效提高了工作效率，具有很強(qiáng)的實(shí)用性。