Excel表格中VBA编程应用实例

2024年9月13日发(作者：)

经济建设　

Ｅｘｃｅｌ表格中ＶＢＡ编程应用实例　

摘要：在Ｅｘｃｅｌ表格中用ＶＢＡ程序设计模板文件，实现页面能显示带括号及注释的计算表达式，跨页ｇｌ用，变量引用，构建专用　

函数等功能，方便工程算量。　

关键词：Ｅｘｃｅｌ表格显示计算表达式专用函数跨页引用变量引用　

刖旨：　

Ｆｏｒｉ＝１　ＴｏＬｅｎ（Ｓ１　

ＩｆＭｉｄ（Ｓ，ｉ，１）＝”＜”Ｔｈｅｎ　

Ｄｏ　

工程算量是工程预结算的基础，基于ＣＡＤ平台的专业算量软件的出现，　

是工程造价领域的又一次革命。在专业算量软件还未全面普及的情况下，还　

ｉ＝ｉ＋１　

有相当一部分造价人员，选择科瑞计算簿和Ｅｘｃｅｌ表格作为工程算量的工具。　

虽然，Ｅｘｃｅｌ表格在运算方面准确、便捷，但是，在显示计算表达式及文字标志　

注释方面，不够直观，以致于在检查、复核较为复杂的计算式时，失去优势。有　

感于此，本人在Ｅｘｃｅｌ表格中用ＶＢＡ程序设计了一个模板文件，旨在通过　

Ｅｘｃｅｌ平台，实现能在页面显示计算式，而且能在计算表达式中采用大、中、小　

Ｉｆ　ｉ＞Ｌｅｎ（Ｓ）Ｏｒ　Ｍｉｄ（Ｓ，ｉ，１）＝”＜”Ｔｈｅｎ：ｅａｃｓｔｒ＝”注释符错误！ｌｌ＿Ｅｘｉｔ　

Ｆｕｎｃｔｉｏｎ　ｋ叩Ｕｎｔｉｌ　Ｍｉｄ（ｓ，ｉ，１）＝”＞”　

Ｅｌｓｅ　

ＳＳ＝ＳＳ＋Ｍｉｄ（Ｓ，ｉ，１）　

ＥｎｄＩｆ　

Ｎｅｘｔ　

括号，造价人员可根据需要构建自己的专用函数，可跨页引用，变量引用。总　

之，能为习惯于使用Ｅｘｃｅｌ工具的人员提供更多的帮助。　

一

、

模板文件的功能　

ＩｆＴｒｉｍ１　ｏ　Ｔｈｅｎ　ＳＳ一嚣娟！ｇ（１ｓｓ．　

Ｉｆ　热）０　Ｔｈｅｎ　ＳＳ＝　鳓瓤ｓｓ，　ｉ　

Ｉｆｎｓｅｌｅ＝１Ｏｒｎ　ｌｅ＝２ｌｔｌｅｎ．　

（　）　

１．以“＜＞”号替代“【】’’作为注释符，允许表达式中按习惯采用（、１、［、ｌ、（、）　

括号，小括号内可以继续采用小括号，并对其合法性进行校验。这一功能有利　

于多个括号时的检查，使表达更清淅。　

２．变量引用是减少工程量录入和增加工程量准确性的重要手段，模板文　

ＳＳ＝　骣　ｓｓ）　

Ｉｆ　ｓｓ＝　函数错误开方值小于零　Ｔｈｅｎ　

￡　＝　函数错误开方值小于零”　

ＥｘｉｔＦｔｍ￣ｉｏｎ　

ＥｎｄＩｆ　

件允许使用变量引用，与科瑞类似，也存在差异。每次计算表达式允许十个初　

始变量，用于填写建筑物特征等不须变化的量；每次计算表达式允许一个中　

间计算变量。　

３．自定义函数。ＥＸＣＥＬ其实是一个平台，能否发挥其功能，取决于使用　

者，模板文件允许使用者按自身应用需要，编写自定义函数，从而简化录入和　

简化表达式。　

Ｉｆ　ｓｓ＝１　数错误半径小于零　Ｔｈｅｎ　

