Creating A Custom Event With VBA

Question

I'm trying struggling to understand how to create a custom event using class modules in VBA.

I've put together the simple following example. You put a value in A1 and B1 and then re activate the sheet to calculate the sum of the two and then I hoped an event would fire to warn of calculation, but nothing happens.

I'd be very grateful for any help solving this example.

Class module cCalc:

Dim m_wks As Worksheet
Public Event BeforeCalc()

Property Set Worksheet(wks As Worksheet)
    Set m_wks = wks
End Property

Public Sub Calc()
Dim dVal1 As Double
Dim dVal2 As Double

    With m_wks
        dVal1 = .Range("A1").Value
        dVal2 = .Range("B1").Value

        RaiseEvent BeforeCalc
        .Range("C1").Value = dVal1 + dVal2
    End With


End Sub

In a module mGlobal:

Public gCalc As cCalc

In the code behind Sheet1:

Private WithEvents calcEvent As cCalc

Private Sub calcEvent_BeforeCalc()
    MsgBox "About to Calc!", vbInformation
End Sub

Private Sub Worksheet_Activate()
    Set gCalc = New cCalc

    Set gCalc.Worksheet = ActiveSheet
    gCalc.Calc

End Sub

Answer 1

You can't declare event-driven classes in modules. You'll need to set the cCalc reference in gModule equal to the object you declared WithEvents in Sheet1. Change your code in Sheet1 to what i wrote below and it will work:

Private WithEvents calcEvent As cCalc

Private Sub calcEvent_BeforeCalc()
    MsgBox "About to Calc!", vbInformation
End Sub

Private Sub Worksheet_Activate()
    Set calcEvent = New cCalc          'Instantiate the WithEvents object above
    Set mGlobal.gCalc = calcEvent      'Set the object declared in gModule

    Set mGlobal.gCalc.Worksheet = ActiveSheet
    mGlobal.gCalc.Calc
End Sub

Note that this is using the variable you put in gModule... The event that is actually called is still calcEvent_BeforeCalc(), which is good as this way you can have n number of objects defined in gModule that would all fire off the same event code when the event is triggered.

To simplify the code, you could always just write:

Private Sub Worksheet_Activate()
    Set calcEvent = New cCalc
    Set calcEvent.Worksheet = ActiveSheet
    calcEvent.Calc
End Sub