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…