Unit testing, and why it’s a Good Thing (IMO, YMMV)

OK, so there’s a codeplex project set up with no content as yet, and a 30-day deadline to get something in and published. Nothing like a bit of pressure to turn a chap’s thoughts to prevaricating.

Why a “unit testing framework” for Excel VBA? Let me see if I can put together a potted history.

Some years ago I became infected with the Agile development virus. Some of the practises they described reflected things I’d figured out for myself over the years, some made immediate sense in a “why didn’t I think of that?” way and some I’m still struggling to really get to grips with.

Concepts like refactoring, for example, just gave names and descriptions to things I’d been doing for years. I had not, however, put the idea of constant refinement together with automated testing, the idea of which was a revelation. Further, the Refactoring book led me into methodological areas such as Extreme Programming (XP), one of whose principal practices is Test-Driven Development (TDD)

The idea of TDD can be summarised in three words: red-green-refactor. Here, briefly is my understanding of how it works.

First write a test that is intended to expose some behaviour that you expect your software to exhibit. Then you run your test. It should fail. That’s the “red” part, and many frameworks display something red to give you a visual clue and astimulus to rectify the failure.

My framework is upset - I need to make it happy

Now that you’re “red”, you can write some code to implement the missing behaviour. The trick is to only do just enough to make the test pass. You should be able, in most cases to do so in a few minutes. (I’m very bad at this) Then you run your test again. All your tests, ideally. The test should pass and you should get a rewarding flash of greenness to signify that everything your tests specify is being delivered. Sometimes changing or adding code breaks something elsewhere – with the tests, you know instantly. I’m a huge fan of this.

Green - that should feel a lot better

As you add code to pass your tests, you should discover opportunities to improve the design, to “refactor”. Removal of duplication, improved coherence and reduced coupling can all be sought, all the while making small changes while still able to check that the code has not changed functionally because you can run the tests. This is the big win.

You don’t need a framework. You can just start writing code that runs code and checks the results. Refactoring to remove duplication and improve design will tend to separate testing code from functional code, however, and after a while extracting that reusable code into a separate package becomes obvious. The second half of the Kent Beck TDD book (probably the canonical work on the subject) shows the “bootstrapping” development of a unit testing framework in Python, using it to test itself. Inspiring, to people with minds that work that way….

But none of that was any good to me, sitting as I was in a job that mostly involved building large complex lumps of VBA within Excel – I didn’t have a testing framework. I searched, and to get up-to-date, I just searched again. A list of unit testing frameworks here, and another one here are both singularly reticent on the subject of VBA or any flavour, let alone the Excel one. Why would that be? Are Excel/VBA developers not really programmers? I certainly felt like a programmer. And I’ve used nUnit with C# and VB.Net and the test/unit and (charming) rspec libraries with Ruby.

So I built one. As I prepare what currently exists for publication, which will have to include at least some identification of all the ways it’s slightly broken in Excel 2007 (sigh), I’ll go through how I implemented the thing, so that when the real brains see it they’ll be able to show me where I went wrong. Or could have gone righter.