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:
- procedures that interect with the document (e.g. worksheet)
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.