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

    TestISPOSTCODE
    'test other functions in this module...

    TestWrapup
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()
    TestFormula.Run
    '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:

Sub TestISPOSTCODE()

        '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

        'Case-sensitive
    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…

About these ads