Posts Tagged 'Testing'

Testing VBA Code – Part 4

In Part 3, we looked at testing a worksheet as an Excel object plus associated code (in the sheet module). This idea is similar to that in object-oriented testing practice, where the unit of testing is a single object (instance of a class), or a group of closely related objects.

In a previous set of postings on Classes, we defined a couple of Class modules, Job and Resource, instances of which acted as adapters on to a worksheet (rather than having operational code in the sheet module itself). This example also used a List class as a ‘helper’ – basically a linked data structure with Add and Remove operations. So, how do we test such Classes?

For simplicity, let’s stick with the List class (since there’s no persistence to the worksheet, as there was with Job and Resource classes).

The test ‘script’ basically consists of:

  • create an instance of the class
  • perform operations on the object
  • check the class invariant after creation and after each operation
  • check the post-condition of each operation
  • use the ‘state query’ functions or properties (e.g. First, Count) to inspect the object.

For collection-like objects, there are often sequences of operations that can be checked for ‘circularity’ – e.g. li.Add(val).Last = val.

Here’s the ‘short form’ for the class – that is, just the member headers plus assertions:

'Warning: not complete VBA syntax!

Dim liHead As ListItem
Dim liTail As ListItem
Dim licount As Integer
Const separator As String = "/"

Public Function Invariant() As Boolean
    Invariant = _
        (licount = 0 And liHead Is Nothing And _
			liTail Is Nothing) Or _
        (licount = 1 And Not liHead Is Nothing And _
			liHead Is liTail) Or _
        (licount > 1 And Not liHead Is Nothing And _
            Not liTail Is Nothing And Not liHead Is liTail)
End Function

Public Property Get Count() As Integer

Function IsEmpty() As Boolean

Function First() As Variant
    If pre Then AssertPre "List.First", (Not IsEmpty)

Function Last() As Variant
    If pre Then AssertPre "List.Last", (Not IsEmpty)

Sub SetLast(val)
    If pre Then AssertPre "List.SetLast", (Not IsEmpty)
    'post: GetNth(Count) = val

Private Function NthItem(n As Integer) As ListItem

Function GetNth(n As Integer) As Variant
    If pre Then AssertPre "List.GetNth", _
		     (Count > 0 And n > 0 And n <= Count) 

Sub SetNth(n As Integer, val)
    If pre Then AssertPre "List.SetNth", _
		     (Count > 0 And n > 0 And n <= Count)
    'post: GetNth(n) = val 

Sub Add(val)
       'post: count = old count + 1 

Sub Remove()
'post: (old count > 0 and count = old count - 1) Or
'        (old count = 0 and count = 0)

Function ToString() As String
'post: Len(ToString) >= count * Len(separator)


  • ListItem is just a Value + NextItem pair.
  • The Invariant is given as a function.
  • The post-conditions are just comments: they will be evaluated in the test code. ‘old’ refers to the value of an expression before execution of the operation.
  • In line with the suggestion in Part 3, the preconditions are given as executable code (‘pre’ is a global switch for evaluation).

Following our general approach, the test code goes in an ordinary module ‘TestList’, corresponding to the Class module. The test module contains a single Run procedure (obviously you could break this down into sub-tests). TestSetup and TestWrapup are calls to general facilities in a ‘Test’ module.

The code is verbose of necessity, but pretty simple and repetitive:

Public Sub Run()
    TestSetup "List"

    Dim li As List    'the object under test
    Dim oldcount As Integer
    Dim val As Integer, val2 As Integer   'some values

    Set li = New List

    AssertInv li
    AssertEqual li.Count, 0
    oldcount = li.Count
    val = 3
    li.Add val

    AssertInv li
    AssertEqual li.Count, oldcount + 1      'post Add
    AssertEqual li.First, val
    AssertEqual li.Last, val
    AssertEqual li.GetNth(li.Count), val
    oldcount = li.Count
    val = 4
    li.SetNth 1, val

    AssertInv li
    AssertEqual li.GetNth(1), val           'post SetNth
    AssertEqual li.ToString, "/" & val
    val = 5
    li.SetLast val

    AssertInv li
    AssertEqual li.GetNth(li.Count), val    'post SetLast
    val2 = 6
    li.Add (val2)

    AssertInv li
    AssertEqual li.Count, oldcount + 1      'post Add
    AssertEqual li.First, val
    AssertEqual li.Last, val2
    AssertEqual li.GetNth(1), val
    AssertEqual li.GetNth(li.Count), val2
    AssertEqual li.ToString, "/" & val & "/" & val2
    oldcount = li.Count

    AssertInv li
    AssertEqual li.Count, oldcount - 1     'post Remove
    AssertEqual li.First, val
    AssertEqual li.Last, val
    AssertEqual li.GetNth(1), val
    AssertEqual li.ToString, "/" & val
    oldcount = li.Count

    AssertInv li
    AssertEqual li.Count, oldcount - 1      'post Remove
    AssertEqual li.Count, 0
    AssertEqual li.ToString, ""
    oldcount = li.Count

    AssertInv li
    AssertEqual li.Count, 0
    AssertEqual li.ToString, ""

