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

EN

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

' ##### Worksheet-Code

'''
' Private Worksheet-Variable to determine, 
' if the button was pressed prior to worksheet calculated-event
'
Private p_RecalcButtonClicked As Boolean


'''
' Procedure to handle Button Clicked 
' (either using a shape with a macro assigned or 
'  an Active-X-Button with this procedure as event handler: best is to use {Button}_MouseUp as {Button}_clicked is fired occasionally by excel itself)
'
Public Sub ButtonClicked()
    '
    ' depending on the calculation state ...
    '
    Select Case Application.CalculationState
        Case xlDone
            '
            ' ... all done, fine ...
            ' ... directly call the calculation procedure sub Recalculate
            '
            p_RecalcButtonClicked = False
            Recalculate
        Case xlPending
            '
            ' ... pending ...
            ' ... set local worksheet variable true in order to call sub Recalculate
            '     later, when the calculated-event was raised
            '
            p_RecalcButtonClicked = True
            '
            ' instruct excel to recalculate
            '
            Application.CalculateFullRebuild
            '
            ' now let excel perform until worksheet calculated event is raised
            '
        Case xlCalculating
            '
            ' ... calculating ...
            ' ... set local worksheet variable true in order to call sub Recalculate
            '     later, when the calculated-event was raised
            '
            p_RecalcButtonClicked = True
            '
            ' let excel continue until worksheet calculated event is raised
            '
        Case Else
    End Select
    
End Sub


'''
' worksheet calculation finished
' this event is raised AFTER calculation was finished
' (shold actually be named Worksheet_Calculated)
'
Private Sub Worksheet_Calculate()
    ' check if the RecalcButton was clicked 
    If p_RecalcButtonClicked Then
        p_RecalcButtonClicked = False
        Recalculate
    End If
End Sub

'''
' Recalculation
'
Public Sub wm_Recalculate()
        '
        ' wait for calculation to be done
        ' just in case...
        '
        If Not waitForRecalculation Then
            MsgBox "Press Ctrl+Alt+F9 for full recalculation", vbCritical + vbOKOnly, "Excel-calculation not done"
            Exit Sub
        End If

        ' [...] Your calculation here...
End Sub

'''
' Helper function to wait and do events until Excel-calculations are done
' returns true if calculation is done within the given time
'
Public Function waitForRecalculation() As Boolean

    Const MAXTIME_S = 10

    Dim t As Double
    t = Timer()


    ' in case of sql-async queries this might be required
    ' 
    ' Application.CalculateUntilAsyncQueriesDone
    
    '
    ' As a safety net,
    ' the second solution is to
    ' do System events until calculation is done
    '
    If Application.CalculationState <> xlDone Then
        Do
            DoEvents
            If Timer() - t > MAXTIME_S Then Exit Do
        Loop Until Application.CalculationState = xlDone
    End If

    '
    ' return true if calculations are done
    '
    waitForRecalculation = (Application.CalculationState = xlDone)

End Function