How To Create Custom Excel VBA Events

What are custom events and why use them ?

Custom Events Are Messages

Custom events are messages that your program can broadcast to other objects in your application.

They have the advantage of being massively flexible, because other objects can choose to react to or ignore these events depending on how you’ve programmed them.

Custom events help simplify your program

Complex programs are simplified by breaking the tasks that the program has to perform into chunks, this idea is kinda like delegating each piece of work to a separate chunk, process or virtual person (via objects), you can then model your program on paper, say with circles for each process, and decide if one process starts directly after another one finishes, or whether it’s based on a signal (message i.e. event) from another process.

Custom events make it easier to add functionality in the future.

Practically speaking this means that if your program is designed to use events, you can add extra functionality to your system without risking breaking your program, this is because adding events doesn’t involve redesigning your application from scratch, but instead just adding a message line such as for example… “Step 4 Completed” and a new piece of code can be added to react to this.

This feature of making it easier to add functionality is partially because you’re using events and partially because you’re using class modules which are necessary for events to work.

When to use custom events

You want to send messages between parts of the program.

Events are best used when you wish to model real world applications that communicate with one another in some manner.

Applications that model real world situations have the following characteristics.

• They are object oriented.

  This means there are multiple class modules which will represent real world objects that need to communicate with other parts of the program.

• They are Unpredictable in their operation

  Specific code routines will need to run in an unpredictable (dare I say chaotic) manner, this means that each “object” will need to be able to handle itself when an unpredictable event or situation occurs.

• Examples of this would include

  Stock Market Trading systems (every equity (stock) would derive from a “Security” class which would have properties for Open, High, Low, Close, Volume) which would respond to “Price Change” and “events”.

  Computer Games

  • Every moving character and object whether AI or Human Controlled would be represented by a class which results in an object when the code is running.
  • Examples of events in this situation would include Object A shoots at Object B. If object A decides it has hit object B, A could “send a message” to B such as “Hit” with the name of the object receiving the hit (in this case B). All objects would receive the hit event, but only Object B would drop its “Health” by one as the event contained the message that only B actually got hit.
• Multiple objects that interact with one another need to be modeled.

There are two parts to creating applications with your own custom events

• Creating the event
• Listening for and reacting to the event

Creating Custom Events

Step by Step process to create custom events in Excel VBA.

1. Events only work with Class Modules, this means that you will need to have a class module to fire the event and one or multiple class modules to react to the event.

