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，会使代码处理得更快，这种差异都能发现，真令人敬佩！