The One Function Every VBA Developer Needs in a WithEvents Class

The WithEvents keyword lets us encapsulate event-handling code in a class module for easy re-use.  If you're not familiar with the concept, I recommend you watch the presentation I gave on the topic, Navigating Continuous Forms using WithEvents.

A common scenario for writing WithEvents class modules in Access is to handle form, report, and control events.  For those objects, handling the event is only half the battle.  The event handling code will never be raised if we don't also set the associated event property on the Property Sheet.

But wait, there's a shortcut!  You don't have to remember to set that value on the property sheet.  Instead, we can set that value in code.  In fact, we can set it from within our class module.  This further encapsulates our class's functionality.  More importantly, it's one less step needed for implementing the class.

Side Effects, Side Effects, Side Effects

Hold on, though, cowboy!  Those event properties are a form of global state.  Since they are external to our class module, we need to be respectful of the side effect that we are creating.

If the value of the event property is blank, it's safe to change it to "[Event Procedure]."  And, if it's already set to "[Event Procedure]," that's obviously not a problem, either.

If it is anything else, we need to be more careful.

What else could it be?  It could be a public function call (like myEnterCombo() function, for example).

It could also be a macro (*shudder*...please don't use macros).  

The bottom line is that if something else is already there, it is poor etiquette for our class to clobber it by forcing an "[Event Procedure]" value.  And by "poor etiquette," I mean that it will lead to subtle bugs.

First, Do No Harm

To take a page out of the Hippocratic Oath, we want to be careful not to cause more harm than good with our code.  So, instead of overwriting existing values with the "[Event Procedure]" string, we should simply warn the developer that there is something preventing us from properly handling the event via our class module.

There are always exceptions

Occasionally, we write a class module that replaces the functionality of an existing public function.  For example, suppose I wrote a class module to perform the same functions as the EnterCombo routine.  In such a situation, it would make sense to also allow replacing the call to =EnterCombo() with [Event Procedure].

The Code: SetEventProc()

The SetEventProc code is straightforward.  It sets the event property to "[Event Procedure]," but only if the event property is:

• blank; OR
• already set to [Event Procedure]; OR
• set to text that we decide is acceptable to overwrite

You can replace the Throw function below with a call to Debug.Print if you want.  It's only there to alert you as a developer that there is a problem that you need to deal with.

' Developer convenience function to prevent accidentally obliterating custom form/control properties.
' Usage:    Ctl.AfterUpdate = SetEventProc(Ctl.AfterUpdate)
Private Function SetEventProc(EventProp As String, Optional OverRidableText As String) As String
    If Len(EventProp) = 0 Or _
       EventProp = "[Event Procedure]" Or _
       EventProp Like OverRidableText Then
       
        SetEventProc = "[Event Procedure]"
    Else
        Throw EventProp & " must be changed to '[Event Procedure]'"
    End If
End Function

Usage

I like to include the SetEventProc function at the bottom of all of my WithEvents code modules. Then, when I'm initializing the form, report, or control object in the class module, I immediately call SetEventProc.  This ensures that every event I'm handling gets set to "[Event Procedure]" so that my event handling code will run.

For example:

Public Sub Initialize(ComboBoxControl As Access.ComboBox)
    Set Ctl = ComboBoxControl
    Ctl.OnEnter = SetEventProc(Ctl.OnEnter)
    Ctl.AfterUpdate = SetEventProc(Ctl.AfterUpdate)
    Ctl.OnChange = SetEventProc(Ctl.OnChange)
    Ctl.OnKeyUp = SetEventProc(Ctl.OnKeyUp, "=EnterCombo(*)")
End Sub

Image by Arek Socha from Pixabay