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

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.

Feels better once you've written code to pass the test

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.


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

  1. Jon says:

    Keep up the good work, I’m really looking forward to seeing your unit testing framework. I do a lot of work in Excel VBA and have started doing a lot in Visio VBA so hopefully what you’ve done can be extended to work in Visio and the rest of Office.

  2. DanF says:

    Agreed. I do a lot of work in VBA also and have been longing for a unit testing framework. I’ve only recently started on the agile/TDD kick (mostly with Ruby) — so the framework’s are particularly helpful.

  3. Ross says:

    Good luck with this, if you need any help/testers etc let us know, I’m sure dick and others will be knee to help too

  4. Mark Merrigan says:

    I’m excited about your work, and would be happy to help test.

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

%d bloggers like this: