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.
VBA document modules (e.g.
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:
The right-side dropdown lists the members of the interface selected in the left-side dropdown:
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
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 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.
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