ｅｆｌｅ，ｓｔｒ＝”函数错误半径小于零”　

Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

ＥｎｄＩｆ　

ＩｆＳＳ＝”弓函数错误”Ｔｈｅｎ　

ｅａｅｓｔｒ：”弓函数错误”　

Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

二、使用说明　

１．设定一个专用页面ｓｈｅｅｔ，用以填定初始变量。在一个单元格中填人：　

＝

ＥｎｄＩｆ　

ＢｉａｎＬ（Ｂ３，Ｃ３，Ｂ４，Ｃ４，Ｂ５，Ｃ５，Ｂ６，Ｃ６，Ｂ７，Ｃ７，Ｂ８，Ｃ８，Ｂ９，Ｃ９，ＢＩＯ，ＣＩＯ，Ｂ１　１，Ｃ　ｉ　１，　

Ｉｆ　ｓｓ＝　乘方函数错误”Ｔｈｅｎ　

ｃａｃｓｔｒ＝　乘方函数错误　

Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

Ｂ１２，Ｃ１２）　

说明：　

ＥｎｄＩｆ　

ＥｎｄＩｆ　

ＢｉａｎＬ（）模块负责产生一个专用字符串，计算模块￣ａｅｓｔｒ通过调用该字符　

串，将初始变量引入，对表达式进行解释和替换。　

Ｂ３单元填人变量名，即表达式中的中文或英文，ｃ３为Ｂ３的计算用值，余　

同。此处引用二十个单元格，共十个变量。当初始变量多于十个时，可以分组，　

在多个单元格中用ＢｉａｎＬ产生专用字符串。　

２．在计算结果单元格中填人：　

Ｉｆｎｓｅｌｅ＝２Ｔｈｅｎ　

ＳＳ＝ＺＤＹＨＳ（ＳＳ１　

ＩｆＳＳ＝”函数参数错误”Ｔｈｅｎ　ｃａｃｓｔｒ＝”函数参数错误ｌ１．Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

ＥｎｄＩｆ　

Ｓｔ＝ＳＳ　

Ｈｕａｈａｏ＝０　

Ｈａｖｅ＝０　

＝ｃａｃｓ｜ｒ（参数１，参数２，参数３，参数４，参数５）　

说明：　

参数１：计算表达式单元格　

参数２：绝对引用初始变量专用字符串所在单元。计算表达式不需要引用　

时，填入空串””。　

参数３：填０，或１，或２。０表示本次计算不考虑自定义函数，以加快计算速　

度，１和２表示本次计算考虑自定义函数，其中１只调用Ｆｕｎｚｈ　０模块，２调用　

ｓｔａｒｌ＝０　

ｓｔａｒｎ＝０　

ｓｔａｒｋ＝０　

ｓｔＨａｖｅ＝０　

ＦｕｎｚｈＯ模块和ＺＤＹＨＳ（）模块。将自定义函数模块分为两档，只是为了适合不　

Ｉｆ　Ｍｉｄ（Ｓｔ，ｋ，１）：”［”Ｏｒ　Ｍｉｄ（Ｓｔ，ｋ，１）＝”］”Ｏｒ　Ｍｉｄ（Ｓｔ，ｋ，１）＝”（”Ｏｒ　Ｍｉｄ（Ｓｔ，ｋ，　

同需要和加快速度而已，使用者可以按不同的需要进行程序变更。　

１）＝”）”ＯｒＭｉｄ（Ｓｔ，ｋ，１）＝”｛Ｔ．ＯｒＭｉｄ（Ｓｔ，ｋ，１）＝”）．．Ｔｈｅｎ　ｓｔｒｉｎｇＨ＝ｓｔｒｉｎｇＨ＋Ｍｉｄ　

参数４和参数５为配合使用，将中间变量引入。参数４为计算式中引用的中　

（ｓｔ，ｋ，１）　

间变量名，参数５为该中间变量的值。　

Ｎｅｘｔ　

Ｆｏｒ　ｋ＝１　ＴｏＬｅｎ（Ｓｔ１　

三、ＶＢＡ程序　

模板文件共用六个模块程序：ｃａｃｓｆｒ（）、ＢｉａｎＬ（）、ＲｅＢｌｇ（）、ｌＹｌｉｃｌｃｌｈ（）、Ｆｕｎｚｈ　

（）、ＺＤＹＨＳ（），鉴于篇幅，本文只提供ｅａｅｓｔｒ（）如下：　

Ｐｕｂｌｉｃ　Ｆｕｎｃｔｉｏｎ　ｃａｅｓｔｒ（Ｓ，Ｘ，ｎｓｅｌｅ，Ｍｉｄｈ，ＭｉｄＶ）　

Ｆ０ｒ　ｊ＝ｌ　Ｔｏ　ＬｓＢ　醇秘Ｉｓ　．｜　

Ｉｆ　ｄＬ５臻ｌ　瑶　ｊ．１）。　（　＂ｆｈｅｎ：ｓ　￡；＝雾蕊；一ｌ：ｌ【１ｌ珈ａ　ｔ　１　

Ｉｆ　薹ｉ威　ｌ尊ｇ　＾】）＝　）　Ｔｈｅｎ：５硌ｆｌ＝取　一ｌ　ｌｈＩ　ｌ【ａｏ＝１　

Ｉｆ　嚣　￡霹　ｌｇ　Ｊ：１）＝　【　Ｔｈｅｎ：蕺∞　盎聪曼ｆ　一１：Ｈａｖｅ掌ｌ　

Ｉｆ　ｄ　嫩　Ｊ．１）＝　Ｔｈｅｎ：取ａｍ　羁姗－１：Ｈａｖｅ　１　

‘２　‘　

囵墨囝．墨　

Ｉｆ　ｄ￡ｓｔｒ１ｎｇ琏Ｊ：Ｉ）　ｌ　Ｔｈｅｎ：．￣ｒｋ—ｓｔａａｒｋ一１　

经济建设　

含括号！　

Ｉｆ　ｄ（蹴ｌｎｇ　Ｊ　１）＝　）　Ｔｈｅｎ：ｓｔａｒｋ；ｓｔａｒｋ—Ｉ　

Ｉｆ　ｓｔａｒｋ＜０　Ｏｒ　ｓｔａｒｎ＜０　Ｏｒ　ｓｔａｒｌ＜０　Ｔｈｅｎ　ｃａｃｓｔｒ＝”），］，）错误！＇＿＝Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

Ｉｆ　Ｈａｖｅ＝１　Ａｎｄ　ｓｔａｒｋ＜＞０　Ｔｈｅｎ　ｃａｃｓｔｒ＝”），１交叉错误１１．：Ｅｘｉｔ　Ｆｕｎｃｔｉｏｎ　

ＩｆＨａｖｅ＝１　Ａｎｄ　ｓｔａｒｋ＝０ＴｈｅｎＨａｖｅ：＝０　

四、示例　

１．安装工程中，有时会用专用的表页，对一些小电器进行统计，则町采　

用中间变量的方式，引至计算式中　

Ｉｆ　Ｈｕａｈａｏ＝１　Ａｎｄ（ｓｔａｒｋ＜＞０　Ｏｒ　ｓｔａｒｎ＜＞０）Ｔｈｅｎ　ｃａｃｓｔｒ＝”），］，｝错误！”：Ｅｘｉｔ　

Ｆｕｎｃｔｉｏｎ　

２．以模板文件为例，在ＺＤＹＨＳ（）巾已构造ｒ函数：直方梁砼（轴｝乏，扣长　

１，扣长２，梁宽，梁深，扣板厚）和直方梁模（轴长，扣长ｌ，扣长２，梁宽，梁深，扣板厚）　

，

ＩｆＨｕａｈａｏ＝１　Ａｎｄｆｓｔａｒｋ＝０　Ａｎｄ　ｓｔａｒｎ＝０１ＴｈｅｎＨｕａｈａｏ＝０　

Ｎｅｘｔ　

则表达式：直方梁砼（４５．２＜Ａ轴一Ｆ轴ｘｌ轴＞，０．２５＜扣柱Ａ柱＞＋０．５　４＜柱＞＜扣柱　

ＢＣＤＥ＞，０．６５＜扣柱Ｆ＞，Ｏ．４＜梁宽＞，０．８＜梁深＞，０．ｔ５＜板厚＞）计筒结果为　

