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

在 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 event (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-aftercalculate-event-excel):

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

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

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

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

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

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

按钮点击时间：2017 年 10 月 25 日下午 4:49:20

计算结束于：2017 年 10 月 25 日下午 4:49:22

子呼叫时间：2017 年 10 月 25 日下午 4:49:22