Excel VBA解读（137）： 让使用用户定义函数的数组公式更快

学习Excel技术，关注微信公众号：

excelperfect

Excel数组公式能够做很多令人惊讶的事情。除了在输入完后要按Ctrl+Shift+Enter组合键外，与普通公式一样。本文主要研究使用用户定义函数的数组公式。

有两类数组公式：

• 单单元格数组公式输入在单个单元格中，循环遍历其参数（通常是计算的参数）并返回单个结果。
• 多单元格数组公式输入在多个单元格中，在每个单元格中都返回结果。

使用这种功能需要付出代价：因为数组公式正处理很多工作，所以计算速度很慢（特别是单单元格数组公式）。

可以将VBA用户定义函数所花费的时间分成下列组成部分：

• 调用用户定义函数的开销时间。
• 用户定义函数获取将要使用的数据的时间。
• 执行计算的时间。
• 返回结果的开销时间。

每次的VBA读写调用都有相当大的开销，因此一次读取和写入大块数据通常要快得多。

因此，应该让VBA用户定义函数在单个块中尽可能多地读取数据并将数据尽可能大地返回到Excel。

而输入多单元数组公式做到了尽可能多地写入数据到Excel，最小化了调用开销，并且通常它可以一次读取数据并多次重复使用。

如何创建多单元格数组公式？

我们沿用《Excel VBA解读（133）：编写高效的Function过程——让代码运行更快的技术》中的示例，创建自定义函数的数组版本AverageTolE函数，功能是找到除多个误差之外的数据的平均值。

假设：这些误差值全在一行中；数据和误差值都以单元格区域提供给函数；忽略错误处理；函数返回与误差行对应的结果。

自定义函数代码：

Public Function AverageTolM(theRange As Range, theTols As Range) As Variant

Dim vArr As Variant

Dim vArrTols As Variant

Dim v As Variant

Dim d As Double

Dim r As Double

Dim k As Long

Dim vOut() As Variant

Dim dTol As Double

Dim lCount As Long

On Error GoTo FuncFail

vArr = theRange.Value2

vArrTols = theTols.Value2

ReDim vOut(1 To 1, 1 To UBound(vArrTols, 2))

On Error GoTo skip

For k = 1 To UBound(vArrTols, 2)

dTol = CDbl(vArrTols(1, k))

r = 0#

lCount = 0

For Each v In vArr

d = CDbl(v)

If Abs(d) > dTol Then

r = r + d

lCount = lCount + 1

End If

skip:

Next v

vOut(1, k) = r / lCount

Next k

AverageTolM = vOut

Exit Function

FuncFail:

AverageTolM = CVErr(xlErrNA)

End Function

在这个自定义函数中：

• theTols区域被强制转换成变体数组：vArrTols = theTols.Value2
• 创建相同大小的输出数组：ReDim vOut(1 To 1, 1 ToUBound(vArrTols, 2))
• 遍历误差数组并填充输出数组
• 将输出数组赋值给函数变量：AverageTolM = vOut

注意到，声明函数返回变体（包含一个数组）而不是返回变体数组。

小结：

1.在许多实际的例子中，使用多单元格数组的用户定义函数可能是最快的计算方法。

2.将通常的用户定义函数转换成多单元格数组用户定义函数很简单。