Creating a custom event with VBA

Question

I'm trying to figure out how to create my own event using class modules in VBA.

I put together a simple example. You put the value in A1 and B1, and then re-activate the sheet to calculate the sum of the two, and then I was hoping the event would light up to warn about the calculation, but nothing happens.

I would be very grateful for the help in solving this example.

CCalc class module:

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 the mGlobal module:

 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 cannot declare event-driven classes in modules. You need to set the cCalc link 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 uses the variable that you put in the gModule ... The event that is actually triggered is still calcEvent_BeforeCalc () , which is good, since in this way you can determine the number of objects in the gModule that will reset one the same event code when the event is triggered.

To simplify the code, you can always simply write:

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