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

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.

Roll-over Periodic Workbook – Part 2

In Part 1, I illustrated the idea of a periodic report, that would roll-over Current data to become Previous data in the next report. Now I should say something about the design. Here’s a UML-ish diagram:

The blue boxes represent Class modules, the yellow ones are Excel objects. The dotted arrows show procedure-calls (delegation).

RORange is a class that associates a pair of Ranges: a Previous one (Prev_<name>) and a Current one (Curr_<name>). It’s responsible for:

• defining the Excel names for the Ranges
• rolling-over the ranges between two given workbooks (i.e. copying and pasting values).

RORange also defines the consistency condition that needs to hold between the two Ranges: that they are single-area, same size and non-intersecting. The last of these is checked by using the intersection operator (whitespace):

intersectcount = Range(ror_prevrange.Address & " " & _
                                ror_currrange.Address).Count
    IsValid = IsValid And intersectcount = 0

The IsValid function is something we can use in our TestRORange module (see earlier postings about testing). For example:

Sub Run()
    ...
    Set rng2 = Range("B1:B2,B4")    'multi-area
    Set ror.CurrRange = rng2
    AssertEqual ror.IsValid, False
    ...

Class ROBook acts as an add-on to Workbook. It’s responsible for doing the book-level roll-over:

• creating a new workbook from the template: Workbooks.Add (xltname)
• calling RollOver for each RORange
• incrementing date and count for the new workbook
• saving the new workbook with the ‘next’ filename.

With ROBook it’s hard to come up with a substantial invariant: all we can really check is that its properties are set, and that each RORange is valid. In a similar vein, the only testing I did was to check that the class initialised alright.

The Setup form has a reference to the collection of RORanges, which it manipulates. It really ought to get these, via the ROBook, from the Workbook. Unfortunately, the Workbook module can’t have Public properties, so I had to put a reference to the ROBook into a Constants module.

The Setup form’s AddCommand_Click handler creates a new RORange, sets its properties and calls DefineNames on it, which creates the Excel definitions.

That’s about it … If you want to try it out, the template is here.

Roll-over Periodic Workbook – Part 1

It’s quite common in financial reporting to carry over data from one periodic report to its successor – from one month to the next, say. The ‘Current’ data in one report becomes the ‘Previous’ data in the next one; percentage or absolute change might be calculated. Something like this:

The Previous data is brought forward from the predecessor report, and is not to be changed (grey fill); the Current data is entered in this report (green fill).

I thought it would be useful to have a template that (a) helped to set up the data ranges to roll-over, and (b) performed the roll-over, creating a new workbook and copying the data. There’s a worksheet that acts as a control panel:

The parameters are:

• base name for the workbooks (created as <basename>_yymmdd.xls)
• date
• period (as used by the DateAdd function)
• count (in the sequence of reports).

(“rob” stands for “roll-over book”).

The range set up is done via a form:

This allows you to select pairs of ranges, with a given name, defining names of the form Prev_X and Curr_X. The ranges are checked for non-intersection, single area and same row/column dimensions. You can have as many pairs of ranges as necessary. All other cells are as defined in the template (e.g. with formulas).

Clicking the ‘Roll Over’ button generates a new report from the template, incrementing Date and Count, and copying Curr ranges to Prev ranges. You then edit and save as normal.

I’ll talk about the design tomorrow. There are a couple of Class modules in there – any ideas?

Set Keyword

In terms of VBA syntax, one thing I find annoying is the need to use the Set keyword for assignment of object references. I think I forget it as often as not, particularly when using my own classes. For example:

Dim myrange as Range
Set myrange = Range(“Source_Data”)

Why is this necessary? I’m not aware of other object-based languages that require a similar keyword.

VBA is obviously capable of inspecting the type of the right-hand-side expression, to see whether it is a valid object type (same as the left-hand-side type, or implements it, or nothing). That’s why it raises an error if Set is missing. So why does it need the keyword?

As far as I can tell from the Mediaeval Mystery that is the VBA Reference Manual, there is no valid use of Set with a non-object type on the right-hand-side, so it’s not there to disambiguate any such case from the default (Let) assignment.

If anyone can shed light on this, please comment…

ByRef and ByVal

I recently came across a discussion about ByRef and ByVal, which I found a bit confusing. So I thought I’d summarise my (possibly mis-) understanding here.

ByRef and ByVal are keywords that influence the argument-passing mechanism of a call, which appear before the affected parameter in a procedure header: Function MyFunc (ByVal name As String, …).

If you don’t give either keyword, the default mechanism is ByRef. In effect, the parameter in the called procedure is a synonym for the variable passed as an argument by the calling procedure. Any changes made to the parameter variable in the called procedure are visible to the calling procedure.

The ByVal mechanism is equivalent to the declaration of a separate variable in the called procedure, and assignment of the argument value to that variable. Any changes made to the parameter variable in the called procedure are not visible to the calling procedure.

There’s a page in the VBA Help titled “Passing Arguments Efficiently”. This contains the following example of ByVal passing:

Function Factorial(ByVal MyVar As Integer)
    MyVar = MyVar - 1
    If MyVar = 0 Then
        Factorial = 1
        Exit Function
    End If
    Factorial = Factorial(MyVar) * (MyVar + 1)
End Function

Sub TestFactorial()
    Dim var As Variant
    var = 5
    Debug.Print Factorial(var)
    Debug.Print var
End Sub

The point is that the value of var in the calling procedure is unaffected by the decrementing in Factorial: it remains as 5.

However, to me, this seems a quite bizarre way of writing a factorial function! Why would you want to decrement MyVar, rather than just passing (MyVar – 1) to the recursive call? Also, what’s wrong with if-then-else?

Incidentally, ByVal is necessary here because we’re passing a Variant argument, which needs to be copied (assigned) to effect the type-conversion. You get an error without the ByVal (that is, if it’s ByRef).

One possible use for argument-modification is to pass back a status value to the caller:

Sub TestDodgy()
    Dim error As Boolean
    Dim result As Integer

    Debug.Print error
    result = Dodgy(42, error)
    Debug.Print error
End Sub

Function Dodgy(n As Integer, ByRef err As Boolean) As Integer
    If n > 100 Then
        Dodgy = n
    Else
        err = True
    End If
End Function

(The explicit ByRef is just for clarity; it’s the default). This prints out False, then True, illustrating that error (in the caller) and err (in the called) are the same run-time variable. I gather that this technique is used in function-call APIs, which I guess is okay in that context.

However, my feeling is that, in application code, we’d be better off with the error variable at the module-level. Better still, a Class module could make the error status visible as a property. This would allow callers in other modules to follow the pattern: Obj.AttemptX, If Obj.SucceededX Then …

Let’s look at the two mechanisms with a simple example:

Function ByvalFunc(ByVal str As String) As Boolean
    str = "yyy"     'this change does not propagate
    ByvalFunc = True
End Function

Function ByrefFunc(ByRef str As String) As Boolean
    str = "zzz"     'this change propagates
    ByrefFunc = True
End Function

Function ByrefFunc2(ByRef str As String) As Boolean
    Dim str2 As String
    str2 = str
    str2 = "xxx"     'this change does not propagate
    ByrefFunc2 = True
End Function

Sub Test()
    Dim res As Boolean
    Dim s1 As String, s2 As String, s3 As String, s4 As String

    s1 = "foo"
    s2 = "bar"
    s3 = "baz"
    s4 = s2
    res = ByvalFunc(s1)
    res = ByrefFunc(s2)
    res = ByrefFunc2(s3)
    Debug.Print s1
    Debug.Print s2
    Debug.Print s3
    Debug.Print s4
End Sub

We get: foo, zzz, baz, bar. So the ByRef string s2 is the one that is modified in the called function. Assignment in either the called procedure (s3) or the caller (s4) breaks the link by giving us a new variable.

In the call to ByrefFunc, the caller can force ByVal passing of s2, by using extra parentheses: res = ByrefFunc((s2)). This is rather dodgy syntactically, since in any other context, superfluous parentheses are ignored.

Now comes a twist. Suppose that we have subs, rather than functions:

Sub ByvalSub(ByVal str As String)
    str = "yyy"
End Sub

Sub ByrefSub(ByRef str As String)
    str = "zzz"
End Sub

Sub Test()
    ...
    s1 = "foo"
    s2 = "bar"
    ByvalSub (s1)
    ByrefSub (s2)
    Debug.Print s1
    Debug.Print s2

In neither case does the change in the called sub propagate to the caller. This strikes me as weird. Assuming that you want argument-changeability at all, why would you not want it for subs? Any clarification welcome!

Fortunately, with Objects, things work as you would want. Arguments passed to Property Set or other methods of an object are typically assigned (directly or indirectly) to the private variables of the object. In this respect, they are like str2 in ByRefFunc2, above.

What happens when arguments are objects? Here, there is an extra level of indirection: the value of an object-typed variable is an object-reference, not the object structure itself. So if we pass an object-typed variable ByVal, the called procedure gets a copy of the object-reference; the object does not get copied. This is entirely reasonable, as the object could be large, complex, or a piece of our application. Here’s an example:

Sub TestObj1()
    Dim li As List
    Set li = New List

    li.Add ((42))
    TestObj2 li
    Debug.Print "TestObj1", li.First
    Set li = Nothing

End Sub

Sub TestObj2(ByVal li As List)
    li.Add (66)
    Debug.Print "TestObj2", li.GetNth(2)
    Set li = Nothing
End Sub

This works, printing out 66, then 42. If TestObj2 had a ByRef parameter (explicitly or by default), then the call to li.first in TestObj1 fails with a ‘No Object’ error.

Why might you want to alter an object-typed parameter? Good question… One possibility is Memory Management. If the object (reference) is passed around ByRef, there is effectively a single shared reference. In this case, whichever procedure sets the reference to nothing deletes the object (I guess we’re thinking of something potentially large, like a Recordset). It might not be clear whose responsibility this is. On the other hand, if the object (reference) is passed around ByVal, then each procedure can set its own local reference to nothing, when it’s finished with it, without worrying about the wider usage – as in the example above. Any comments on this would be welcome.

There are some pages in the MSDN library on this: look at and around this one. This is Visual Basic, not VBA, and I noticed that it says: “The default in Visual Basic is to pass arguments by value”. Since the VBA default is ByRef, this looks like a potential gotcha, if you ever tried to migrate code between the two. Curious…