Custom Events – Part 1

I thought I’d take a look at Custom Events in VBA: a little-used (I suspect) corner of the language.

The basic idea is that a Class can declare custom events and raise them from inside its method code. Other objects that hold a ‘WithEvents’ reference to an instance of the event-raising class will be notified of its events, and can handle them appropriately (like the familiar event handlers for Form/Control events).

This is interesting from a programming point of view, because it inverts the normal relationship between caller object and called object. Rather than the caller holding or obtaining a reference to the called object, the called object(s) – the ones with the event handlers – hold a (WithEvents) reference to the caller; the caller – the raiser of the event – doesn’t need to know anything about the recipients, or even whether there are any. That sounded a bit theoretical, but hopefully an example will make it more concrete.

MSDN has an example where a custom object (a DataComboBox) is used to wrap a standard ComboBox control, adding the ability for a user to add items to the list. If this is done, the DataComboBox raises a custom event. ItemAdded. The Form has a WithEvents reference to the DataComboBox, and has a handler for the ItemAdded event (although the example doesn’t say what this might do). The idea of wrapping instances of standard classes is quite interesting, but the example is rather small-scale.

The obvious application seems to be a Model-Views pattern, where different Forms are ‘interested’ in the same underlying Model object, but display different information from it. When the Model is updated, it raises an event; each Form handles the event by refreshing their information from the Model. So it’s Notification + Data  Pull, rather than Data Push from the Model.

The simplest useful-looking scenario I could think of is where we have a typical record list on a worksheet, with many columns:


(Okay, there’s only 4 columns, for simplicity).

What we want is something like the built-in Data Form, but with the ability to select just two or three columns. Rather than try and do this in a single form, we could have separate, simple forms for each column:


(These are meant to be side-by-side).

Underlying these multiple forms we need a Model object that represents the whole of a single row: the forms above are displaying values from Row 1. The Model object has the ability to move to the next row or the previous row (if there is one). This is very like a Cursor on a Recordset in ADODB, so we’ll call it RecordCursor. In this case, our cursor is read-only, but I think a full-blown one with write/delete/seek/etc could be useful.

So, the problem is how to synchronise our forms: I can click Next or Previous in either form, but I want both forms to update with the new row’s data.

In summary:

  • There is a global reference to a RecordCursor object (this is set up manually, but could be done on Workbook_open).
  • The forms hold a Private WithEvents reference to the RecordCursor
  • The RecordCursor’s MoveNext and MovePrev methods raise a CursorMove event.
  • Each form handles the event by getting RecordCursor.Value(n), where n is its column number.

This works rather nicely. I’ll illustrate the code structure in the next post.


    9 Responses to “Custom Events – Part 1”

    1. 1 Peder Schmedling May 28, 2009 at 5:56 am

      Great post, looking forward to the follow-up.

      Regarding the MS example: You say that “although the example doesn’t say what this might do”, the MS example page you point to notes the following sentences about what the ItemAdded event can be used for:

      “The programmer can use the ItemAdded event procedure to run validation code before adding the item to the list. For example, the ItemAdded event procedure in the sample file checks to see whether the item already exists in the list. If it does, the event is cancelled and the item is not added.”

      Isn’t this clear enough or did I misinterpret your statement?

    2. 2 roymacleanvba May 28, 2009 at 7:51 am

      Ah, yes, you’re right. Although the point of the event handling is that it has to be something that only the Form can do, not the DataComboBox itself. Simple validation or conversion could be done by the DataComboBox.AddDataItem function. DataComboBox could even have references to other Controls, if their values were of interest.

    3. 3 Bruce June 8, 2012 at 8:50 pm


      Just came across this post after I read the MS article and typed it in Excel.

      Did you get the example to work?

      In the UserForm_Initialize() subroutine I get a “Method or data member not found” error message at “Set mdcbCombo.ComboBox = Me.cboData”

      The combo box is named cboData and I have “Private WithEvents mdcbCombo as DataComboBox” at the top of the code module for the form.

      – Bruce

    4. 4 main page June 1, 2013 at 11:34 am

      Good day! This is my first comment here so
      I just wanted to give a quick shout out and say I truly enjoy reading through your blog posts.
      Can you suggest any other blogs/websites/forums that deal with the same
      topics? Thanks!

    5. 5 Tamika October 12, 2013 at 8:33 pm

      Good day! This post could not be written any better!
      Reading this post reminds me of my old room mate! He always kept chatting about
      this. I will forward this post to him. Pretty sure he will
      have a good read. Many thanks for sharing!

    1. 1 Custom Events – Part 2 « Roy MacLean’s VBA Blog Trackback on May 28, 2009 at 3:05 pm
    2. 2 Application Events « Roy MacLean’s VBA Blog Trackback on June 1, 2009 at 4:47 pm
    3. 3 Dynamic Filtering (using events) – Part 1 « Roy MacLean’s VBA Blog Trackback on June 8, 2009 at 5:08 pm
    4. 4 Dynamic Filtering (using events) – Part 2 « Roy MacLean’s VBA Blog Trackback on June 9, 2009 at 12:22 pm

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    May 2009
    M T W T F S S
    « Apr   Jun »

    %d bloggers like this: