vba 自定义function返回值_Excel VBA解读(133): 编写高效的Function过程——让代码运行更快的技术...

学习Excel技术,关注微信公众号:

excelperfect

引子:fastexcel.wordpress.com中,有很多如何编写高效的Function过程的经验,本文及接下来的一系列文章,与大家分享这些经验。

在Excel中,使用VBA编写用户自定义函数很容易。例如,想要写一个自定义函数,计算单元格区域的平均值,但要排除非数字或小于误差值的单元格。

我们可以这样编写代码:

Function AverageTol(theRange,dTol)

   For Each Thing In theRange

        If IsNumeric(Thing) Then

            If Abs(Thing) > dTol Then

                AverageTol = AverageTol + Thing

                lCount = lCount + 1

            End If

        End If

   Next Thing

   AverageTol = AverageTol / lCount

End Function

代码遍历给定单元格区域的每个单元格,如果单元格是数字且大于误差值,则对其求和并使计数器增加1,最后用总和除以计数和得到结果。

注意到,代码中没有显示声明变量,VBA默认所有变量的类型为Variant型。如果显示声明变量为合适的类型,代码运行速度将会更快:

Function AverageTolA(theRange AsRange, dTol As Double)

   Dim oCell As Range

   Dim lCount As Long

   For Each oCell In theRange

        If IsNumeric(oCell) Then

            If Abs(oCell) > dTol Then

                AverageTolA = AverageTolA +oCell

                lCount = lCount + 1

            End If

        End If

   Next oCell

   AverageTolA = AverageTolA / lCount

End Function

然而,上述代码每次将数据从Excel单元格转换到VBA变量时都会产生不小的开销,并且一个循环要进行3次转换,因此使程序速度变慢。如果一次性将其整体转换,则可以避免此类开销,程序运行速度也会变得更快:

Function AverageTolC(theRange AsRange, dTol As Double)

   Dim vArr As Variant

   Dim v As Variant

   Dim lCount As Long

   On Error GoTo FuncFail

    '将单元格区域转换为variant型数组

   vArr = theRange

   For Each v In vArr

        If IsNumeric(v) Then

            If Abs(v) > dTol Then

                AverageTolC = AverageTolC + v

                lCount = lCount + 1

            End If

        End If

   Next v

   AverageTolC = AverageTolC / lCount

   Exit Function

FuncFail:

   AverageTolC = CVErr(xlErrNA)

End Function

语句vArr=theRange接受单元格区域中所有的值并将其转换成Variant型二维数组。代码遍历Variant数组的每个元素。

这样还不够!如果将:

vArr = theRange

替换为:

vArr = theRange.Value2

代码运行速度将更快。使用.Value2代替缺省属性.Value使Excel做更少的处理,因为.Value检查单元格是否格式化为货币型或日期型,而.Value2只将包括日期型和货币型在内的所有数字视为双精度浮点型。

此外,使用双精度浮点型而不是Variant型,可以使程序速度得到进一步改进,将循环语句代码修改为:

   Dim d As Double

   Dim r As Double

   On Error GoTo skip

   For Each v In vArr

        d = CDbl(v)

        If Abs(d) > dTol Then

            r = r + d

            lCount = lCount + 1

        End If

skip:

   Next v

点评:使用.Value2代替缺省属性.Value,会使代码处理得更快,这种差异都能发现,真令人敬佩!

d53da37726f2d7d2af12df57205e7820.png

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页