在Excel中使用VBA语言进行二次开发提高阀门井设计效率
2021-05-28陈向东
陈向东
(新疆兵团勘测设计院(集团)有限责任公司云南分院，云南 昆明 650000)
0 前言
实施高效节水灌溉是保证国家粮食安全和节水型社会建设的迫切需要，发展农业高效节水灌溉技术已成为全社会各界的共识。在此情况下，高效节水类项目、小型农田水利重点县、节水示范项目、灌区配套建设等项目越来越多。阀门井是节水类项目中常见的建筑物，为此，如何快速准确地确定项目中各阀门井的尺寸并进行工程量的统计，是高效节水项目设计中亟待解决的问题。
本文依托Excel平台使用VBA语言进行二次开发，编制出阀门井的统计整理软件以便快速统计阀门井的尺寸，缩短阀门井的设计时间。
1 传统阀门井的设计现状及问题
在高效节水灌溉项目中，阀门井作为保障高效节水灌溉工程安全运行和操作方便的重要建筑物，一般输水干管平均不足1 km需要设置一处阀门井，一个滴灌系统、喷灌系统中平均每150亩需要设置一处阀门井，而对于云南等多山且地形起伏较大的项目区，需要的排气井、排泥井、检修井等阀门井的数量会在此基础上大量增加。尤其在云南的高效节水灌溉项目的设计中，阀门井不但数量多，而且其尺寸受阀门井内管件大小和数量的影响，不同种类数量的阀件所需要的阀井规格也不一样。
在以往的设计中，阀门井的尺寸主要通过大量翻阅管件图集查找各种管件的长度、宽度来确定，阀门井的工程量需确定阀门井尺寸后逐个计算后汇总，这样做耗时耗力效率低下。在设计及施工过程中，一旦管径、阀件规格有所调整，阀门井的规格也要进行相应的调整，设计及修改工作量大且极易出错。基于以上情况，如何快速准确地确定项目中各阀门井的尺寸并进行工程量的统计是我们当下需要解决的问题。
2 阀门井统计软件的编制
2.1 Excel功能区界面的设计
Excel本身是一个非常强大的数据统计分析的软件，Excel VBA是依附在Excel中的二次开发语言，全称为“Visual Basic For Application”。VBA不仅已经有20年的历史，其开发和语法已经趋于完善。
Excel的功能区界面已经相当成熟，使用功能区对Excel进行各种操作，既美观又方便，而且可以在相同的空间中放置更多的按钮，又可以节约查找命令按钮的时间。对于VBA开发者而言定制属于自己的专用选项卡可以提升调用过程的便捷性，同时也使自己的程序更好的融入Excel中。借助Custom UI Editor for Microsoft Office软件对Excel的Ribbon功能区进行编程以增加自己需要的模块，使其在Excel界面中就能直接进行阀门井尺寸统计的操作，后续处理也能应用Excel本身自带的功能，减少了重新封装软件的工作量。本程序增加的功能区模块为一个一级菜单和两个下拉二级菜单。
使用Custom UI Editor for Microsoft Office[1]软件对功能区进行代码编写的结构相当严谨，每句代码的顺序都有严格的规定，而且所有代码都必须配对，严格区分大小写。在代码编辑中第一句和最后一句是配对的，它是功能区代码的根或者称之为壳，类似于VBA中的Sub与End的关系。首尾两句都“customUI”,表示这是一个自定义功能区的容器。首句代码使用了“<>”符号。部分主要代码；Ribbon功能区界面见图1。
图1 Ribbon功能区界面
2.2 软件主程序和窗口界面的设计
2.2.1 软件逻辑设计
阀门井的尺寸由长和宽决定，确定阀门井的尺寸其实就是确定阀门井的长和宽。现定义阀门井长的方向为X方向并且X方向分为X左方向和X右方向，宽的方向为Y方向。设计流程图见图2。
图2 软件程序逻辑设计流程图
第一步，单击功能区彩云之南菜单，然后单击查找的二级菜单。在弹出的窗口中单击复位，清空原有数据。输入阀门井的编号、X左方向阀件的种类和数量，求出X左方向的阀件长度总和。
第二步，如果阀门井在Y方向上没有阀件那么保存结果并退出。如果在Y方向上有其他阀件的话，继续输入Y方向的阀件种类和数量，求出Y方向的阀门件的长度总和。
第三步，如果阀门井在X右方向上没有阀件那么保存结果并退出。如果X右方向还有阀件，则继续输入X右方向阀件的种类和数量，并求出结果。
第四步，单击统计按钮，进行阀门井尺寸的分类汇总。在此环节使用了Excel自带的函数功能对上一步传递过来的阀门井的尺寸数据进行了分类汇总和筛选。
第五步，已经设计好的典型尺寸的阀门井工程量已经保存在了Excel的工作簿中，通过Excel函数的筛选，对筛选结果稍加整理，便可以得出最终的工程量汇总结果。至此阀门井的特性表和工程量的汇总表已经全部整理好。
2.2.2 软件操作界面设计
VBA的扩展区自带UserForm模块[2]。UserForm即用户窗体，通过窗体可以操作工作簿、工作表、单元格、批注、图形对象等，也可以利用窗体设计一个单独的操作界面，完全脱离单元格、工作表等数据载体而工作。本程序中VBA的窗体主要用于一下几个方面：①设计登录窗口;②制作数据输入界面;③数据查询界面；④选项设置窗口。
为方便工程设计人员使用本程序，利用Excel中VBA模块的窗口编辑操作界面，分别设计了数据填写、清空、计算、求和、下一步等操作按钮。为了方便操作人员识别法兰、变径、三通等阀件，在每个阀件的按钮下加上了阀件的附图和典型管件节点的截图。为便于操作者快速上手还在每个界面中补充了使用说明。其余每个界面均类似，窗口设计见图3。
图3 程序窗口的界面截图
2.2.3 软件主程序设计
1)阀门井尺寸统计的一个关键点在于，在窗口输入管件数据后程序能够自动查找出对应管件的尺寸。为解决这个问题，首先在另一个工作薄中建立管件尺寸的数据库，将不同管件的管径和尺寸分别填入单元格中，固定其位置。在语言选择中，采用了VBA中的if循环语句，使计算机执行循环查找的功能。其语法如下：
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)为保证多个操作按钮同时执行命令，利用VBA语法中的Call命令。其语法如下：
Sub Y计算_单击()
Call 法兰1_单击
Call 三通1长_单击
Call 减压阀_单击
Call 排气阀_单击
Call 三通2长_单击
Call 蝶阀_单击
Call 伸缩节_单击
Call 变径_单击
Call 法兰2_单击
End Sub
3)录制宏[3]可以自动产生代码，既减少了编写代码的时间，又为程序编写人员提供了参考，在忘记代码拼写方式的前提下仍然可以编程，完成既定的工作。部分繁琐操作直接编写程序语言比较复杂，为了发挥Excel平台的优势，采用录制宏的方式获得程序语言。对于复位的操作录制的宏过程如下：
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 工程应用及效果检查
使用本程序对常规阀门井设计耗时统计表中的4个项目进行比较验证，同时在3个新项目中推广本程序。通过在7个项目中的应用验证，将老项目活动前后耗时以及新老项目活动后耗时进行对比，见表1。由表1可以看出使用程序后极大的提高了设计效率。
表1 新老项目活动后耗时对比表
通过对现状调查中7个样本项目合同额及工作时间进行对比，按常规阀门井设计占工程总设计时间的10%进行考虑，可以看出应用程序成果前，项目组成员人均日创造效益为1660元，程序成果应用后，项目组成员人均日创造效益为1885元，提高高效节水类项目人均日效益值225元。效益对比见表2。
表2 常规设计与程序应用效益对比表
4 结语
本文简要介绍了VBA在Excel二次开发中的应用。详细介绍了Custom UI Editor for Microsoft Office编辑器在功能区扩展开发中的应用及程序的编写，以及循环语句的编写、宏的录制、VBA窗口的设计。其实本程序的查找功能同样适用于其他设计环节的工作。比如造价人员在做概算时需要查找单价，而翻阅价格信息同样很繁琐。如果把单价信息的原始数据提前存储到数据库中，再对窗口界面进行适当修改，同样可以达到快速查找单价的目的。希望本程序对水利项目设计及Excel的VBA二次开发有所帮助。
