Lazing On A Sunny Afternoon

(stackoverflow rep: 2906, Project Euler 59/230 complete)

Below (lightly edited) is a recent answer to a question on StackOverflow. The question is pretty much a waste of time, but what an answer!

Laziness is indeed the first of the Three Programming Virtues, but it is misunderstood. Programming Perl defines it well:

* LAZINESS: The quality that makes you go to great effort to reduce overall energy expenditure.

Good programming calls for laziness, but laziness requires hard work. Good programmers must constantly think of and implement new ways to be lazy. The first compiler had to be written in assembly, and the first assembler had to be written in machine language. Wonderfully lazy, but hard. You don’t get to call it a day after an hour just because what used to take a day takes an hour.

That’s about as close to an encapsulation of my personal programming philosophy as I’ve ever seen. The virtue of Laziness is closely related, if not identical to the DRY (Don’t Repeat Yourself) Principle as espoused by the Pragmatic Programmers in The Pragmatic Programmer.

(I just found myself wondering if there’s a way to refactor that last sentence to remove some of the duplication)

Laziness - all I have to do is this...

The idea behind xlUnit (which has been languishing somewhat since being codeplexed in, good grief, September) was to minimise the amount of manual repetition involved in testing Excel/VBA code as it is developed. As a useful side-effect, it also made TDD (Test-Driven Design, or Development, depending on your choice of definition) possible. A fair amount of the code complexity of the framework is involved in eliminating repetitive manual tasks such as test class creation.

There are only three significant UI-level entry points in xlUnit as it stands: application creation (used rarely, only about once per app) class creation, used rather more often, and test execution, which is used all the time. Oh, and there’s a little “Options” dialog, but it only has one setting and that’s not really very interesting so we’ll gloss over that.

Since the first two things are used relatively infrequently, I tucked them away on a menu, whereas the test execution, which is an all-the-time thing, gets a toolbar button. I could have been fancier with that, but I never got past the simple text “Run Tests”, which had the extra benefit of giving a larger target area for the mouse.

...and I get this for free.

There is no interface within the VBE, which is most definitely a shortcoming. To be honest, I only even tried briefly (failing, obviously) to get it to work: with a dual screen setup, which my working environment has had for the best part of a decade, both workbook and VBA are usually visible, so I can get to the “Run” button easily enough. For a similar reason, there is no built-in keyboard shortcut at present. Of course, in Excel 2007, it’s all a bit of a mess. I have the (massive) RibbonX book, by the way, I just need the intestinal fortitude to sit down and read it.

The “Create Testable Application” and “Create Testable Class” routines both live in the add-in itself and are located within UserAccessibleEntryPoints.bas. I’ll come back to “Run Tests” another time – not only was it a bit tricky but I have some new ideas that would make it even trickier.

Choosing to create an application causes a throwaway instance of the xlUnitCodeBuilder class to create a workbook and its tester and create references from the tester to both the application (so it can call classes there) and the framework (so it can use the framework). The same class contains the code to create new classes. I think I put the functions together because they’re about building code but I don’t think I’ve achieved very good separation of concerns here: there’s workbook creation and test class creation and they would probably be better off being separated. Since there’s work to do with Excel 2007 anyway, I think I’ll refactor that bit next time it’s open.

I mentioned “throwaway instance” above without explanation: it’s a way I describe this VB pattern:

    With New UsefulClassThatDoesntReallyNeedToBeDimmed
        .DoSomethingProfound
        .DoSomethingSimilarlyDeep "AndMeaningful"
    End With

Is there a more “standard” way to describe this? Something using “anonymous” perhaps? Anyway, I use it a lot (and I’m now fervently hoping that no-one points out some awful risk that I’m running as a consequence) as it’s a way to get part of the economy of class (“static” methods in Java or C#, where the word means something rather different than in VBA) method.

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.

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.

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.