】０．９９８５ｍ　。　

Ｉｆ　ｓｔａｒ　＜＞０　Ｔｈｅｎ　ｓａｃ＃ｔｒ＝　［】错误ｆ　：Ｅｘｉｔ　Ｆｔｍｃｔｉｏａ　

Ｉｆ　ｓｔａｒｋ＜＞０　Ｔｈｅｎ　ｇ　ｃｓｔｒ＝　（）错误　．ＥｘｉｔＦｕｎｃｔｉｏｎ　

Ｉｆ　ｓｔａｒｌ＜＞０　Ｔｈｅｎ　ｃａｃｓｔ　＝　｛ｊ错误！　：Ｅｘｉｔ　Ｆｕ曲ｃｔ：ｏｎ　

Ｆａｔ】　１　ＴｏＬｅｕＣＳＳ）　

＂ＩＴ；　ｉｉｄ（ＳＳ．１　１）　

复制表达式，将“砼”改为“模”直方梁模（４５　２＜Ａ轴一Ｆ轴ｘｌ轴＞，０　２５＜扣柱　

Ａ柱＞＋０．５　４＜柱＞＜扣柱ＢＣＤＥ＞　０．６５＜扣柱Ｆ＞　Ｏ．４＜梁宽＞　０．８＜梁深＞，０．１５＜板厚　

＞）计算结果为７１．９１５　Ｉｌｌ　。　

如果没有调用初始变量和中间变量，计算单元格中填人＝ｃａｃ：ｓｔｒ（Ｅ２，…’，２，　

…’

，

ＩｆＴＴ＝　【”ｏｒｌｑ＂＝”（　Ｔｈｅｎ：ＴＴ＝　（　・　

Ｉｆｎ＝　ｌ”ｏｒＴＴ；”）　Ｔｈｅｎ：丌＝　）　＋　

ＳＳＳ　ＳＳＳ一丌　

Ｎｅｘ卜　

…’）则可，Ｅ２为计算式所在单元格，２表示调用ＺＤＹＨＳ（）。　

３　以模板文件为例，在Ｆｕｎｚｈ（）中已构造ｒ函数：弓面积ｆ半径表达式　

弓高）则表达式：　

Ｉｆ　ＳＳＳ＝　１１１姐ｃ　

Ｅｎｄ　Ｆｕｎｃｔｉｏｎ　

＝０　Ｅｌｓｅ　ｃａｃｓｔｒ＝ＡＤｏｌ￣ｃａｔｉＱｎｌＥ、　ｎｌａ　ｆＳＳＳ　

弓面积ｆ４，２）　Ｏ．１２＜板厚＞计算结果为１．１７９２３４ｍ　，是半径４ｍ、飘出２ｍ和厚　

度０．１２ｍ的弧形阳台板砼量。　

４．表１是模块程序运行结果示例　

其它模块说明：　

ＢｉａｎＬ（）对初始变量名按长度排序后，产生一个专用字符串；格式为：　

变量数ｌ变量１名ｌ变量１值Ｉ变量１名长度１．．＿…　

ＲｅＢｌｇ（）将专用字符串解开后对表达式进行替换。　

五、小结　

本模板文件与科瑞计算簿比较，有如下优缺点：　

１　计算式表达可采用大中小括号，优于科瑞；　

Ｍｉｄｄｈ（）将表达式的中间变餐名替换为变量值。　

Ｆｕｎｚｈ（）、ＺＤＹＨＳ（）开放式ｆ｛定义函数，应用者可对其进行开发，函数的　

表达格式为函数名（参数１，参数１，参数１，…），参数可以是计算式，但不能包　

２　可构建白已的专用函数，优于科瑞；　

３　中间变量引用不如科瑞方便，需要指定引用单元。但可跨页引用，则　

优于科瑞。　

袁１　

字号　项置　箭缝　心号　丹－蔓采　

直方粟毪　４５．２＜ｚ、琢ｌ＞，ｏ．２５＜船Ａ＞－Ｏ．５ｑ＜桂＞＜蛔　

ＢｃＤＥ＞，０，５５（妇Ｆ＞　Ｏ　４（壹＞，０　８（；　

