陳向東

(新疆兵團(tuán)勘測設(shè)計(jì)院(集團(tuán))有限責(zé)任公司云南分院，云南 昆明 650000)

0 前言

實(shí)施高效節(jié)水灌溉是保證國家糧食安全和節(jié)水型社會(huì)建設(shè)的迫切需要，發(fā)展農(nóng)業(yè)高效節(jié)水灌溉技術(shù)已成為全社會(huì)各界的共識(shí)。在此情況下，高效節(jié)水類項(xiàng)目、小型農(nóng)田水利重點(diǎn)縣、節(jié)水示范項(xiàng)目、灌區(qū)配套建設(shè)等項(xiàng)目越來越多。閥門井是節(jié)水類項(xiàng)目中常見的建筑物，為此，如何快速準(zhǔn)確地確定項(xiàng)目中各閥門井的尺寸并進(jìn)行工程量的統(tǒng)計(jì)，是高效節(jié)水項(xiàng)目設(shè)計(jì)中亟待解決的問題。

本文依托Excel平臺(tái)使用VBA語言進(jìn)行二次開發(fā)，編制出閥門井的統(tǒng)計(jì)整理軟件以便快速統(tǒng)計(jì)閥門井的尺寸，縮短閥門井的設(shè)計(jì)時(shí)間。

1 傳統(tǒng)閥門井的設(shè)計(jì)現(xiàn)狀及問題

在高效節(jié)水灌溉項(xiàng)目中，閥門井作為保障高效節(jié)水灌溉工程安全運(yùn)行和操作方便的重要建筑物，一般輸水干管平均不足1 km需要設(shè)置一處閥門井，一個(gè)滴灌系統(tǒng)、噴灌系統(tǒng)中平均每150畝需要設(shè)置一處閥門井，而對(duì)于云南等多山且地形起伏較大的項(xiàng)目區(qū)，需要的排氣井、排泥井、檢修井等閥門井的數(shù)量會(huì)在此基礎(chǔ)上大量增加。尤其在云南的高效節(jié)水灌溉項(xiàng)目的設(shè)計(jì)中，閥門井不但數(shù)量多，而且其尺寸受閥門井內(nèi)管件大小和數(shù)量的影響，不同種類數(shù)量的閥件所需要的閥井規(guī)格也不一樣。

在以往的設(shè)計(jì)中，閥門井的尺寸主要通過大量翻閱管件圖集查找各種管件的長度、寬度來確定，閥門井的工程量需確定閥門井尺寸后逐個(gè)計(jì)算后匯總，這樣做耗時(shí)耗力效率低下。在設(shè)計(jì)及施工過程中，一旦管徑、閥件規(guī)格有所調(diào)整，閥門井的規(guī)格也要進(jìn)行相應(yīng)的調(diào)整，設(shè)計(jì)及修改工作量大且極易出錯(cuò)?；谝陨锨闆r，如何快速準(zhǔn)確地確定項(xiàng)目中各閥門井的尺寸并進(jìn)行工程量的統(tǒng)計(jì)是我們當(dāng)下需要解決的問題。

2 閥門井統(tǒng)計(jì)軟件的編制

2.1 Excel功能區(qū)界面的設(shè)計(jì)

Excel本身是一個(gè)非常強(qiáng)大的數(shù)據(jù)統(tǒng)計(jì)分析的軟件，Excel VBA是依附在Excel中的二次開發(fā)語言，全稱為“Visual Basic For Application”。VBA不僅已經(jīng)有20年的歷史，其開發(fā)和語法已經(jīng)趨于完善。

Excel的功能區(qū)界面已經(jīng)相當(dāng)成熟，使用功能區(qū)對(duì)Excel進(jìn)行各種操作，既美觀又方便，而且可以在相同的空間中放置更多的按鈕，又可以節(jié)約查找命令按鈕的時(shí)間。對(duì)于VBA開發(fā)者而言定制屬于自己的專用選項(xiàng)卡可以提升調(diào)用過程的便捷性，同時(shí)也使自己的程序更好的融入Excel中。借助Custom UI Editor for Microsoft Office軟件對(duì)Excel的Ribbon功能區(qū)進(jìn)行編程以增加自己需要的模塊，使其在Excel界面中就能直接進(jìn)行閥門井尺寸統(tǒng)計(jì)的操作，后續(xù)處理也能應(yīng)用Excel本身自帶的功能，減少了重新封裝軟件的工作量。本程序增加的功能區(qū)模塊為一個(gè)一級(jí)菜單和兩個(gè)下拉二級(jí)菜單。

