Events with Excel VBA

Up to this point, the objects we created neither received messages from other objects nor sent them, which may suggest that in VBA programs are simply a sequence of procedures executed one after another. This is not the case.

Often, the moment when a procedure is executed in a program is determined by an external factor — an event. For example, clicking a button, selecting a radio option, minimizing a form, etc.

Thus, an event serves as a signal in an application, informing that something important has happened in the system and requires additional attention.

An event is declared within a class using the keyword Event. The declaration must include the event identifier and its list of parameters.

For example, the following event — MoneyWithdrawn — which may be generated when money is withdrawn from an account, has two parameters: Money and Cancel. The first returns the withdrawn amount, while the second determines whether the operation should be canceled:

Public Event MoneyWithdrawn(ByVal Money As Long, ByRef Cancel As Boolean)

Events themselves cannot return data. Data is returned through the event’s parameters.

Declaring an event only indicates that it can be generated. The actual generation is performed by the RaiseEvent statement. For example, the following statement generates the MoneyWithdrawn event when the code reaches it:

RaiseEvent MoneyWithdrawn(Money, Cancel)

When declaring a variable that will reference an instance of the class capable of generating events, you must additionally include the keyword WithEvents.

For example:

Private WithEvents sc As SumListener

To respond to an event, it must be associated with a special procedure called an event handler.

This association is made using the following template:

Sub Object_Event(argumentList)
    ...
End Sub

In the example below, the handler procedure is:

Sub sc_SumDone(ByVal s As Double)
    Debug.Print CStr("Sum done " & s)
End Sub

Example: Class with an Event

Consider a simple example.

The class SumListener has two public fields a and b, and one private field s.

• The method Sum() adds the values of a and b and places the result in s. After the sum is computed, it generates the event SumDone(ByVal s As Double), whose parameter returns the result of the addition.
• The method GetRes() returns the computed sum.

Thus, in the procedure DemoEvent, the result is displayed in the Immediate window first via the SumDone event handler triggered by the Sum() method, and then again explicitly via the GetRes() method.

Class with an event. Class module SumListener

Public a As Double
Public b As Double
Private s As Double

Public Event SumDone(ByVal s As Double)
Public Sub Sum()
    s = a + b
    RaiseEvent SumDone(s)
End Sub

Public Function GetRes() As Double
    GetRes = s
End Function

Class with an event. Worksheet or ThisWorkbook module

Private WithEvents sc As SumListener

Sub DemoEvent()
    Set sc = New SumListener
    sc.a = 1 : sc.b = 3
    sc.Sum
    Debug.Print sc.GetRes()
End Sub

Sub sc_SumDone(ByVal s As Double)
    Debug.Print CStr("Sum done " & s)
End Sub