如何确保在 VBA 程序中完成 Excel 计算

debugcn 发表于 Dev

马布斯博士

在 Excel 电子表格中，用户定义的函数用于计算电子表格矩阵形式的基本结果（复合元素的横截面值）。

Public Function XSValues(inputRange as Range) as variant
    [...]
    ' returns an array and used as matrix-formula (Ctrl-Shift-Enter)
End Function

一方面，这些结果在电子表格中使用。另一方面，基于来自这些电子表格结果的一些值，VBA 过程用于执行相对复杂和耗时的计算（结构模型的静态分析）。此过程由按钮触发。

Public Sub Recalculate()
    [...]
    myValue = range("SomeXLResult").Value
    ' calculation
    ' notification updates
    ' drawing
    ' places different results into cells
End Sub

现在，我的问题是，当Sub Recalculate触发时，电子表格计算已过时。我发现在 Excel 2016 中，电子表格计算被拆分为多个线程。并体验到用户交互有时比电子表格计算更快。

因此，我在 VBA 过程中得到了用于进一步处理的折旧值。我的问题是：如何保证电子表格范围内的更新值？

安比

如果您的答案中解释的解决方案对您有用，那就太好了。我只是想知道您是否知道应用程序的AfterCalculate事件（https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-aftercalculate-event-excel）：

只要计算完成并且没有未完成的查询，就会发生此事件。在事件发生之前必须满足这两个条件。即使工作簿中没有工作表数据，也可以引发该事件，例如当整个工作簿的计算完成并且没有运行查询时。

加载项开发人员使用 AfterCalculate 事件来了解工作簿中的所有数据何时已被任何可能正在进行的查询和/或计算完全更新。

此事件发生在所有 Worksheet 之后。计算，图表。计算、AfterRefresh 和 SheetChange 事件。它是所有刷新处理和所有计算处理完成后发生的最后一个事件，它发生在 Application 之后。CalculationState 设置为 xlDone 。

这对您来说可能更容易实现。访问应用程序对象事件的技巧是WithEvents在类模块中声明它。例如，我将类称为clsAppEvents：

Option Explicit

Private WithEvents mApp As Application

Private Sub Class_Initialize()
    Set mApp = Application
End Sub

Private Sub mApp_AfterCalculate()
    Debug.Print "Calc ended at: " & Now
    ConsumeAfterCalculate
End Sub

在您的模块中，您只需拥有调用和事件处理代码：

Option Explicit

Private mAppEvents As clsAppEvents
Private mEnableConsume As Boolean

Public Sub RunMe()
    Set mAppEvents = New clsAppEvents
End Sub
Public Sub ConsumeAfterCalculate()
    If mEnableConsume Then
        Debug.Print "Sub called at: " & Now
        mEnableConsume = False
    End If
End Sub
Public Sub ConsumeButtonClick()
    Debug.Print "Button clicked at: " & Now
    mEnableConsume = True

    'For demo purposes I'm just forcing a calculation on existing data.
    Sheet1.EnableCalculation = False
    Sheet1.EnableCalculation = True
End Sub

仅供参考，调试结果如下：