VBA is often said to be an event-driven language: a lot of worksheet automation involves executing code in response to such or such workbook or worksheet event. ActiveX controls such as
MSForms.CommandButton are trivially double-clicked, and code is written in some
But how does it all work, and can we leverage this event-driven paradigm in our own code too? But first, what is this paradigm all about?
In a procedural paradigm, code executes one statement at a time, top to bottom, in sequence: procedure A gets invoked, procedure A calls procedure B, procedure B completes, execution returns to procedure A, procedure A completes, execution ends. That’s a paradigm you’re likely already familiar with.
In an event-driven paradigm, code still executes one statement at a time, in the very same way – except now procedures are being invoked by an external object, and there isn’t always a way to tell at compile-time what the run-time sequence will be. If you’re writing the code-behind for a
UserForm module with
Button2 controls, there is no way to know whether
Button1_Click will run before
Button2_Click, or if either are even going to run at all: what code gets to run, is driven by what events get raised – hence, event-driven.
Event-driven code is asynchronous, meaning you could be in the middle of a loop, and then a
DoEvents statement is encountered, and suddenly you’re not in the loop body anymore, but (as an example) in some worksheet event handler that gets invoked when the selected cell changes. And when that handler completes, execution resumes in the loop body, right where it left off.
This mechanism is extremely useful, especially in an object-oriented project, since only objects (class modules) are allowed to raise and handle events. It is being put to extensive use in the OOP BattleShip project (see GridViewAdapter and WorksheetView classes for examples of event forwarding and how to define an interface that exposes events), which I’m hoping makes a good advanced-level study on the matter.
But let’s start at the beginning.
Host Document & Other Built-In Events
Whether you’re barely a week into your journey to learn VBA, or several years into it, unless all you ever did was record a macro, you’ve been exposed to VBA events.
VBA code lives in the host document, waiting to be executed: any standard module public procedure that can be invoked without parameters can be an entry point to begin code execution – these are listed in the “macros” list, and you can attach them to e.g. some
Shape and have the host application invoke that VBA code when the user clicks it.
But macros aren’t the only possible entry points: the host document often provides “hooks” that you can use to execute VBA code when the host application is performing some specific operation – like opening a new document, saving it, modifying it, etc.: different hosts allow for various degrees of granularity on “hooking” a varying amount of operations. These “hooks” are the events exposed by the host application’s object model, and the procedures that are executed when these events are raised are event handler procedures.
In Excel the host document is represented by a
Workbook module, named
ThisWorkbook; every worksheet in this workbook is represented by a
Worksheet module. These document modules are a special type of class module in that they inherit a base class: the
ThisWorkbook class is a
Workbook class; the
Sheet1 class is a
Worksheet class – and when classes relate to each other with an “is-a” relationship, we’re looking at class inheritance (“has-a” being composition). So document modules are a very special kind of module, first because their instantiation is in the hands of the host application (you can’t do
Set foo = New Worksheet to create a new sheet), and second because like
UserForm modules, they are inheriting the members of a base class – that’s how you can type
Me. in a procedure inside an otherwise empty document module, and get plenty of members to pick from: if you’re in the
Sheet1 module, you have access to
Me.Range, and that
Range property is inherited from the
Worksheet “base class”. Or
Me.Controls in the
UserForm1 module, inherited from the
Wait I thought VBA didn’t support inheritance?
Indeed, VBA user code doesn’t have any mechanism in the language to support this: there’s no
Inherits keyword in VBA. But VBA creates and consumes COM types, and these types can very well be pictured as having an inheritance hierarchy.
Or something like it. Picturing the
ThisWorkbook : Workbook relationship as though there was a hidden
Private WithEvents Workbook As Workbook field in the
ThisWorkbook module, i.e. more like composition than inheritance, wouldn’t be inaccurate either.
Fair enough. So what does this have to do with events?
Take any old
Sheet1 module: because it “inherits” the
Worksheet class, it has access to the events defined in that class. You can easily see what events are available in any class, using the Object Browser (F2) – all events are members represented with a lightning bolt icon:
So when you’re in a
Worksheet module, you can implement event handlers for any events fired by the base
Worksheet class. Because of how events work under the hood, in order for an event handler to “hook” the event it means to handle, it must have the correct signature or at least, a compatible one: the name of the procedure, the number, order, and type of its parameters must match exactly with the signature/definition of the event to be handled… and ensuring that isn’t as complicated as it sounds:
Notice the VBE generates Private procedures: there is no reason whatsoever to ever make an event handler public. Event handler procedures are meant to handle events, i.e. they’re callbacks whose intent is to be invoked by the VBA runtime, not by user code! That’s why I recommend limiting the amount of logic that you put into an event handler procedure, and having the bulk of the work into a separate, dedicated procedure that can be made public if it needs to be invoked from elsewhere. This is especially important for
UserForm modules, which tend to be accessed from outside the form’s code-behind module.
Event handler procedures are always named in a very specific way, just like interface implementations:
Private Sub EventProvider_EventName()
Note the underscore: it matters, on a syntactical level. This is why you should avoid underscores in procedure names, and name all procedures in
PascalCase. Adhering to this naming standard will spare you many headaches later, when you start defining and impementing your own interfaces (spoiler: your project will refuse to compile if you try implementing an interface that has members with an underscore in their name).
Any VBA class module can define its own events, and events may only be defined in a class module (remember: document and userform modules are classes). Defining events is done using the (drumroll)
Public Event BeforeSomething(ByRef Cancel As Boolean) Public Event AfterSomething()
You’ll want the events
Public, so they can be handled in other classes. Now all that’s left to do is to raise these events. That’s done using the
Public Sub DoSomething() Dim cancelling As Boolean RaiseEvent BeforeSomething(cancelling) If Not cancelling Then 'do stuff... RaiseEvent AfterSomething End If End Sub
Here are a few guidelines (that word is chosen) for sane event design:
- DO define
Afterevent pairs that are raised before and after a given operation. This leaves the handlers the flexibility to execute preparatory/cleanup code around that operation.
- DO provide a
ByRef Cancel As Booleanparameter in
Beforeevents. This lets the handlers determine whether an operation should be cancelled or not.
- CONSIDER using
ByVal Cancel As MSForms.ReturnBooleanif the MSForms type library is referenced. Being a simple object encapsulating the cancel state, it can be passed by value, and the handler code can treat it as a
Booleanif it wants to, because the object’s
Valueis the class’ default member.
- CONSIDER exposing a public
On[EventName]procedure with the same signature as the event, whose purpose is simply to raise said event; events can only be raised by the class they are defined in, so such methods are very useful for making an object raise an event, notably for testing purposes.
- DO use past tense to indicate that an event occurs after a certain operation has completed, when there is no need for an event to occur before. For example:
- DO use future tense to indicate that an event occurs before a certain operation has started, when there is no need for an event to occur after. For example:
- DO NOT use present tense (be it indicative or progressive/continuous), it’s ambiguous and unclear exactly when the event is raised in the operation. For example, a lot of standard library events use this naming scheme, and it’s easy to second-guess whether the event is fired before or after said
Changinghas actually happened.
- DO NOT use
Afterwithout also exposing a corresponding
- DO NOT mistake guidelines for gospel, what really matters is consistency.
The class that defines an event is the provider; a class that handles that event is a client. The client code needs to declare a
WithEvents field – these fields must be early-bound, and the only types available for the
As clause are event providers, i.e. classes that expose at least one event.
Option Explicit Private WithEvents foo As Something Private Sub foo_BeforeDoSomething(ByRef Cancel As Boolean) 'handles Something.DoSomething End Sub
WithEvents field adds a new item in the left-hand code pane dropdown, and that class’ events are displayed in the right-hand code pane dropdown – exactly like any plain old workbook or worksheet event!