2. In the class module that fires the event you will need to declare a public event (usually at the top of the module).

   This looks similar to a function or subroutine and takes the form.

   05  Public Event eventNameGoesHere('as many arguments as you want here)
   

   An example would be ..

   05  Public Event NewTrade(byval szTickerName as String, byval lVolume as Long, byval dblPrice as double)
   

3. With the event above having been declared at the top of the firing class module, we then need the code to fire the actual event.

   1. To fire the above event you need to use the vba RaiseEvent statement.
   2. This can be added as line of code to whatever subroutine needs to fire the event, this will take the form.

      05  RaiseEvent EventName(arguments)
      

      An example would be ..

      05  RaiseEvent NewTrade("TSLA", 100, 662.56)       ' Stock TSLA (tesla) has traded 100 shares at a price of 662.56
      

   So once the above RaiseEvent line is executed, a VBA project event is fired and any class modules that have been set to listen for this particular event will react to it.

Responding to Custom Events

So we’ve created the code for the events but now we need them to work.

All VBA applications need to be launched from either a Standard Module (AKA Module) or a Sheet Module (i.e. code behind the worksheet sheet).

Within a standard module or code sheet you can either declare your objects as private or public at the module level.

So the subroutine in the standard module will declare the class module to fire the event and any class modules that will listen for the event.

An example of the standard module code can be seen below.

01  Option Explicit
02
03  Private moThrow As clsThrow    ' Declare class that contains RaiseEvent Code
04  Private moCatch1 As clsCatch   ' Declare instance of class that reacts to event
05  Private moCatch2 As clsCatch   ' Declare a second instance of the class that react to the event
06
07  Sub TestHarness()
08    Set moCatch1 = New clsCatch  ' Create an instance of the Catch class
09    moCatch1.Caption = "Bill"     
10    ' Create another instance of the Catch Class, each of these objects are hovever unique
11    Set moCatch2 = New clsCatch   
12    moCatch2.Caption = "Rob"
13    
14    Set moThrow = New clsThrow    ' Create an instance of the Throw Class
15    
16    ' This part is very easy to miss, the throwing class, clsThrow (object being moThrow) must be connected
17    ' to the objects (moCatch1 and moCatch2) that are receiving the event. 
18    moCatch1.ConnectToThrow = moThrow
19    moCatch2.ConnectToThrow = moThrow
20    
21    ' Here we instruct the Throw class to send a messge to fire an event              
22    moThrow.SendMessage "Hey"
23  End Sub

You can see the code behind each of these methods (procedures ) in the class listing below.

First let’s look at the code for clsCatch.

01  ' This is clsCatch
02
03  Option Explicit
04
05  Public WithEvents TheThrow As clsThrow
06  Public mszCaption As String     ' Internal variable to hold the name of the object
07
08  Public Property Let Caption(ByVal szIn As String)
09    mszCaption = "Catch " & szIn        ' Calling program sets the object name
10  End Property
11  
12  Public Property Get Caption() As String
13      Caption = mszCaption    ' Calling program can ask the object it's name
14  End Property
15  
16  Private Sub Class_Initialize()
17      mszCaption = "Catch"    ' Sets the initial "basic" name of the object
18  End Sub
19   
20  ' This property is very important, as it connects this class
21  ' to the event generation class
22  Public Property Let ConnectToThrow(ByRef oIn As clsThrow)
23      ' The variable "TheThrow" is what enables this
24      ' "event sink" to work.
25      Set TheThrow = oIn
26  End Property
27  
28  Private Sub TheThrow_CanYouHearMe(ByVal szMessage As String)
29      ' Print out the caption to the immediate window in the VBA Editor
30      Debug.Print Me.Caption & "-" & szMessage
31  End Sub

So when TestHarness in the regular module executes moThrow.SendMessage “Hey”, the subroutine above “TheThrow_CanYouHearMe” will react to this message, the reason this subroutine reacts will be obvious if you look at the code for clsThrow below.

01  ' This is clsThrow
02  Option Explicit
03  
04  Public Event CanYouHearMe(ByVal szMessage As String)
05  
06  Sub SendMessage(ByVal szMessage As String)
07      RaiseEvent CanYouHearMe(szMessage)
08  End Sub

So in the clsThrow (moThrow object) SendMessge cause the “RaiseEvent CanYouHearMe(szMessage)” line to fire which makes use of the event that we’ve declared at the top of the module.

We can run the above code by clicking in the TestHarness subroutine of the standard module and pressing the F5 key or clicking the Run subroutine toolbar key (play button).

When we do this the following two messages get printed out to the immediate window

Catch Bill-Hey
Catch Rob-Hey

This is because although we only created one “catch” class we derived (“instantiated”) two objects based on that class i.e. “Bill” and “Rob” and each of those responded to the event.

To show how flexible events are lets create a completely new class and have it respond to the same event, thus showing how extra functionality can be added with minor modifications to the code.

So let’s insert a new class module and call it clsGrasshopper.

It’s going to create a Chirp event with a class of clsThrow (being the class that the event comes from).

01  Option Explicit
02  
03  Private WithEvents Chirp As clsThrow    ' Event listener for the "SendMessage" event
04  
05  Public Property Let ConnectToThrow(ByRef oIn As clsThrow)
06      Set Chirp = oIn
07  End Property
08  
09  Private Sub Chirp_CanYouHearMe(ByVal szMessage As String)
10      Debug.Print "Grasshopper " & szMessage & vbCrLf & "Grasshopper says: - " & szMessage & " Yourself, now stop bugging me!"
11  End Sub

Now that we’ve created the “GrassHopper” class (clsGrasshopper), we need to launch a Grasshopper object, let’s call it “Gerry”, so we’ll define a module level object called “moGerry” that points to the grasshopper class, we also need to connect it to to the class that “throws” the event “clsThrow”.

This is done via the “ConnectToThrow” property above.

So just to be clear about what we’re doing here, I’m adding a grasshopper class to the program to demonstrate that when the throw event is fired, now not only will the clsCatch objects react to it but now the grasshopper i.e. “moGerry” will also react to it.

This demonstrates that you can add extra functionality to a program in the future without having to do a “major rewiring” job.

So the new lines that’ll be added to the mMain module are as follows…

' Added at top of module
Private moGerry As clsGrasshopper

     ' Added near bottom of TestHarness Subroutine
     Set moGerry = New clsGrasshopper   ' assign moGerry to grasshopper class
     moGerry.ConnectToThrow = moThrow   ' Pass in moThrow object to Grasshopper object so it can listen for event

The adjusted mMain module now looks like this..

' This is mMain
01  Option Explicit
02
03  Private moThrow As clsThrow
04  Private moCatch1 As clsCatch
05  Private moCatch2 As clsCatch
06  Private moGerry As clsGrasshopper
07  
08  Sub TestHarness()
09    Set moCatch1 = New clsCatch
10    moCatch1.Caption = "Bill"
11  
12    Set moCatch2 = New clsCatch
13    moCatch2.Caption = "Rob"
14      
15    Set moThrow = New clsThrow
16  
17    moCatch1.ConnectToThrow = moThrow
18    moCatch2.ConnectToThrow = moThrow
19  
20    Set moGerry = New clsGrasshopper
21    moGerry.ConnectToThrow = moThrow
22      
23    moThrow.SendMessage "Hey"
24  End Sub

When the above code is run you should get the following message in the Immediate Window of your Integrated Development Environment.

Catch Bill-Hey
Catch Rob-Hey
Grasshopper Hey
Grasshopper says: - Hey Yourself, now stop bugging me!

The above messages can be interpreted as …

• moCatch1 object Bill received the event (message)
• moCatch2 object Rob received the event (message)
• New Grasshopper object moGerry received the "Hey" Message and added it’s own "Stop Bugging me" response.