Raising Custom Events in VBA

This quick tutorial will have you writing custom events in VBA in no time.

Raising Custom Events in VBA

In yesterday's article, I used the childhood game of funnel ball to talk about how event-driven programming relates to real life.  As promised at the end of that article, I'll now go through the process of representing those real-life events in VBA.

The Real-life Events of Funnel Ball

For context, here's what a funnel ball pole looks like:

Throw the ball in the top, then it comes out a colored hole numbered 2, 4, 6, or 8

As a quick review from yesterday's article, these are the events we will be modeling:

  • Ball Enters Funnel: this is what we'll call it when the ball enters the funnel
  • Ball Exits Funnel: this is what we'll call it when the ball leaves the funnel

Additionally, when the Ball Exits Funnel, we want to know the Hole Number (2, 4, 6, or 8) of the hole from which it exits.

Announcing: The oFunnelBallPole Class

Whenever I have a class module that represents a real-life object, I like to prefix the class module name with a lower-case "o" (for object).  So, for the class module that represents the funnel ball pole itself, we'll call that oFunnelBallPole.

Here is the code for that class:

'--== oFunnelBallPole class module ==--

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'Custom events must be declared at the top of a class module
Public Event BallEntersFunnel()
Public Event BallExitsFunnel(HoleNumber As Byte)

'This method models the real-life act of throwing a ball
'   into the funnel at the top of a funnel ball pole
Public Sub ThrowBallInFunnel()

    'First, we raise an event to announce that
    '   the ball has entered the funnel
    RaiseEvent BallEntersFunnel
    
    'Then, the ball rattles around for 1 to 5 seconds
    Sleep RandomInt(1, 5) * 1000
    
    Dim HoleNum As Byte
    HoleNum = RandomInt(1, 4) * 2
    
    'Finally, we raise an event to announce that
    '   the ball is exiting the funnel along with
    '   the number of the hole from which it exits
    RaiseEvent BallExitsFunnel(HoleNum)
    
End Sub

'Returns a random long integer between the Min and Max values, inclusive
Private Function RandomInt(Min As Long, Max As Long) As Long
    RandomInt = (Rnd() * (Max - Min)) + Min
End Function

I tried to keep this class as simple as possible, to really highlight the event code.

Public Event lines

At the top of the class module, we declare our custom events using the Event keyword.  The Public keyword is optional.  All events are public whether you use the Public keyword or not; Private Events are not allowed (as they would serve no purpose).

RaiseEvent lines

Once we declare public events at the top of our class module, we can raise those events using the RaiseEvent statement within the functions (Function), methods (Sub), and properties ( Property Get/ Let/ Set) of our class.  The VBA compiler will complain if you try to call RaiseEvent without first declaring the event publicly at the top of the class module.

Think of RaiseEvent as an announcement to the world that something is happening.  

This is where event-driven programming can start to feel confusing.  With procedural programming, we can generally look at the next line of code to know what's going to happen next.  With event-driven programming, that's no longer the case.

When our class calls RaiseEvent, that could initiate code to run in a different part of our program.  Maybe even in multiple parts of the program.  Or maybe nowhere at all.

Remember, RaiseEvent is nothing more than an announcement.  Whether anyone is listening is an entirely separate matter.

When it comes to event-driven programming, if a tree falls in the woods and no one hears it, then the answer is clear: it does not make a sound.

Listening: The clsFunnelBallGame Class

Raising events is only half of the puzzle.  Events serve no purpose unless you listen for them elsewhere.  And the only way to listen to events in VBA is inside a class module.

But how do we actually listen to events in VBA?  Using the WithEvents keyword.  Here's the sample class:

'--== clsFunnelBallGame class module ==-- 

Option Explicit

Public WithEvents Pole As oFunnelBallPole

Private Sub Class_Initialize()
    Set Pole = New oFunnelBallPole
End Sub

Private Sub Pole_BallEntersFunnel()
    Debug.Print "At "; Format(Now, "h:mm:ss"); " ball enters funnel"
End Sub

Private Sub Pole_BallExitsFunnel(HoleNumber As Byte)
    Debug.Print "At "; Format(Now, "h:mm:ss"); " ball exits hole"; HoleNumber
End Sub

In the declaration section at the top of the class module, we use the WithEvents keyword to tell VBA to be ready for us to handle the events raised by the oFunnelBallPole class.

The event handler methods are constructed using "magic identifier" syntax.  What I mean by that, is that there is no keyword–such as Handles or Listens–that declares the private subroutine Pole_BallEntersFunnel() to be a handler for the oFunnelBallPole class's BallEntersFunnel event.  Rather, VBA uses the WithEvents declaration at the top of the class to look for private subroutines of the form Pole_{oFunnelBallPole declared event name}.

Technically speaking, VBA uses the WithEvents variable name (i.e., Pole in this example) with an appended underscore character as an "event handler name prefix" within the class module.

This is why "[t]he name of an event MUST NOT contain any underscore characters (Unicodeu+005F)." (VBA language specification)

Testing

Let's write a quick test procedure to demonstrate how all of this works:

Sub TestFunnelBall()
    Dim Game As clsFunnelBallGame
    Set Game = New clsFunnelBallGame
    
    Dim i As Integer
    For i = 1 To 3
        Debug.Print vbNewLine; "Toss #"; i
        Debug.Print String(30, "-")
        Game.Pole.ThrowBallInFunnel
    Next i
End Sub

Here's what it looks like if we run it in the immediate window:

Multiple Listeners: The clsPhotography Class

I said earlier that each time we call RaiseEvent, we can never be sure how much other code we might be kicking off.  This is another one of the differences between purely procedural code and event-driven code.  With event-driven code, we can have multiple listeners handling the same event.

For example, let's imagine that we have a photographer taking pictures of the funnel ball game.  As soon as that ball exits the funnel, he wants to snap a photo of the exact hole whence the ball emerges.  Let's write such a class:

'--== clsPhotography class module ==--

Option Explicit

Private WithEvents GamePole As oFunnelBallPole

Sub PhotographFunnelBallPole(FunnelBallPole As oFunnelBallPole)
    Set GamePole = FunnelBallPole
End Sub

Private Sub GamePole_BallExitsFunnel(HoleNum As Byte)
    Debug.Print "At "; Format(Now, "h:mm:ss"); " take picture of hole:"; HoleNum
End Sub

Now, let's update our test procedure to add the new listener:

Sub TestFunnelBall()
    Dim Game As clsFunnelBallGame
    Set Game = New clsFunnelBallGame
    
    Dim Photography As New clsPhotography
    Photography.PhotographFunnelBallPole Game.Pole
    
    Dim i As Integer
    For i = 1 To 3
        Debug.Print vbNewLine; "Toss #"; i
        Debug.Print String(30, "-")
        Game.Pole.ThrowBallInFunnel
    Next i
End Sub

Finally, let's test our funnel ball game again, this time with an extra listener.  

If you step through this code as it runs, you will see that the following line in the oFunnelBallPole class gets called only one time per toss...

RaiseEvent BallExitsFunnel(HoleNum)

...but the next two methods called are...

Private Sub Pole_BallExitsFunnel(HoleNumber As Byte)

...in the clsFunnelBallGame class module and...

Private Sub GamePole_BallExitsFunnel(HoleNum As Byte)

...in the clsPhotography class module.

This is a powerful feature.  Use it wisely.

Quick Recap

It takes two to tango when it comes to custom event-driven programming in VBA:

  • an Announcer class that declares a module-level Public Event and then calls RaiseEvent within the class body (methods/functions/properties)
  • a Listener class that handles events via WithEvents

However, you only need an Announcer class if you want to raise your own custom events.  A far more common scenario is to create one or more Listener classes that handle built-in events in Access, like KeyDown, MouseUp, etc.

That sounds like a good topic for another day.

All original code samples by Mike Wolfe are licensed under CC BY 4.0