Making Excel Apps Testable

A typical unit testing framework provides a means to identify tests, execute them and report on their successful completion – or otherwise. Remember that a failing test isn’t a bad thing, it’s an invitation to extend one’s code. Ideally the framework should take care of everything that isn’t about writing new tests.

The add-in, the app and the test harness

Methods vary across frameworks, but it seems to be true (from the limited sample I’ve actually looked at) that where reflection is available, then either by convention or attribution, tests can be identified at run time. For example, nUnit tests are denoted by having the “Test” attribute assigned to them, while Ruby’s test/unit looks for methods whose names start with “test” at execution time. While this may not seem such a big improvement over manually adding calls to your test routines, anything that lowers the amount of unnecessary effort on the part of the user is worthwhile, especially when the idea is that you write a lot of tests.

VBA doesn’t exactly have that much reflective capability, although we do have the ability to scan code modules, which could pretty much amount to the same thing. Not only that, but we can write code on a “just-in-time” basis, which is really handy. Very dynamic.

In xlUnit I have a TestFinder class whose job is to examine the modules in the test application to find all the classes that implement the “TestCase” interface. Then I build a new disposable test module that, when executed, builds a Collection of TestCase objects, one per class identified. Finally, a TestRunner object executes the “Run” method of each object. I’ve chosen, for the moment, to ignore the common “Fixture”, an aggregation of cases, mostly because it’s not obvious how to implement it in a frictionless way.

As mentioned above, a Test Case has to implement the TestCase interface:

Option Explicit

Public Sub Run(R As TestReport)
End Sub

Public Property Get Title() As String
End Property

Public Sub Setup()
End Sub

Public Sub Teardown()
End Sub

Setup() and Teardown() are pre- and post-execution routines – for creation of the conditions necessary for the execution of the tests within the class and tidying up afterwards. I’m not completely happy with the way this is done at present: it’s less flexible and hence less useful than in some (at least) of the other frameworks. And when I manage to persuade CodePlex to let me upload something via TortoiseSVN I’ll start to document the weak areas as issues. I hope when I say “when”, I don’t really mean “if”.

I need to say something about the “plumbing” aspects, which I’m both rather proud of and which, perhaps paradoxically, are also in need of a fair amount of work. I’ll try to get to those shortly, by which time (assuming “when” not “if”) there should be something to look at.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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: