Excel VBA解读（139）：用户定义函数计算了多次

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

excelperfect

写在开头的话：本系列从第133篇开始的22篇文章，都是翻译改编自fastexcel.wordpress.com的Making your VBA UDFs Efficient系列，可能有点高深晦涩，但确实都是好的VBA用户自定义函数编程细节技巧和经验。对于大多数人来说，你可以略过这些内容，因为我们只需懂得基础的VBA用户自定义函数知识就足够了。对于想深入研究VBA的人来说，还是值得研究和试验的。我刚开始看到这些文章的时候，一是水平有限，看不大懂，也觉得没有什么必要，所以一直收藏着。然而，随着自已研究VBA的深入，不由得佩服老外对VBA研究的深入，此时刚好Excel VBA解读系列又写到自定义函数这里，正好拿出来，边研究并翻译分享给大家。如果对这些内容没有兴趣的朋友，可以略过，免得浪费时间。

在有些情况下，当希望仅计算一次自定义函数时，Excel却会计算自定义函数多次。如果你的自定义函数需要执行很长时间，那么这可能是一个重大问题。

当被修改后Excel重新计算工作簿时，计算引擎将通过计算最近修改的公式开始，然后对剩余的公式使用最新的计算序列。如果计算引擎找到一个公式，该公式依赖于已被处理/修改（或者是易失的）但尚未计算的单元格，则会将公式重新安排到计算链的末尾，以便可以在未计算的单元格之后重新计算。

问题是计算引擎仅在计算公式/自定义函数之后才执行此重新计算，因此在每次重新计算时包含自定义函数的公式会计算多次。

下面是一个非常简单的示例，你可以在工作簿中试试。

1.设置计算模式为“手动重算”，以方便查看发生的情况。

2.在VBE中输入自定义函数：

代码语言：javascript复制

Function Tracer(theCell As Range)
   Tracer = theCell.Value
   Debug.Print Application.Caller.Address & "-" & Tracer
End Function

3.在VBE中显示立即窗口。

4.在单元格A1中输入1。

5.在单元格A2中输入公式：=Tracer(A1) 1。

6.在单元格A3中输入公式：=Tracer(A2) 1。

立即窗口中会显示：

$A$2-1

$A$3-2

现在，清除立即窗口中的数据，返回Excel并按F9键执行重新计算，可以看到立即窗口中会显示：

$A$3-

$A$2-1

$A$3-2

可以看到，首先计算单元格A3（其参数单元格A2的值显示为空），然后是单元格A2，接着又是A3，此时显示出其参数单元格A2的正确值。

现在，如果清除立即窗口中的数据，并再次计算公式而不改变任何内容（使用Ctrl Alt F9），此时单元格A3仅重新计算一次，因为Excel正在重复使用先前重新计算的最终计算序列。

处理未计算的单元格

幸运的是，自定义函数很容易检测到它被传递了一个未计算的单元格，因为该单元格将为空：

代码语言：javascript复制

Function Tracer2(theCell As Range)
   If IsEmpty(theCell) Then Exit Function
   Tracer2 = theCell.Value
   Debug.Print Application.Caller.Address & "-" & Tracer2
End Function

这个版本的用户自定义函数检查单元格是否为空，如果为空则立即退出。如果需要区分真正的空单元格和未计算的单元格，那么可以检查单元格是否包含正使用的公式：

=IsEmpty(theCell.Value) and Len(theCell.formula)>0Then Exit Function

如果参数是包含公式的单元格区域，那么更复杂一些：

代码语言：javascript复制

Function IsCalced(theParameter AsVariant) As Boolean
    '如果theParameter引用仍未计算的单元格,则返回False
   Dim vHasFormula As Variant
   IsCalced = True
   On Error GoTo Fail
   If TypeOf theParameter Is Excel.Range Then
        vHasFormula = theParameter.HasFormula
        'HasFormula可以是True,False或Null
        '如果单元格区域包含公式和数据则返回Null
        If IsNull(vHasFormula) Then vHasFormula= True
        If vHasFormula Then
            '如果任意单元格仍未计算则CountA返回0
            IfApplication.WorksheetFunction.CountA(theParameter) = 0 Then
                IsCalced = False
            End If
        End If
   ElseIf VarType(theParameter) = vbEmpty Then
        '如果计算参数引用未计算的单元格则该参数为空
        IsCalced = False
   End If
   Exit Function
Fail:
   IsCalced = False
End Function

这个函数处理单元格引用和计算单元格区域（数组公式表达式等），并检查参数中的所有单元格是否包含公式和未计算任何单元格。

仅变体和单元格对象参数不可计算

仅声明为Range或Variant的用户定义函数参数不可计算。例如，如果所有参数都被定义为Double，那么Excel将在参数传递给用户定义函数之前尝试将参数强制转换为Double，如果参数实际引用了未计算的单元格，则不会调用用户定义函数。

由函数向导引起的多个用户定义函数重新计算

每当将函数向导与用户定义函数一起使用时，用户定义函数会被多次调用，因为在输入函数的参数时，函数向导使用评估动态显示函数的结果。如果用户定义函数执行起来很慢，那就不好了！

可以通过检查标准命令栏是否已启用来检测函数向导是否已调用用户定义函数。

If NotApplication.CommandBars("Standard").Controls(1).Enabled Then ExitFunction

具有多个单元格数组公式用户自定义函数的多个用户自定义函数重新计算

使用将结果返回到多个单元格的数组用户定义函数是加速用户定义函数执行的一种非常好的方法（请参阅前面的文章），但是应该注意一个导致速度减慢的Bug：

当输入或修改多单元格用户定义函数并且取决于易失性公式时：用户定义函数对其占用的每个单元计算一次。只有在输入或更改用户定义函数时，才会重新计算该函数。

条件格式公式中的用户定义函数

每次包含条件格式的屏幕部分被重新绘制或重新计算时，都会评估条件格式规则中的公式（可以通过在条件格式设置规则中使用的用户定义函数中使用Debug.Print语句来证明这一点），因此，总的来说，在条件格式中使用用户定义函数可能不是一个好主意。