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…


2 Responses to “Testing VBA Code – Part 2”

  1. 1 Peder Schmedling April 23, 2009 at 5:57 am

    I love this!

    I’ll have to say I’ve been missing some articles regarding automated testing of VBA code. I’m not an experienced programmer, but I know that much that this approach exists for other programming languages and that there should be a way of doing it in VBA.

    Just found your blog via “Newton Excel Bach, not (just) an Excel Blog”, and I’m looking forward to future posts regarding this matter 🙂

    best regards

  1. 1 Testing VBA Code - Part 3 « Roy MacLean’s VBA Blog Trackback on April 24, 2009 at 1:13 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

April 2009
« Mar   May »

%d bloggers like this: