鈕洪亮 楊 武
(中交第二公路勘察設(shè)計(jì)研究院有限公司 武漢 430053)
在高速公路設(shè)計(jì)項(xiàng)目中,各種工程數(shù)量的計(jì)算與統(tǒng)計(jì)是項(xiàng)重要而又繁瑣的工作。在路基路面設(shè)計(jì)工作中,很多設(shè)計(jì)內(nèi)容都是以設(shè)計(jì)通用圖紙為基礎(chǔ),依據(jù)設(shè)計(jì)圖確定的單位工程數(shù)量逐段計(jì)算并統(tǒng)計(jì)全線的工程量,編制工程數(shù)量總表,如路基防護(hù)工程數(shù)量表、路基排水工程數(shù)量表、路面工程數(shù)量表,等等。對(duì)于這些工程數(shù)量的計(jì)算與統(tǒng)計(jì),Micr osoft公司的EXCEL電子表格軟件是最適合的輔助工具,在目前的設(shè)計(jì)工作中發(fā)揮了巨大的作用。但是多數(shù)設(shè)計(jì)人員只運(yùn)用了EXCEL提供的內(nèi)置函數(shù)和方法,尤其以嵌套的IF語(yǔ)句居多。雖然可以解決一些問(wèn)題,但是存在計(jì)算公式的可讀性差、檢查和復(fù)核困難、嵌套層數(shù)有限等一些問(wèn)題,而且對(duì)于一個(gè)邏輯判斷較復(fù)雜的計(jì)算過(guò)程,僅通過(guò)常規(guī)的內(nèi)置函數(shù)無(wú)疑增加了公式編寫的難度。因此,能夠利用EXCEL提供的VBA編程技術(shù),編制適用于特定要求的自定義函數(shù)顯得尤為重要,其不僅可以實(shí)現(xiàn)復(fù)雜的計(jì)算,而且便于計(jì)算公式的修改和維護(hù),代碼可讀性高,有利于高效率、高質(zhì)量地完成各項(xiàng)工程數(shù)量的計(jì)算工作。本文介紹了在EXCEL中運(yùn)用VBA編程實(shí)現(xiàn)工程數(shù)量計(jì)算與統(tǒng)計(jì)的方法與技巧,并以巴達(dá)高速公路兩階段施工圖設(shè)計(jì)項(xiàng)目中的路基防護(hù)工程數(shù)量計(jì)算為例,介紹其在實(shí)際工程中的應(yīng)用效果。
VBA是Visual Basic f or Application的簡(jiǎn)稱,是內(nèi)嵌于 Microsoft Excel中的程序語(yǔ)言,它的語(yǔ)法結(jié)構(gòu)與Visual Basic極其相似,是為了更高效地使用Excel應(yīng)用程序而有針對(duì)性地優(yōu)化設(shè)置過(guò)的Visual Basic。VBA的主要任務(wù)是組織或集合Excel程序的功能,它無(wú)法脫離Excel環(huán)境獨(dú)立運(yùn)行[1]。
EXCEL中的VBA有著豐富的類庫(kù)和組件,通過(guò)VBA編程可以實(shí)現(xiàn)功能強(qiáng)大且復(fù)雜的EXCEL擴(kuò)展應(yīng)用程序。而針對(duì)高速公路設(shè)計(jì)項(xiàng)目中的工程數(shù)量計(jì)算而言,僅需其中幾條簡(jiǎn)單的語(yǔ)法即可完成幾乎所有的工程數(shù)量計(jì)算工作,下面對(duì)所需的語(yǔ)法歸納總結(jié)如下。
語(yǔ)法:Di m par a m As Type
Di m是聲明局部變量的關(guān)鍵字,par a m是變量名,As Type是數(shù)據(jù)類型關(guān)鍵字,可以是As Integer(整數(shù))或As Double(雙精度浮點(diǎn)數(shù))或As String(字符串)等。
語(yǔ)法:Function f unna me(par a m As Type,……)As Type
…
End Function
Function是聲明一個(gè)函數(shù)的關(guān)鍵字,f unname是函數(shù)名,par am是函數(shù)所需要的形參,As Type代表各自的數(shù)據(jù)類型(同上)。
語(yǔ)法:IF(condition)Then
tr ue_instr uctions
Else f alse_instr uctions
End If
IF-Then是條件判斷構(gòu)造語(yǔ)句,condition為條件表達(dá)式,tr ue時(shí)執(zhí)行tr ue_instr uctions語(yǔ)句,
false時(shí)執(zhí)行f alse_instr uctions語(yǔ)句。適用于2
種或3種選擇的情況。
語(yǔ)法:Select Case testex pression
Case ex pressionlist-n
Instr uctions-n
Case Else
Def ault_instr uctions
End Select
Select Case構(gòu)造用于在3個(gè)或更多的選項(xiàng)之間做選擇的情況,可用于替代多層IF嵌套語(yǔ)句。
語(yǔ)法:For counter =start To end Step stepval
instr uctions
Next counter
For-Next語(yǔ)句是應(yīng)用最多的一種簡(jiǎn)單循環(huán)語(yǔ)句。counter以start為起點(diǎn),以stepval為步距,到End終止循環(huán)運(yùn)行。
在EXCEL中運(yùn)用VBA編程實(shí)現(xiàn)工程數(shù)量計(jì)算的主要方法就是自定義函數(shù)。用VBA編制的自定義函數(shù)與EXCEL中的內(nèi)置函數(shù)(如:SUM()求和函數(shù)[2])是等價(jià)的,應(yīng)用方法也是相同的。編制一個(gè)有自定義函數(shù)名和特定運(yùn)算規(guī)則的VBA函數(shù)的基本步驟與方法如下。
(1)啟動(dòng)VBA編程環(huán)境。選擇EXCEL應(yīng)用程序(2003版)中的“工具→宏→Visual Basic編輯器”(或Alt+F11快捷鍵)打開VBA編程環(huán)境。然后在左側(cè)“工程資源管理器”(若沒(méi)有可通過(guò)點(diǎn)擊“視圖→工程資源管理器”開啟)窗口中右鍵單擊EXCEL文檔名稱,然后選擇“插入→模塊”菜單,創(chuàng)建一個(gè)模塊(默認(rèn)名稱為“模塊1”,可在屬性面板中重命名)。此時(shí)右側(cè)出現(xiàn)一個(gè)可供輸入代碼的窗口(見圖1),完成VBA編程環(huán)境的啟動(dòng)。
圖1 VBA編程環(huán)境界面
(2)編制代碼。在代碼窗口中按照VBA語(yǔ)法規(guī)則及用戶意圖輸入代碼。下面以路基防護(hù)數(shù)量計(jì)算中的三維網(wǎng)植草防護(hù)數(shù)量計(jì)算為例,說(shuō)明自定義一個(gè)函數(shù)的方法,具體代碼及說(shuō)明見表1。
表1 示例代碼與說(shuō)明表
(3)使用自定義函數(shù)。在Excel單元格編輯器中輸入“=SWWFH($A2,$B2,$C2,D$1)”即可,$A2,$B2,$C2和D$1單元格分別為lengt h(長(zhǎng)度),height(邊坡高度),sl ope(邊坡坡率),mat Type(材料類型)參數(shù)的輸入值。
在實(shí)際的高速公路工程數(shù)量計(jì)算中,最為常用的語(yǔ)句就是條件判斷語(yǔ)句(IF語(yǔ)句),但往往因一張工程數(shù)量表中涉及到的工程類型很多,不得不采用多層嵌套的IF語(yǔ)句才能夠完成數(shù)量計(jì)算。這種傳統(tǒng)的計(jì)算方法不僅受到EXCEL應(yīng)用程序本身對(duì)IF嵌套層數(shù)的限制,而且對(duì)于計(jì)算公式的復(fù)核、修改和維護(hù)都變得很困難,尤其在每種工程類型采用各自單獨(dú)的計(jì)算公式時(shí),很容易因工程類型與計(jì)算公式不一致而導(dǎo)致工程數(shù)量計(jì)算結(jié)果有誤。利用EXCEL應(yīng)用程序的VBA編制自定義函數(shù),可以輕松地避免以上問(wèn)題,而且對(duì)于一個(gè)具體的工程數(shù)量計(jì)算問(wèn)題,經(jīng)過(guò)對(duì)其計(jì)算特點(diǎn)的分析,合理地組織程序結(jié)構(gòu),便可得到一個(gè)邏輯嚴(yán)緊、層次清晰、易于修改維護(hù)、計(jì)算準(zhǔn)確、應(yīng)用便捷的工程數(shù)量計(jì)算表。下面以在巴中至達(dá)州高速公路兩階段施工圖設(shè)計(jì)中采用VBA編程實(shí)現(xiàn)的路基防護(hù)工程數(shù)量計(jì)算表為例,介紹所采用的程序結(jié)構(gòu)與應(yīng)用效果。
路基防護(hù)工程數(shù)量計(jì)算表中一般都會(huì)包括多種防護(hù)形式,每種防護(hù)形式都有各自的材料數(shù)量,不同防護(hù)形式可能包括相同的材料類型(如M7.5漿砌片石),為了采用統(tǒng)一的計(jì)算公式獲得所有防護(hù)類型的所有工程數(shù)量,設(shè)計(jì)如下程序結(jié)構(gòu):
'按防護(hù)類型計(jì)算數(shù)量
Select Case typeProj ect
Case…
Case"02-三維掛網(wǎng)" '掛三維網(wǎng)噴播植草
Quantit y =SWWF H(length,height,slope,mat-Type)
Case…
End Select
End Function
'掛三維網(wǎng)噴播植草工程數(shù)量
Private Function SWWF H (length…,mat Type As String)As Double
…
Select Case mat Type
Case"三維網(wǎng)" '三維網(wǎng)面積
SWWFH =SWWF H =area*1.0354 'm2
Case…
End Select
End Function
…… 其他防護(hù)類型工程數(shù)量計(jì)算函數(shù)
'獲取工程數(shù)量主函數(shù)
Public Function Quantity(start Stake … ,typeP-roj ect As String,mat Ty pe As String)As Double
Quantity()函數(shù)為數(shù)量計(jì)算的主函數(shù),也是在EXCEL表格中調(diào)用的唯一函數(shù),采用公有(Public)屬性,其他函數(shù)(如SWWFH())在 EXCEL表格中不需要調(diào)用,只被Quantity()主函數(shù)引用,采用私有(Private)屬性。Quantity()主函數(shù)除了基本的起點(diǎn)樁號(hào)、終點(diǎn)樁號(hào)、邊坡高度、邊坡坡率等參數(shù)外,另外還包括了typePr oject防護(hù)形式和mat Type材料類型2個(gè)參數(shù),通過(guò)2次Select Case條件選擇獲得某種防護(hù)形式中的某種材料的數(shù)量。在EXCEL表格所有的工程數(shù)量單元格中調(diào)用Quantity()函數(shù),利用單元格數(shù)據(jù)的有效性及鎖定功能,即可得到應(yīng)用便捷的路基防護(hù)工程數(shù)量計(jì)算表,見圖2。
圖2 利用VBA編程實(shí)現(xiàn)的路基防護(hù)工程數(shù)量表
通過(guò)在巴中至達(dá)州高速公路設(shè)計(jì)項(xiàng)目中對(duì)利用VBA編程實(shí)現(xiàn)路基防護(hù)工程數(shù)量計(jì)算的實(shí)際應(yīng)用,得出如下結(jié)論:
(1)利用EXCEL應(yīng)用程序中的VBA編程功能,可以有效地避免采用多層嵌套IF語(yǔ)句所帶來(lái)的不便,有利于提高工作效率和計(jì)算的準(zhǔn)確性。
(2)利用EXCEL應(yīng)用程序中的VBA編程功能,可以實(shí)現(xiàn)統(tǒng)一的計(jì)算函數(shù),避免了一個(gè)EXCEL表中因包含多個(gè)計(jì)算公式而帶來(lái)的混淆計(jì)算錯(cuò)誤問(wèn)題。
(3)利用EXCEL應(yīng)用程序中的VBA編程功能,可以將所有的計(jì)算代碼集中在一起,利用Select Case語(yǔ)句代替IF語(yǔ)句,使代碼的層次更加清晰,可讀性高,便于公式的復(fù)核、修改和維護(hù)。
(4)結(jié)合EXCEL應(yīng)用程序中的單元格數(shù)據(jù)有效性、鎖定和表格保護(hù)等功能,能夠?qū)崿F(xiàn)類似程序輸入界面的計(jì)算表格,使數(shù)據(jù)輸入?yún)^(qū)與計(jì)算結(jié)果區(qū)完全分開,有效地避免了對(duì)計(jì)算公式的無(wú)意修改,同時(shí)參數(shù)輸入集中,操作便捷。
(5)能夠適用于高速公路設(shè)計(jì)項(xiàng)目中所有的工程數(shù)量計(jì)算,涉及到的VBA語(yǔ)法少而簡(jiǎn)單,便于推廣應(yīng)用。
[1] John Walkenbach.Excel 2003高級(jí) VBA編程寶典[M].北京:電子工業(yè)出版社,2005.
[2] John Walkenbach.Excel 2002公式與函數(shù)應(yīng)用寶典[M].北京:電子工業(yè)出版社,2002.