用VBA编写Excel自定义的累加函数郑云勇云南楚雄思远投资有限公司摘要:Excel是常用的数据统计分析软件,本文介绍了用VBA编写加载宏的方法来扩展、定制Excel,以适合工程统计专业的特殊需要。
下面,讨论如何实现一个自定义的具有任意合计形如“30.2m/165.45m3”的“分子/分母”工程量统计功能的函数,由于Sum函数系统已经内置,我们要实现的函数不妨命名为uLSum和uRSum。
关键词:Excel,定制,加载宏1引言电子表格软件Microsoft Excel具有快捷方便的数据输入方式和强大的数据处理能力,是工程地质中常用的数据统计分析软件,为我们的工作带来了极大的方便。
但Excel毕竟只是一个通用的办公软件,对于工程专业中的各种特殊需求,它自然不会有专门的功能支持。
比如:采矿工程师在做每年的采掘生产计划时,通常遇到要累加掘进工程量的合计数,即“30.5/125.6”加“120.2/625.8”的合计,我们通常的做法是分步完成,先将分子之、分母之和求出来后,在填入单元格中。
或者将分子、分母分别填入两列中,分别用sum函数求和。
这样既劳神,又容易出错,特别是对大量数据进行复杂的公式计算。
如果从头编写一个独立、专门的计算程序来处理,则似有小题大作之嫌,而且还不能与Excel无缝集成。
那么有没有更好的解决办法呢?答案是肯定的,那就是定制Excel,通过编程扩展它的功能。
事实上,Excel作为Microsoft最优秀的软件之一,很早就开始提供了二次开发的接口,4.0版以前有XLM,4.0版又发布了Excel C API,5.0版则内置了VBA。
现在的Excel,除了人所共知的操作功能外,还是一个完善的软件开发平台。
它拥有完备的ActiveX Automation服务器和客户机机制,可以通过编程对其进行全方位的扩展、定制,实现各种自定义功能。
基于ActiveX Automation技术,使用C/C++等多种支持Automation的编程语言均可控制Excel,但我们最为熟悉和常用的还是内置于Excel中的VBA。
2VBA简介Visual Basic for Application(VBA)是Microsoft面向最终用户的应用软件编程语言。
它最早出现于Microsoft的Excel和Project中,如今VBA已成为VB和所有Office产品的组件。
另外,越来越多的软件开发商购买了VBA语言的使用权,如常用的绘图软件AutoCAD等均已支持VBA作为二次开发工具。
这意味着我们懂得VB,就已经懂得了VBA,反之亦然。
VBA的最大特点和最大优点是利用面向对象(OOP)的ActiveX Automation技术,使语言的引擎在技术上与开发环境分离,这可以从在任何VBA的IDE环境中都可以看到VBA单独的入口得到印证。
因此,VBA的功能在很大程度上依赖于它的客户显露的Automation 接口,例如,VB与Office套件中的VBA,能完成的功能就大不一样。
当然,如其他许多通用编程语言一样,VBA可以方便地直接调用许多传统DLL(VB不能生成)模块中的函数,只要这些函数使用的数据类型能被VBA处理。
另一方面,由于VBA是基于ActiveX Automation 技术,它可以使用任何支持Automation技术的组件中的类和对象,换句话说,它能够集成系统中的各种支持Automation技术的应用程序共同工作。
VBA语言简单易学,但功能却不寻常,其运行效率对于普通的应用也完全能满足需要。
一般而言,使用VBA可以做到:①定制和扩展客户应用程序功能;②将客户应用程序及数据集成到其他应用程序中。
在Excel中,VBA最常见的用途即是录制宏。
每个可以用键盘或菜单命令完成的动作均能被宏记录下来,然后对不同数据进行(或播放)同一批操作。
录制宏可以保存在当前工作簿中,也可以保存在个人工作簿中。
当宏保存在个人工作簿中时,当前用户每次打开Excel时便都能使用其中包含的宏。
记录宏把需要用户重复的工作自动化,这其实已经是对Excel 的定制。
但它仅仅是定制或扩展Excel的开端。
因为,系统自动记录的宏虽然常常罗列了一大堆代码,但真正有用的却很少,且没有嵌入错误处理方案,至于我们需要实现的系统中没有的特殊功能,自然不可能有记录。
另外,录制宏也没有能与整个Excel系统完全融合,不便于提供给其他用户使用。
利用VBA定制Excel,Microsoft 推荐采用加载宏-即全局宏的方式。
3函数实现在Excel电子表格中最常使用的函数应该是Sum了,它是内置的;当然,在Excel中内置的函数还有很多;但是,往往我们使用的函数Excel中并没有直接提供,或者提供了我们并不知道,这个时候可以自己使用VBA定义一个自己需要使用的函数参与电子表格单元格的计算,这在日常工作中可能是经常要遇到的一个问题。
下面,讨论如何实现一个自定义的具有自动合计分子分母式样功能的函数。
首先,想到的应该使用VBA,毫无疑问,最直接的针对Excel 功能的扩展来源于VBA。
事实上,我们可以在Excel的Microsoft Visual Basic编辑器的模块中增加一个公共函数来实现该自定义函数,该公共自定义函数是可以在Excel单元格中直接像使用Excel 内置函数一样使用的。
使用菜单“工具——宏——Visual Basic编辑器”或者直接使用快捷键【Alt+F11】组合键,打开Microsoft Visual Basic编辑器窗口;在“工程”浏览器窗口中的树状目录的任一项目上单击右键,使用“插入——模块”命令;OK,在这个模块编辑器中,即可以开始编辑自定义函数了。
在此之前,我们应该考虑该函数的参数形式,和内置的Sum函数进行类比,我们可以发现Sum函数的参数应该是一个或多个单元格,而在VBA中对应的就应该是一个Range对象(关于Range对象可以参考Microsoft Visual Basic帮助中的Excel VBA对象模型),也就是说我们要自定义的函数的参数应该是一个Range对象,由此,我们可以在“模块1”的编辑器中自定义几个如下的函数:用ulSUM、urSUM求m/m3的值3.1编写函数分别取得分子和分母的数值'取得如“200/800”字符串右边的数字“800”(函数1)Public Function fipR(str1As String)As DoubleDim m As IntegerDim ss As Stringm=查询分隔位置(str1)If Len(str1)=0Thenss=0Elsess=Mid(str1,m+1)End IffipR=CDbl(ss)End Function'查询分子式中“/”字符的位置(函数2)Public Function查询分隔位置(ByVal sstrcha As String)As IntegerIf sstrcha=""Or IsNull(sstrcha)=True Then Exit Function查询分隔位置=InStr(1,sstrcha,"/",1)End Function'取得如“200/800”字符串左边的数字“200”(函数3) Public Function fipL(str1As String)As Double Dim m As IntegerDim ss As Stringm=查询分隔位置(str1)If Len(str1)=0Thenss=0Elsess=Left(str1,m-1)End IffipL=CDbl(ss)End Function'累加分子(函数4)Public Function uLSum(ParamArray x())As Double Dim i As IntegerDim j As IntegerDim k As IntegerDim rtn As Doublertn=0For i=0To UBound(x)For j=1To x(i).Rows.CountFor k=1To x(i).Columns.Countrtn=rtn+fipL(x(i).Cells(j,k)) Next kNext jNext iuLSum=rtnEnd Function'累加分母(函数5)Public Function uRSum(ParamArray x())As Double Dim i As IntegerDim j As IntegerDim k As IntegerDim rtn As Doublertn=0For i=0To UBound(x)For j=1To x(i).Rows.CountFor k=1To x(i).Columns.Countrtn=rtn+fipR(x(i).Cells(j,k)) Next kNext jNext iuRSum=rtnEnd Function函数很简单,遍历Range对象的所有单元格,使其中的数字相加后返回即可。
在上面的描述中,我们使用到VBA的ParamArray特性,使得uLSum、uRSum函数既能接受连续单元格区域中的数据求和,也能接收不连续单元格区域中的数据求和,如uLSum(B2:C3,B5:C6),函数4和函数5均能接受不定数量的多个参数。
保存更改后,返回到工作簿Book1界面,在连续的单元格(如B2:C3)中录入几个数字,然后在另外一个单元格(如E4)中录入“= uLSum(B2:C3)&"/"&uRSum(B2:C3)”,也就是说,我们使用我们自定义的合计函数uLSum、uRSum来计算区域B2:C3中的工程量数字之和。
经过验证,我们可以发现这两个函数可以实现内置函数Sum 的合计功能。
至此,我们可以这样使用类似于=Sum(B2:C3,B5:C6,B8:C9)的公式了。
也就是说,我们已经实现了一个和内置Sum函数一样功能的自定义函数了。
3.2实例当然,在VBA函数编写好并存盘后,今后打开文件,必须选择【启用宏】按钮,才能在工作簿中使用自定义的函数。
如下图所示。
¨霉C日g虬悍e000/2监00ee≮霉哥日Ji000、aa日00日000、Sie00Si3日0、Jija0J8ej0、i日日00ia0J0/33日80J3368/Jb0日bJJJ83bJ\8iaaoidJ¥☆,Ⅻ¥甘¥甘k喜ik}!斟目亩怀:《#并ni遵%■目j008士e甘望县弓埕b曾b喜¥Ees・}=nT2Ⅲ(Ee3:Ee^)F。
\。
F“2Ⅲ(Ee3:Ee^)=工T…州删一…・B、Ⅱ;i■司毫*・w≈一:]醴1’117蚵寻?1留・^・・矛∑・7f甜霄第五届矿山技术论文发布会实例应用,如下图所示:在E62单元格中,有公式“=uLSum (E63:E67)&"/"&uRSum(E63:E67)”,如此即可分别求出分子分母的合计值了。