如何确保在VBA过程中完成Excel计算 [英] How to ensure Excel calculation is completed from within a VBA procedure

问题描述

在excel电子表格中，用户定义的函数用于将基本结果计算为电子表格矩阵(复合元素的横截面值).

In an excel-spreadsheet user-defined functions are used to calculate basic results as spread-sheet matrices (cross-section values of composite elements).

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

这些结果一方面用于电子表格中.另一方面，基于这些电子表格结果中的某些值，VBA程序用于执行相对复杂且耗时的计算(结构模型的静态分析).此过程由一个按钮触发.

These results are used within the spreadsheet on the one hand. Based on some values from these spreadsheet-results, on the other hand, a VBA-procedure is used to perform a relatively complex and time consuming calculation (static analysis of a structural model). This procedure is triggered by a button.

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

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

Now, my problems is, that the spread-sheet calculation is out of date, when the Sub Recalculate is triggered. I found that in Excel 2016, the spread-sheet calculation is split into multliple threads. And experienced that user interaction sometimes is faster than spreadsheet calculation.

因此，我得到了折旧后的值，以便在VBA程序中进行进一步处理. 我的问题是:如何保证电子表格范围内的值更新?

Therefore, I get depreciated values for further processing in the VBA-procedure. My question is: How to guarantee for updated values from spread-sheet ranges?

推荐答案

如果答案中所述的解决方案对您有用，那就太好了.我只是想知道您是否知道应用程序的AfterCalculate事件(

If the solution as explained in your answer is working for you then great. I'm just wondering if you're aware of the application's AfterCalculate event (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-aftercalculate-event-excel):

每当计算完成并且没有 悬而未决的查询.必须同时满足两个条件 在事件发生之前.即使没有，也可以引发该事件 工作簿中的工作表数据，例如每当计算完成时 整个工作簿，并且没有查询在运行.

This event occurs whenever calculation is completed and there are no outstanding queries. It is mandatory for both conditions to be met before the event occurs. The event can be raised even when there is no sheet data in the workbook, such as whenever calculation finishes for the entire workbook and there are no queries running.

外接程序开发人员使用AfterCalculate事件来了解何时所有 工作簿中的数据已通过任何查询和/或完全更新 计算可能正在进行中.

Add-in developers use the AfterCalculate event to know when all the data in the workbook has been fully updated by any queries and/or calculations that may have been in progress.

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

This event occurs after all Worksheet . Calculate , Chart . Calculate , AfterRefresh , and SheetChange events. It is the last event to occur after all refresh processing and all calc processing have completed, and it occurs after Application . CalculationState is set to xlDone .

对您来说，这可能是一个更容易的实现.访问应用程序对象的事件的技巧是在类模块中将其声明为WithEvents.对于示例，我已将类称为 clsAppEvents :

It might be an easier implementation for you. The trick to access the application object's events is to declare it WithEvents in a class module. For the example, I've called the class 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

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

In your module, you'd simply have the calling and event handling code:

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

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

FYI, the debug results were as follows:

在以下时间单击按钮:25/10/2017 4:49:20 p.m.

Button clicked at: 25/10/2017 4:49:20 p.m.

计算截止时间为:25/10/2017 4:49:22 p.m.

Calc ended at: 25/10/2017 4:49:22 p.m.

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

Sub called at: 25/10/2017 4:49:22 p.m.

这篇关于如何确保在VBA过程中完成Excel计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！