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
Update
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
Next
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
Update
RaiseEvent CursorMove
End Sub
Public Sub MovePrev()
'pre: Position > 1
rc_position = rc_position - 1
Update
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()
Update
End Sub
Update gets the appropriate value:
ValueText.Value = _
frm_record.Value(CInt(ColumnCombo.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
Next
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 …
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
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.