How to ensure Excel calculation is completed from within a VBA procedure

Question

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

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

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.

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?

Answer 1

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.

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.

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 .

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:

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

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

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

Answer 2

The following solution satisfies my needs:

When a button for recalculation is pressed, vba checks the current Excel calculation state. In case, calculation is done, the VBA-Procedure for calculation Recalculate is started directly. In case, calculation-mode is pending or calculating, then only the local worksheet-variable p_RecalcButtonClicked is set to true. When excel calculation is done, each worksheet fires the Worksheet_Calculate event after it was calculated. And so we can instruct Excel to Recalculate then.

As a safety measure, I kept the solution described in the related two questions from the above comment at the beginning of the sub Recalculate using the function waitForRecalculation. To avoid inactivity, I introduced a timer to tell the user, if calculation could not be finished within a given amount of time.

This is the code of the main worksheet:

' ##### 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