Testing VBA Code - Part 3

In Part 2, we introduced the idea of writing tests in code, and applied this to a trivial worksheet function. However, more interesting, and more error-prone, is code that alters our worksheets. For example, we might have a procedure that adds a new record to a list (possibly called from the OK button of a data-entry form). Let’s say that the worksheet, called “Records”, looks like this:

Id is an ‘autonumber’ field; Name is entered by the user; Date is today’s date. To keep things simple, we’ll get Name from the user using an InputBox, rather than a proper form.

Sub NewRecord()
    Dim name As String
    name = InputBox("Please enter a name")
    Records.AddRecord (name)
End Sub

Now, it seems to me that the place to put AddRecord is the Records worksheet – that is, the worksheet module (renamed from the default “Sheet1″ to “Records”). Here it is:

Public Sub AddRecord(name As String)
        'pre Len(name) > 0
        'post: RowCount = old RowCount + 1

    Dim id As Integer, rownum As Integer

    id = NewId
    rownum = NewRow

    Range("A" & rownum).Value = id
    Range("B" & rownum).Value = name
    Range("C" & rownum).Value = Date

End Sub

Note the pre- and post-condition comments, discussed in an earlier posting. More on these later.

NewId and NewRow are trivial functions, based on the size of the data region: RowCount.

Public Function Records() As Range
        'number of filled rows, including header
    Set Records = Range("A1").CurrentRegion
End Function

Public Function RowCount() As Integer
    RowCount = Records.Rows.Count
End Function

Public Function MaxId() As Integer
    MaxId = RowCount - 1     'allow for headers
End Function

Public Function NewId() As Integer
    NewId = MaxId + 1
End Function

Public Function NewRow() As Integer
    NewRow = NewId + 1     'allow for headers
End Function

So, having put the interesting code in the worksheet module, this is what we want to test. In other words, we want to test the worksheet as object + code.

More specifically, we want to test:

• the object’s invariant
• the operation post-conditions

(we’ll come back to the pre-conditions later).

We can define the invariant as a Boolean function. In this case, the worksheet always has at least 1 row (the headers), and the Name fields cannot be empty:

Public Function Invariant() As Boolean
    Invariant = _
        RowCount >= 1 And _
        WorksheetFunction.CountA(Range("B:B")) = RowCount
End Function

We should probably check columns A and C, as well. This invariant holds whenever the worksheet is in a ‘stable state’. For example, it’s allowed not to hold when we’re part way through adding a record, and we’ve added the Id (increasing rowcount), but have not yet added the Name (which is therefore blank).

Now we come to the issue of what is in the worksheet when we run the tests. In this case, it’s simplest to start with an empty worksheet (i.e. containing only the header row), and then add records. So at the start of our Run procedure, we need to call a Clear procedure:

Public Sub Clear()
        'post: rowcount = 1
    Cells.Clear
    Range("A1").Value = "Id"
    Range("B1").Value = "Name"
    Range("C1").Value = "Date"
End Sub

This has it’s own post-condition, which itself needs testing.

Obviously, we don’t want to run these tests on a workbook containing the only copy of our live data! A warning MsgBox might be appropriate as a safety measure.

Finally, in a module TestRecords, we have the Run procedure. This is mixture of operations, invariant checks and post-condition checks:

Sub Run()
    TestSetup "WS Records"

    Dim rc As Integer   '(old) rowcount

    Records.Clear
    AssertEqual Records.Invariant, True
    rc = Records.RowCount
    AssertEqual rc, 1       'post Clear

    Records.AddRecord ("Abby")
    AssertEqual Records.Invariant, True
    AssertEqual Records.RowCount, rc + 1    'post AddRecord
    rc = Records.RowCount

    Records.AddRecord ("Billy")
    AssertEqual Records.Invariant, True
    AssertEqual Records.RowCount, rc + 1    'post AddRecord
    rc = Records.RowCount

    TestWrapup
End Sub

Notice that we’re calling the worksheet’s procedures via the module name “Records”. Interestingly, it still works if you call via the object – Worksheets(“Records”).Clear – but you don’t get the pop-up prompt for the properties and methods.

In some cases, we might want to clear up the worksheet after the tests have run (referred to in some frameworks as “tear down”). However, in this case it’s useful to do a visual check on what’s been added.

Now we should say something about the pre-condition on AddRecord: that Name must not be empty. The origin of this is the worksheet’s invariant: one way or antoher, we need to preserve this. AddRecord could have taken that responsibility, by doing the Length > 0 check, and using a default value. However, if there is no sensible default value, the responsibility has to be ‘pushed out’ to the client code: in this case, the ‘user interface’ procedure NewRecord. So we can’t test the pre-condition just on the worksheet, we have to involve the client code.

The problem with pre-conditions is that testing them is essentially negative: we have to show that they are never false (whereas post-conditions are shown to be true on a case-by-case basis). One approach is to make the pre-conditions executable, and evaluate them during testing or normal use. So the start of AddRecord would look like this:

Public Sub AddRecord(name As String)
    If pre Then AssertPre "Records.AddRecord", _
            (Len(name) > 0)
    ...

‘pre’ is a flag to switch this checking on or off (during development, one could leave it on all the time). We can put it in the general Test module:

Public Const pre As Boolean = True

AssertPre is just a variation on AssertEqual, which takes the procedure name as a tag:

Public Sub AssertPre(proc As String, precond As Boolean)
    Dim msg As String

    If Not precond Then
        msg = "*** Pre " & proc & " failed"
        Debug.Print (msg)
    End If
End Sub

Entering an empty value into the InputBox now causes the precondition to fail. A blank value gets put into the Name field, and the worksheet is thus invalid. Various user interface solutions are of course possible: validation, drop-down lists, etc. Here, we can just re-prompt until we get a non-empty value:

Sub NewRecord()
    Dim name As String

    Do Until Len(name) > 0
        name = InputBox("Please enter a name")
    Loop

    Records.AddRecord (name)
End Sub

NewRecord is now ensuring the pre-condition of AddRecord.

I think that’s enough for now. Next, we’ll apply these ideas to user-defined Classes (which is where the ideas came from in the first place).

About these ads