使用Custom UI Editor for Microsoft Office[1]軟件對(duì)功能區(qū)進(jìn)行代碼編寫的結(jié)構(gòu)相當(dāng)嚴(yán)謹(jǐn)，每句代碼的順序都有嚴(yán)格的規(guī)定，而且所有代碼都必須配對(duì)，嚴(yán)格區(qū)分大小寫。在代碼編輯中第一句和最后一句是配對(duì)的，它是功能區(qū)代碼的根或者稱之為殼，類似于VBA中的Sub與End的關(guān)系。首尾兩句都“customUI”,表示這是一個(gè)自定義功能區(qū)的容器。首句代碼使用了“<>”符號(hào)。部分主要代碼；Ribbon功能區(qū)界面見圖1。

圖1 Ribbon功能區(qū)界面

2.2 軟件主程序和窗口界面的設(shè)計(jì)

2.2.1 軟件邏輯設(shè)計(jì)

閥門井的尺寸由長和寬決定，確定閥門井的尺寸其實(shí)就是確定閥門井的長和寬?，F(xiàn)定義閥門井長的方向?yàn)閄方向并且X方向分為X左方向和X右方向，寬的方向?yàn)閅方向。設(shè)計(jì)流程圖見圖2。

圖2 軟件程序邏輯設(shè)計(jì)流程圖

第一步，單擊功能區(qū)彩云之南菜單，然后單擊查找的二級(jí)菜單。在彈出的窗口中單擊復(fù)位，清空原有數(shù)據(jù)。輸入閥門井的編號(hào)、X左方向閥件的種類和數(shù)量，求出X左方向的閥件長度總和。

第二步，如果閥門井在Y方向上沒有閥件那么保存結(jié)果并退出。如果在Y方向上有其他閥件的話，繼續(xù)輸入Y方向的閥件種類和數(shù)量，求出Y方向的閥門件的長度總和。

第三步，如果閥門井在X右方向上沒有閥件那么保存結(jié)果并退出。如果X右方向還有閥件，則繼續(xù)輸入X右方向閥件的種類和數(shù)量，并求出結(jié)果。

第四步，單擊統(tǒng)計(jì)按鈕，進(jìn)行閥門井尺寸的分類匯總。在此環(huán)節(jié)使用了Excel自帶的函數(shù)功能對(duì)上一步傳遞過來的閥門井的尺寸數(shù)據(jù)進(jìn)行了分類匯總和篩選。

第五步，已經(jīng)設(shè)計(jì)好的典型尺寸的閥門井工程量已經(jīng)保存在了Excel的工作簿中，通過Excel函數(shù)的篩選，對(duì)篩選結(jié)果稍加整理，便可以得出最終的工程量匯總結(jié)果。至此閥門井的特性表和工程量的匯總表已經(jīng)全部整理好。

2.2.2 軟件操作界面設(shè)計(jì)

VBA的擴(kuò)展區(qū)自帶UserForm模塊[2]。UserForm即用戶窗體，通過窗體可以操作工作簿、工作表、單元格、批注、圖形對(duì)象等，也可以利用窗體設(shè)計(jì)一個(gè)單獨(dú)的操作界面，完全脫離單元格、工作表等數(shù)據(jù)載體而工作。本程序中VBA的窗體主要用于一下幾個(gè)方面：①設(shè)計(jì)登錄窗口;②制作數(shù)據(jù)輸入界面;③數(shù)據(jù)查詢界面；④選項(xiàng)設(shè)置窗口。

為方便工程設(shè)計(jì)人員使用本程序，利用Excel中VBA模塊的窗口編輯操作界面，分別設(shè)計(jì)了數(shù)據(jù)填寫、清空、計(jì)算、求和、下一步等操作按鈕。為了方便操作人員識(shí)別法蘭、變徑、三通等閥件，在每個(gè)閥件的按鈕下加上了閥件的附圖和典型管件節(jié)點(diǎn)的截圖。為便于操作者快速上手還在每個(gè)界面中補(bǔ)充了使用說明。其余每個(gè)界面均類似，窗口設(shè)計(jì)見圖3。

圖3 程序窗口的界面截圖

2.2.3 軟件主程序設(shè)計(jì)

1)閥門井尺寸統(tǒng)計(jì)的一個(gè)關(guān)鍵點(diǎn)在于，在窗口輸入管件數(shù)據(jù)后程序能夠自動(dòng)查找出對(duì)應(yīng)管件的尺寸。為解決這個(gè)問題，首先在另一個(gè)工作薄中建立管件尺寸的數(shù)據(jù)庫，將不同管件的管徑和尺寸分別填入單元格中，固定其位置。在語言選擇中，采用了VBA中的if循環(huán)語句，使計(jì)算機(jī)執(zhí)行循環(huán)查找的功能。其語法如下：

Sub法蘭11_單擊()

a=Sheet1.Cells(5,19)

For i=3 To 100

If Sheet2.Cells(i,18)=a Then

Sheet1.Cells(7,19)=Sheet2.Cells(i,19)*Sheet1.Cells(6,19)

End If

Next i

End Sub

