Custom Events – Part 2

In Part 1, I introduced an example where two or more forms present differing views of an underlying ‘Model’ object. In this case, the Model is a cursor-like object that represents a particular data record, and the forms display different columns from the currently cursored row. Okay, it’s a bit simplistic, but suggests how a proper multi-form interface might work.

Now the code. Let’s start with a class (module) for RecordCursor. This has Get properties for Position (i.e. row number), Value (indexed by column number) and column count (fixed). Here’s the initialization:

Private Sub Class_Initialize()
    rc_colcount = Range("A1").CurrentRegion.Columns.Count
    ReDim rc_value(rc_colcount - 1)
    rc_position = 1
End Sub

The Update function refreshes the RecordCursor’s value array:

Private Sub Update()
    Dim i As Integer
    Dim rng As Range

    For i = 1 To rc_colcount
        Set rng = Range("A1").Offset(rc_position - 1, i - 1)
        rc_value(i - 1) = rng.Value
End Sub

Now the event-related parts. The class declares an event:

Public Event CursorMove()

This is raised by the Move operations:

Public Sub MoveNext()
    rc_position = rc_position + 1
    RaiseEvent CursorMove
End Sub

Public Sub MovePrev()
'pre: Position > 1
    rc_position = rc_position - 1
    RaiseEvent CursorMove
End Sub

Note the precondition on MovePrev: this will be ensured by the form disabling the Previous button when the cursor is on record #1. In contrast, there’s nothing to stop the cursor moving past the last record.

Now the forms (there are two identical, but differently named form modules). These have a reference to a RecordCursor, and use it as you would expect, calling (Get) Value and the Move operations. However, the reference is WithEvents:

Private WithEvents frm_record As RecordCursor

This allows the form to handle the CursorMove event:

Private Sub frm_record_CursorMove()
End Sub

Update gets the appropriate value:

    ValueText.Value = _

The point is that both forms handle the CursorMove event: the one that caused it and the passive one.

Finally, each form’s RecordCursor needs to be set on initialization:

Private Sub UserForm_Initialize()
    Set frm_record = current

    Dim i As Integer
    For i = 1 To frm_record.ColCount
        ColumnCombo.AddItem i
    ColumnCombo.ListIndex = 0       '--> Change event
End Sub

current is a public variable in an ordinary module. It would be nicer if it could be in the Worksheet module, but this isn’t allowed. The object is created from a command button on the worksheet, but this could be done on Workbook_open or Worksheet_activate.

Private Sub ResetCommand_Click()
    Set current = New RecordCursor
    MsgBox "Reset Cursor"
End Sub

In addition, there are a couple of command buttons to show the forms, but this could be done in different ways.

So the event mechanism allows synchronisation between our two (or more) forms. We could get more sophisticated. We could have two different forms using two different cursors on two different worksheets, synchronised via a shared ‘entity’ object (holding the current Id or Key that connects records on the two worksheets). There would then be two levels of event-handling: by the cursors and by the forms. I think I might try this …


4 Responses to “Custom Events – Part 2”

  1. 1 Peder Schmedling May 29, 2009 at 6:29 am

    Hi Roy,

    Once again a great post. What often seems to miss in articles about custom events in VBA is good real-world-examples, you came up with one and I’ve given it a bit of thought and I think I’ve come up with another example of how to use custom events in Excel solutions (at least for my own solutions);

    Very often when I’m creating Excel workbooks for analyzing data I have one sheet for test data, one or more sheets for pivot tables and several sheets for data analysis (to run several different analyzes of the test data). Normally I would create buttons/menus to clear data, import/add data, refresh pivot tables and within each analysis worksheet I would create buttons to adapt each analysis to the existing data.

    Now, using custom events, this can be more or less automated (due to the fact that worksheets can be regarded as class definitions/modules that can be customized, one is able to implement custom methods, properties, events and event handlers for them); the test data worksheet could for example have a custom event DataUpdated. The pivot sheet(s) could have a WithEvents reference and event handler(s) for the DataUpdated event and adapt the pivot table(s) to the updated data.

    Furthermore each pivot table worksheet could have a custom event for example PivotTablesUpdated which each of the dependent analysis worksheet could listen to and trigger adaption of each analysis to the updated data..

    The reason I think this setup is suitable for custom events is that each of the analysis worksheets needs to handle, in an individual way, the update of data.

    Off course the whole thing could be turned upside down and each data analysis sheet and pivot table sheet could have a .AdaptToData method which was called when the data in the data worksheet was updated. But this solution would require the programmer to add the .AdaptToData method to the code running in the data worksheet for each new worksheet (analysis or pivot) added to the workbook.. With custom events this wouldn’t be a problem as the programmer could just implement an event handler in the worksheet he/she is adding..

    What do you think? Do you know of any more real world examples of custom events in VBA?
    I hope I managed to explain this in an understandable way, if not let me know 😛

  2. 2 roymacleanvba May 29, 2009 at 8:03 am

    Peter, that’s very interesting. What you suggest is another take on the Model-Views relationship. Here, your test data worksheet is the Model, and the pivot tables, etc, are different Views, which are dependent on it. As you say, the relationships are between worksheets, and represent a kind of ‘work flow’ within the workbook, so data changes propagate to other elements.

    This has some similarity with the dependency network created by using formulas (i.e. dependents/precedents), but is more general, since the dependent objects can respond in different ways.

  3. 3 rc boats January 25, 2014 at 2:18 pm

    I’m impressed, I must say. Sekdom do I encounter a blog that’s both
    educative and entertaining, and let me tell you, you have hit the nail
    on the head. The problem iss something which too few people arre spealing intelligently about.
    I’m vry happy that I came across this during my search for something regarding

  1. 1 Dynamic Filtering (using events) – Part 1 « Roy MacLean’s VBA Blog Trackback on June 8, 2009 at 5:08 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
« Apr   Jun »

%d bloggers like this: