在Excel中使用VBA語言進行二次開發(fā)提高閥門井設計效率

陳向東

(新疆兵團勘測設計院(集團)有限責任公司云南分院，云南 昆明 650000)

0 前言

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

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

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

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

在以往的設計中，閥門井的尺寸主要通過大量翻閱管件圖集查找各種管件的長度、寬度來確定，閥門井的工程量需確定閥門井尺寸后逐個計算后匯總，這樣做耗時耗力效率低下。在設計及施工過程中，一旦管徑、閥件規(guī)格有所調整，閥門井的規(guī)格也要進行相應的調整，設計及修改工作量大且極易出錯。基于以上情況，如何快速準確地確定項目中各閥門井的尺寸并進行工程量的統(tǒng)計是我們當下需要解決的問題。

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

2.1 Excel功能區(qū)界面的設計

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

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

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

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

2.2 軟件主程序和窗口界面的設計

2.2.1 軟件邏輯設計

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

圖2 軟件程序邏輯設計流程圖

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

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

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

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

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

2.2.2 軟件操作界面設計

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

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

圖3 程序窗口的界面截圖

2.2.3 軟件主程序設計

1)閥門井尺寸統(tǒng)計的一個關鍵點在于，在窗口輸入管件數(shù)據(jù)后程序能夠自動查找出對應管件的尺寸。為解決這個問題，首先在另一個工作薄中建立管件尺寸的數(shù)據(jù)庫，將不同管件的管徑和尺寸分別填入單元格中，固定其位置。在語言選擇中，采用了VBA中的if循環(huán)語句，使計算機執(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)為保證多個操作按鈕同時執(zhí)行命令，利用VBA語法中的Call命令。其語法如下：

Sub Y計算_單擊()

Call 法蘭1_單擊

Call 三通1長_單擊

Call 減壓閥_單擊

Call 排氣閥_單擊

Call 三通2長_單擊

Call 蝶閥_單擊

Call 伸縮節(jié)_單擊

Call 變徑_單擊

Call 法蘭2_單擊

End Sub

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

Sub復位()

′復位 宏

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 工程應用及效果檢查

使用本程序對常規(guī)閥門井設計耗時統(tǒng)計表中的4個項目進行比較驗證，同時在3個新項目中推廣本程序。通過在7個項目中的應用驗證，將老項目活動前后耗時以及新老項目活動后耗時進行對比，見表1。由表1可以看出使用程序后極大的提高了設計效率。

表1 新老項目活動后耗時對比表

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

表2 常規(guī)設計與程序應用效益對比表

4 結語

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