End Sub

AssertInv is just another general procedure in module Test. If Invariant is a standard name, then we just need to pass in the object:

Public Sub AssertInv(obj As Object)
    Dim msg As String

    If Not obj.Invariant Then
        msg = "*** Inv " & TypeName(obj) & " failed"
        Debug.Print (msg)
        errcount = errcount + 1
    End If
End Sub

Now, we haven’t investigated the question of how much testing is enough testing. There’s no simple answer, of course, but having a standard approach and framework into which to put testing code makes it easy to do testing alongside development, and to do both in small increments. Any change or extension to the List class is matched by changes or extensions to TestList.Run. If we find a bug that’s not been picked up by the tests, then we add some tests so that it is, then we fix the bug. We come back to the project after a year, say, and we can immediately discover whether it’s working or not (bad case of Software Rot…). It really can be most productive.

Finally, a doff of the hat towards the Eiffel language, the only one that fully integrates and supports assertions: preconditions, postconditions and invariants. These are part of the primary code, and can (optionally) be evaluated at run-time. This almost removes the need for separate unit testing code, allowing you to concentrate on system-level or architectural testing. Meanwhile, back with VBA …

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

    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

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

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

Testing VBA Code – Part 2

The general idea, introduced in Part 1, is that we write at least some amount of test code, as a means of exercising our VBA projects, as opposed to (though not excluding) informal, manual use.

Out in the wider world, there is considerable use of test frameworks, such as JUnit. These are OO frameworks that define abstract classes for TestCase, TestSuite, TestResult and so on, which can be specialised for actual tests. Language ‘reflection’ is used to do things like pass method names as arguments to generic ‘run’ methods. For more information, see this JUnit overview.

We don’t need to do as much in VBA (and can’t, indeed). However, we can provide facilities to:

  • Log testing (including any failures) to a file, or maybe a worksheet, or less persistently to the Immediate window. We’ll pick the last of these for simplicity.
  • Provide ‘assertion’ procedures that check actual against expected values, and log any failure.

Incidentally, the failure of a test should be referred to as a ‘failure’, not as an ‘error’ (which means specifically a VBA environment error), or indeed ‘bug’.

The above general facilities can be put in a separate module (say, “Test”), which can be imported or included as an add-in. More on this shortly.

As to overall structure, I think we should have a test module for each primary module (that is, general modules and class modules; not forms, which we’ll ignore for now). So if I have a module Formula (containing public functions for use in Excel formulas), then I’d have a module TestFormula.

TestFormula contains a Run( ) sub:

Public Sub Run()
    TestSetup "Formula"  'the module being tested

    'test other functions in this module...

End Sub

TestSetup and TestWrapup are defined in the general Test module.

The function that we’re testing here checks for valid UK postal codes:

Function ISPOSTCODE(text As String) As Boolean
        'Is text a valid UK postcode?

    ISPOSTCODE = (text Like "[A-Z]# #[A-Z][A-Z]") Or _
            (text Like "[A-Z]## #[A-Z][A-Z]") Or _
            (text Like "[A-Z][A-Z]# #[A-Z][A-Z]") Or _
            (text Like "[A-Z][A-Z]## #[A-Z][A-Z]")
End Function

Now, it’s important that we can run all the tests in our project by a single operation, so that it can be done easily and frequently. So we need a simple TestAll sub:

Sub TestAll()
    'test all other modules in the project...
End Sub

This can go on its own in a TestAll module (just so that it can be found easily).

Since ISPOSTCODE is a ‘pure’ function (that is, with no side effects on the workbook),  the tests are just simple checks of the results:


        'Junk values
    AssertEqual ISPOSTCODE(""), False
    AssertEqual ISPOSTCODE("12345"), False
    AssertEqual ISPOSTCODE("hello"), False

        'Valid values
    AssertEqual ISPOSTCODE("S2 3HS"), True
    AssertEqual ISPOSTCODE("B23 7UJ"), True
    AssertEqual ISPOSTCODE("CB4 6RR"), True
    AssertEqual ISPOSTCODE("PE21 4FG"), True

        'Valid but non-existent
    AssertEqual ISPOSTCODE("XX99 9XX"), True

    AssertEqual ISPOSTCODE("cb4 6rr"), False

        'Whitespace senstive
    AssertEqual ISPOSTCODE("S2  3HS"), False
    AssertEqual ISPOSTCODE("S23HS"), False

        'Central London - not handled yet
    AssertEqual ISPOSTCODE("WC1A 1AA"), False
    ' and so on...
End Sub

AssertEqual is defined in the general Test module (you could give it a shorter name, if you like).

This isn’t the last word in formality, but the test sub does provide quite a good at-a-glance description of what the function does (and doesn’t) do. Better than poring over the # characters.

Finally, here’s what’s in the Test module:

Dim testnum As Integer
Dim failcount As Integer
Dim activemod As String

Public Sub TestSetup(modname As String)
    activemod = modname
    testnum = 0
    failcount = 0
End Sub

Public Sub TestWrapup()
    Debug.Print (activemod & ": " & failcount & " failures")
    Debug.Print "---------"
End Sub

Public Sub AssertEqual(actual, expected)

    Dim msg As String

    testnum = testnum + 1

    If Not (actual = expected) Then
        msg = "Test " & activemod & "." & testnum _
                & " failed: actual " & actual _
                & ", expected " & expected
        Debug.Print (msg)
        failcount = failcount + 1
    End If

End Sub

This is obviously pretty basic, but it’s a start.

In the next posting, we need to consider code that alters worksheets (or other documents). Things get a bit trickier here…

Testing VBA Code – Part 1

Back from the Easter break now…

Looking at using classes got me thinking about testing again. Now, one of the reasons that VBA has a reputation for sloppy coding is that we’re often not sufficiently rigorous about testing. Even if we do exercise the code, this is often done manually in an informal manner. There’s no record of what’s been tested or how. I expect many of us have inherited old code, and had no idea to what extent it worked.

Over in the world of mainstream software development, it has been recognised that testing needs to be automated (or at least very easy to do repeatedly), and closely integrated with the coding activity. For the testing of code units, this means that tests are written as software, which exercises the primary code. In an OO language, this means that we have test classes exercising primary classes. The full current set of tests is run frequently and regularly – say overnight. There are various software frameworks, such as JUnit, for Java, which support this style of testing.

Some people advocate Test-Driven Development, in which test code is written before the primary code, possibly in very fine-grain increments: adding an individual feature or capability. In this way the tests serve as a specification for the primary code, albeit a fairly low-level one.

The ability to re-test frequently makes it much easier and safer to re-factor code: to make structural improvements that do not change behaviour. An example would be sharing some code, rather than duplicating it, at the procedure or module level. The reluctance of most people to do this kind of thing is one reason why VBA code ‘degenerates’ over time.

In general, I think this is all Good Stuff. However, applying it to VBA needs a bit of thought. In particular, our VBA code is in a quite specific context, in terms of the host application (Word, Excel, etc). This means that the software that we’re testing includes, for example, ranges and formulas defined on Excel worksheets.

Now, you might feel that you have plenty to do without writing a load of extra code – and you know it’ll work okay, don’t you? However:

  • The benefits can be considerable: you’ll probably save debugging time further down the line
  • The test code is often quite simple and stereotypical
  • It’s all part of an approach that involves thinking concretely about what your code does (and doesn’t) do.

Obviously, we’re not using VBA to build a flight-control system for the Space Shuttle (or its replacement). So there is a point at which one might say “Okay, X is just too difficult to test, I’ll settle for checking Y”.

In forthcoming posts, we’ll look at testing:

  • functions
  • procedures that interect with the document (e.g. worksheet)
  • classes.

I’ll also say something about the relationship of testing to the ideas of ‘Design by Contract’ (mentioned in an earlier posting about comments): preconditions, postconditions and invariant properties.

November 2019
« Dec