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.


3 Responses to “Testing VBA Code – Part 1”

  1. 1 Dick April 20, 2009 at 9:02 pm

    Oh, I’m really looking forward to this. I find my testing code is very solid and robust at the start of the project, but gradually fades away. The tests still pass, but the coverage is sub-optimal. And there’s not a lot written on the subject to motivate me (or shame me) into doing it better.

  2. 2 roymacleanvba April 21, 2009 at 8:37 am

    I’m honoured that you’ve dropped in. I’ll have to think carefully about what I post! I suppose what I want to do is bring some of the rigour of my former existence (OO – Eiffel – DbC – test frameworks , etc) to VBA. This is very much up for discussion, so your comments would be most welcome.


  1. 1 Testing VBA Code - Part 2 « Roy MacLean’s VBA Blog Trackback on April 22, 2009 at 10:02 am

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: