Excel VBA instantiate AfterCalculate event handler when opening workbook

Question

I want to handle Excel's Application.AfterCalculate event.

It appears that this can only be done using a class. How do I define and instantiate such an event handler when the workbook is opened?

Answer 1

Put the following code in the ThisWorkbook object.

Public WithEvents appEventHandler As Application

Private Sub Workbook_Open()
    Set appEventHandler = Application
End Sub

Private Sub appEventHandler_AfterCalculate()
    ' DO WHATEVER YOU WANT After Calculate.  E.g., call a Public Subroutine.
    MsgBox "AfterCalculate called"
End Sub

If the AfterCalculate handler modifies anything in the spreadsheet then, in order to prevent unexpected repeated or recursive callbacks, I wrap it in an If statement to make sure it only runs once, as in the following Module code:

Public RunningAfterCalculate As Boolean

Public Sub RunAfterCalculate()
    If Not RunningAfterCalculate Then
        RunningAfterCalculate = True
        ' DO WHATEVER YOU WANT AfterCalculate
        ' ...
        RunningAfterCalculate = False
    End If
End Sub

Note that if for some reason VBA encounters an unhandled exception you will have to reset the appEventHandler – e.g. by running a sub like this:

Public Sub RestartEventHandler()
    Set ThisWorkbook.appEventHandler = Application
    Application.EnableEvents = True  ' Just in case...
    RunningAfterCalculate = False
End Sub