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.

Tie an Office 2007 Ribbon…

Credit’s still a-crunching in bank-land, although for once it’s just possible that I find myself in a relatively secure part. But I’ve felt secure in the face of crisis before: wrongly as it transpired, so we’ll see.

I can’t say I was ever much of a fan of the rather clunky way in which we used VBA to add and extend menus, toolbars and (in particular) categorise user functions (who’d have thought we’d still be calling Excel 4 macros in the 21st century?).

But xlUnit needs to expose its (minimal) UI into Excel 2007 in an appropriate fashion, so I need to bite the bullet and figure out how to tie me a ribbon. in Office Fluent UI. Gulp.

It’s times like these that turn a grumpy old man’s thoughts to How-tos. I need some step-by-step FLuent UI For Dummies education. Google time. Oho – MSDN have Office 2007 Visual How Tos. How convenient. Time to save a draft and peruse…

Let’s start with Adding Custom Buttons to the 2007 Office Fluent User Interface. Ooh, a video. Doesn’t work. And the XML is nonsense. And to be honest, for the first time in well over a decade I don’t actually have a Microsoft development environment loaded on my laptop – and I don’t seem to be missing it. There must be an easier way. Back to Google.

Now this is more like it. Run a little utility to insert the XML – and the example code is valid this time – and hook up to a VBA routine. Not only that, but it explains how the process works so that you can work without the utility completely.

WIth the buttons and bows taken care of, I’d better take a step back and figure out what’s got to go on them…

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.

Version control for Excel workbooks, Part 1

(I expect this will end up being renamed “part 1”. Oh heck, I’ll change it now)

For whatever sins I may have committed, I have spent much of the last (pauses to count fingers, pauses longer to add toes) 18 years building Excel spreadsheets. I’ve been using the application, one of Microsoft’s rare gems, for all its documented frailties, since version 3.0 and I’ve spent a good part of my life in VBA since it first appeared in version 5.0, a little over a decade ago. I’m very fond of Excel and it’s been my number one meal ticket for most of the period.

By now I’ve built up a number of useful utilities that I carry with me from job to job. I have an Excel/VBA unit-testing framework, a VBA profiling tool, a simple code analyser, a (thin) abstraction of ADO for database access and several more, rather less well-defined bits and pieces.

a little bit of xlUnit

At least in part to demonstrate how clever I am (or think I am, there could very well be a gap between perception and reality) I’m looking at making these available – before they lose all relevance – under some sort of open-source license, hosted somewhere suitable – CodePlex seems kind of appropriate.

But a lot of the benefit of source code control, once we’re past the obvious security-blanket stuff, derives from being able easily to examine different versions and see the changes that were made. Which is a bit tricky with an XLS file. OK, we could go full-bore 2007 and work exclusively with the XML formats, but an awful lot of people are going to be excluded on that basis and to be honest I’m not convinced that trying to read an XML Diff on a VBA project would be a tremendous amount of fun.

It seems that what should be checked in to the version control system ought, as far as possible, to be text. So all the VBA modules, classes, forms and whatnot should be checked in independently of the XLS file. Which points to some sort of Excel “disassembler” and “assembler” functions that can pull a workbook apart and put it back together. Without breaking anything.

Resources would include code modules and some text representation (a sort of “manifest”, if that word’s not already too overloaded) of references. I kind of like the idea of taking worksheets apart and storing them separately, although none of the things I’m dealing with are really “front-end” apps, so that’s probably something for the future.

While the processing involved could be built in anything that can interact with Excel as a COM server, I rather like the idea of doing it in Excel. I think I’ll explore that first, using xlUnit, my testing framework. In fact, in the interests of increased self-reference I think xlUnit could be one of the test cases.

I’d better go and start writing tests.