２　

３　

Ｃ２０置方粱　

Ｃ２０甄形鬃　

Ｏ．１　扳＞）　

ｌｌ，００　ｍ３　

５．ｉ７　ｍ３　

自定义函号ｆ嗣　

自定义嚣辱ｌ用　

计蕈港果　单龟　备注　

：　ｃ２ｏ直　粱　１：．ＯＯ　丑３　自烹义国辱！罚　

【４ｓ．２￣＇－Ｆｘ：＞一《Ｏ　２５＜妇＾＞＿ｏ．　４（控×妇　

ＢＣＩ￣＞－＇０．６５＜扫Ｆ＞）：的．４，烹＞￥　０．Ｂ＜瀑　Ｏ．ｉ　５＜板＞　

弧方桑　（４０＋５，３５，０．是０．５，０．４　０．Ｂ＇０．Ｉ５）　

４　Ｃ２０弧形阳台援　号蓄　４，２　粕。Ｉ２＜扳覃＞　

直方桑馕（４５．２＜Ａ轴、Ｆ轴慰辅＞　０．２ｓ＜９：ｒｌ￣Ａ｛耋　

：．ｉ８　ｔ３　自定义两号｝蜀　

Ｓ　直＝亨粟缜板　＞．ｏ．　４＜垃＞（妇挂ＢＣＩ￣＞，０．６５＜妇１童Ｆ、，０。４＜粟烹　

》　０．８＜粱源＞，０．ＺＳ＜投孥＞）　

了１．９２　ｍ２　宜定义两号！焉　

６　弧形鬟填板　

鬃馕故安装合计　

弧方桑攫（ｚ２，３ｓ，０　５　Ｏ　ｓ，０．４，０　８，０．：ｓ１　０．　６　１２．　

８２．６８　ｉｔ２　

自定义硒弓Ｉ舄　

８　单管荧玎安装　单譬荧玎台　诺见詹表＞　

｛　琶亳篇ｎｉ暮鍪＞２＜营　

：：ｌ　十　幸『司受盂引用　

９　Ｄ　Ｏ毫绫曹畴装　Ｄ　Ｏ＾　

＞￥［３．４　ｓ－２．　３　４一　．　：．４　＜孳ｌ下＞：一＜甄爱奄箱ｎ２　

薅＞［３－４－５－　．４一　３．　１．４　３＜号！下＞：＃４（营＞ｊ犍＜蓐　

＞取４（｛曩》　

：Ｏ　．８ｏ　

Ｄ　

ｌｏ　ｚＲ喝　２．５穿管　

３（竣）．｛２＜管　ｌ＜缭＞　（４．　ｉ．４）＜引下＞　蛇＜　

３：Ｉ２＋Ｏ０　瓤　串阍变量弓ｌ雳　

屡　４＜　

１：　

Ｉ２　

１３　

Ｐ　：ｏ。ｊ掌水管安装　

暖琶职　

蹈嚣琢　

ＰＬ－Ｉ、：６　［总意．ｏ．５＜埋±毫＞：虬６（营＞　

４Ｏ，ＪｌＩＩ＞阜４ｏ（－＿　事　

匿匿职（舣ｍ＞　

２８　ｏ。　

５０２６．５５　

５ｏ２６．５Ｓ　

韧妁变量辱ｌ胃　

ｌ４　

ｌ５　

正切∞寰　

乘方４５－０．２３　

５纽（３。）　Ｓ毁ｔ３Ｏ）馕示强（３０）＞　

秉方（４５，＿ｏ．２３）＜表示４５－０．２３＞　

Ｏ．Ｓ８　

Ｏ，４２　

王６　

１７　

１３　

一组胡女媛置引异ｌ示铡　

一级幻女姨量引用示铡　

：蹑初蚰变量号ｌ用示弱　

２－惠－￣ｃ－２　

２－晷离　

建筑爱　－曩蕈费率　

３４４．２０　

８Ｏ．００　

５Ｏ　６Ｏ．ＯＯ　

奶始变量号！嗣　

胡始变置号ｌ写　

奶始变量浮ｌ焉　

。２５６・