Excel VBA解读（135）： 影响工作表公式中运用自定义函数效率的Bug及解决方法

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

excelperfect

在前面的两篇文章中，我们通过简单地修改VBA代码来使自定义函数运行得更快。本文将聚焦于Excel中会影响到自定义函数的Bug，并探讨如何避免它们。

在VBE中存在一个小的Bug：Excel每次在工作表计算过程中运行包含自定义函数的公式时，包含自定义函数的公式都会将VBE标题栏改更为“正在运行”，如下图1所示。

图1

在执行完自定义函数后又将标题栏切换回正常状态，如图2所示。

图2

此时，Excel会在VBE标题栏名称显示的工作簿名后显示[运行中]，这就需要VBE向Windows屏幕处理程序发送消息，并刷新包含VBE的窗口。

如果处理的数据量足够大，且计算机配置不适合这么大的数据的话，会看到VBE窗口不断闪烁。对于少量的数据处理来说，这个影响并不大，但是要处理大量数据的话，处理速度就会变慢。

如何解决这个问题呢？

如果Excel处于手动计算模式，可以捕获触发计算的所有键击，并在VBA代码中启动计算。下面是需要计算的过程和键击：

Shift/F9键——Activesheet.Calculate F9键——Application.Calculate Ctrl/Alt/F9——Application.CalculateFull Ctrl/Alt/Shift/F9——Application.CalculateFullRebuild

在ThisWorkbook模块添加键击捕获过程：

Private Sub Workbook_Open()
   Application.OnKey "+{F9}", "SheetCalc"
   Application.OnKey "{F9}", "ReCalc"
   Application.OnKey "^%{F9}", "FullCalc"
   Application.OnKey "+^%{F9}", "FullDependCalc"
End Sub

在标准模块中添加相应的计算子过程：

Sub SheetCalc()
   ActiveSheet.Calculate
End Sub
Sub ReCalc()
   Application.Calculate
End Sub
Sub FullCalc()
   Application.CalculateFull
End Sub
Sub FullDependCalc()
   Application.CalculateFullRebuild
End Sub

如果是自动计算模式，比较有效的方法是在ThisWorkbook模块中添加代码：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Application.Calculation = xlCalculationAutomatic Then
        Application.Calculation = xlCalculationManual
        Exit Sub
   End If
   Calculate
End Sub

然而，它不会停止首次的自动计算，但会捕获后续的计算。

小结：如果需要在Excel中使用大量引用了VBA自定义函数的公式，则需要使用“手动计算”模式，并在工作簿中添加计算键捕获和处理程序。