2)為保證多個(gè)操作按鈕同時(shí)執(zhí)行命令，利用VBA語法中的Call命令。其語法如下：

Sub Y計(jì)算_單擊()

Call 法蘭1_單擊

Call 三通1長_單擊

Call 減壓閥_單擊

Call 排氣閥_單擊

Call 三通2長_單擊

Call 蝶閥_單擊

Call 伸縮節(jié)_單擊

Call 變徑_單擊

Call 法蘭2_單擊

End Sub

3)錄制宏[3]可以自動(dòng)產(chǎn)生代碼，既減少了編寫代碼的時(shí)間，又為程序編寫人員提供了參考，在忘記代碼拼寫方式的前提下仍然可以編程，完成既定的工作。部分繁瑣操作直接編寫程序語言比較復(fù)雜，為了發(fā)揮Excel平臺(tái)的優(yōu)勢(shì)，采用錄制宏的方式獲得程序語言。對(duì)于復(fù)位的操作錄制的宏過程如下：

Sub復(fù)位()

′復(fù)位 宏

Range("B8:H10").Select

Selection.ClearContents

Range("J4:L12").Select

Selection.ClearContents

Range("J14:L22").Select

Selection.ClearContents

Range("M5:S7").Select

Selection.ClearContents

Range("B9").Select

ActiveCell.FormulaR1C1="1"

Range("B9").Select

Selection.AutoFill Destination:=Range("B9:H9"), Type:=xlFillDefault

Range("B9:H9").Select

Range("K4").Select

ActiveCell.FormulaR1C1="1"

Range("K4").Select

Selection.AutoFill Destination:=Range("K4:K11"), Type:=xlFillDefault

Range("K4:K11").Select

Selection.AutoFill Destination:=Range("K4:K12"), Type:=xlFillDefault

Range("K4:K12").Select

Range("K14").Select

ActiveCell.FormulaR1C1="1"

Range("K15").Select

ActiveCell.FormulaR1C1="1"

Range("K14:K15").Select

Selection.AutoFill Destination:=Range("K14:K22"), Type:=xlFillDefault

Range("K14:K22").Select

Range("M6").Select

ActiveCell.FormulaR1C1="1"

Selection.AutoFill Destination:=Range("M6:S6"), Type:=xlFillDefault

Range("M6:S6").Select

End Sub

3 工程應(yīng)用及效果檢查

使用本程序?qū)ΤＲ?guī)閥門井設(shè)計(jì)耗時(shí)統(tǒng)計(jì)表中的4個(gè)項(xiàng)目進(jìn)行比較驗(yàn)證，同時(shí)在3個(gè)新項(xiàng)目中推廣本程序。通過在7個(gè)項(xiàng)目中的應(yīng)用驗(yàn)證，將老項(xiàng)目活動(dòng)前后耗時(shí)以及新老項(xiàng)目活動(dòng)后耗時(shí)進(jìn)行對(duì)比，見表1。由表1可以看出使用程序后極大的提高了設(shè)計(jì)效率。

表1 新老項(xiàng)目活動(dòng)后耗時(shí)對(duì)比表

通過對(duì)現(xiàn)狀調(diào)查中7個(gè)樣本項(xiàng)目合同額及工作時(shí)間進(jìn)行對(duì)比，按常規(guī)閥門井設(shè)計(jì)占工程總設(shè)計(jì)時(shí)間的10%進(jìn)行考慮，可以看出應(yīng)用程序成果前，項(xiàng)目組成員人均日創(chuàng)造效益為1660元，程序成果應(yīng)用后，項(xiàng)目組成員人均日創(chuàng)造效益為1885元，提高高效節(jié)水類項(xiàng)目人均日效益值225元。效益對(duì)比見表2。

表2 常規(guī)設(shè)計(jì)與程序應(yīng)用效益對(duì)比表

4 結(jié)語

本文簡要介紹了VBA在Excel二次開發(fā)中的應(yīng)用。詳細(xì)介紹了Custom UI Editor for Microsoft Office編輯器在功能區(qū)擴(kuò)展開發(fā)中的應(yīng)用及程序的編寫，以及循環(huán)語句的編寫、宏的錄制、VBA窗口的設(shè)計(jì)。其實(shí)本程序的查找功能同樣適用于其他設(shè)計(jì)環(huán)節(jié)的工作。比如造價(jià)人員在做概算時(shí)需要查找單價(jià)，而翻閱價(jià)格信息同樣很繁瑣。如果把單價(jià)信息的原始數(shù)據(jù)提前存儲(chǔ)到數(shù)據(jù)庫中，再對(duì)窗口界面進(jìn)行適當(dāng)修改，同樣可以達(dá)到快速查找單價(jià)的目的。希望本程序?qū)λ?xiàng)目設(shè)計(jì)及Excel的VBA二次開發(fā)有所幫助。