VBA Events Sources and Handlers

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

What are events?

VBA is event-driven: VBA code runs in response to events raised by the host application or the host document - understanding events is fundamental to understanding VBA.

APIs often expose objects that raise a number of events in response to various states. For example an Excel.Application object raises an event whenever a new workbook is created, opened, activated, or closed. Or whenever a worksheet gets calculated. Or just before a file is saved. Or immediately after. A button on a form raises a Click event when the user clicks it, the user form itself raises an event just after it's activated, and another just before it's closed.

From an API perspective, events are extension points: the client code can chose to implement code that handles these events, and execute custom code whenever these events are fired: that's how you can execute your custom code automatically every time the selection changes on any worksheet - by handling the event that gets fired when the selection changes on any worksheet.

An object that exposes events is an event source. A method that handles an event is a handler.


Handlers

VBA document modules (e.g. ThisDocument, ThisWorkbook, Sheet1, etc.) and UserForm modules are class modules that implement special interfaces that expose a number of events. You can browse these interfaces in the left-side dropdown at the top of the code pane:

ThisWorkbook module implements Workbook events

The right-side dropdown lists the members of the interface selected in the left-side dropdown:

Worksheet modules can handle Worksheet events

The VBE automatically generates an event handler stub when an item is selected on the right-side list, or navigates there if the handler exists.

You can define a module-scoped WithEvents variable in any module:

Private WithEvents Foo As Workbook
Private WithEvents Bar As Worksheet

Each WithEvents declaration becomes available to select from the left-side dropdown. When an event is selected in the right-side dropdown, the VBE generates an event handler stub named after the WithEvents object and the name of the event, joined with an underscore:

Private WithEvents Foo As Workbook
Private WithEvents Bar As Worksheet

Private Sub Foo_Open()

End Sub

Private Sub Bar_SelectionChange(ByVal Target As Range)

End Sub

Only types that expose at least one event can be used with WithEvents, and WithEvents declarations cannot be assigned a reference on-the-spot with the New keyword. This code is illegal:

Private WithEvents Foo As New Workbook 'illegal

The object reference must be Set explicitly; in a class module, a good place to do that is often in the Class_Initialize handler, because then the class handles that object's events for as long as its instance exists.


Sources

Any class module (or document module, or user form) can be an event source. Use the Event keyword to define the signature for the event, in the declarations section of the module:

Public Event SomethingHappened(ByVal something As String)

The signature of the event determines how the event is raised, and what the event handlers will look like.

Events can only be raised within the class they're defined in - client code can only handle them. Events are raised with the RaiseEvent keyword; the event's arguments are provided at that point:

Public Sub DoSomething()
    RaiseEvent SomethingHappened("hello")
End Sub

Without code that handles the SomethingHappened event, running the DoSomething procedure will still raise the event, but nothing will happen. Assuming the event source is the above code in a class named Something, this code in ThisWorkbook would show a message box saying "hello" whenever test.DoSomething gets called:

Private WithEvents test As Something

Private Sub Workbook_Open()
    Set test = New Something
    test.DoSomething
End Sub

Private Sub test_SomethingHappened(ByVal bar As String)
'this procedure runs whenever 'test' raises the 'SomethingHappened' event
    MsgBox bar
End Sub


Got any VBA Question?