Excel VBA解读（142）： 易失性函数和函数参数

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

excelperfect

本文主要讲述何时及为什么要使函数具有易失性。

什么是易失性？

通常，Excel的智能重新计算引擎仅重新计算已更改/输入的公式，或者取决于已在公式的更高层级引用链某处发生更改的单元格或公式。

这使得计算速度非常有效，因为在典型的工作簿中，只有一小部分公式将依赖于任何特定的单元或数据。

但是有些函数需要在每次重新计算时重新计算。例如，NOW()应该总是在最后一次计算时给出当前时间，RAND()应该在每次计算时给出不同的随机数。这些函数称为易失性函数，任何使用其中一个易失性函数的公式都是易失性公式。

Excel的智能重新计算引擎如何知道何时重新计算函数或公式？

Excel通过查看函数或公式引用的其他单元格来维护其依赖关系树，并且智能重新计算引擎使用这些依赖关系树来确定要重新计算的公式。

对于函数，Excel仅查看函数的参数以确定函数所依赖的内容，因此，如果写了下面这样一个函数：

Function Depends(theCell As Range)
   Depends = ActiveSheet.Range("Z9") + _
            theCell + _
            theCell.Offset(0, 1)
End Function

在公式中调用：

=depends(A1)

那么，Excel只会在A1改变时重新计算该函数，而在B1或Z9改变时不会重新计算。

这将提供不正确的结果。

注意：在重新计算期间，如果Excel确实评估用户自定义函数，那么它会确定在函数内实际使用哪些单元格引用以影响该函数结果，并且如果这些单元格尚未最终计算，那么它将重新安排该函数以供稍后计算。这是使用户自定义函数最终以正确的依赖顺列计算所必需的。

如何修复问题？

有几种方法来修复上述问题。

1.使函数成为易失的

在函数代码中添加Application.Volatile，那么该函数总会重新计算：

Function Depends(theCell As Range)
   Application.Volatile
   Depends = ActiveSheet.Range("Z9") + _
            theCell + _
            theCell.Offset(0, 1)
End Function

但这会减慢计算速度，因此一般来说这并不好，除非像RAND()或NOW()那样函数真的需要成为易失的。

2.使用Ctrl+Alt+F9触发完全计算

如果按Ctrl+Alt+F9组合键，则Excel将重新计算所有打开的工作簿中的每个公式，无论更改了内容还是易失性的。

当然这可能非常慢。

3.确保用户自定义函数的参数引用用户自定义函数使用的所有单元格

将上面的自定义函数修改为：

Function Depends(theCell1 AsRange, theCell2 As Range)
   Depends = theCell1.Resize(1, 1) + _
            theCell1.Resize(1, 1).Offset(0, 1)+ _
            theCell2
End Function

这是最好的解决方案。

使用=Depends(A1:B1,Z9)调用该函数，以便Excel知道通过theCell1.Offset(0,1)引用B1。

现在，Excel知道函数所依赖的所有单元格，并且将正确且高效地重新计算。

小结

确保用户自定义函数的参数总是直接引用该函数使用的所有单元格。