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.
- 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.