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