Grumpy Old Programmer

(stackoverflow rep: 8417, Project Euler 87/261 complete

When we want Excel to talk to a compiled library, the list of options available to us is not long.

On the one hand, we can write a compiled library that references the Excel SDK (pre- or post-2007), setting its prefix to XLL for convenience, typically we’d grit our collective teeth and accomplish this with C or (cough, spit) C++. One big plus here is speed – we’re talking to Excel in its own language. Or something awfully close to it. Another plus, of course, is that this approach probably involves the minimum set of dependencies for non-enterprise distribution convenience. If you haven’t spotted the big minus then we should probably agree to disagree on the relative merits of programming languages.

On the other hand, we can write a library that exposes its functions through good ol’ COM, referencing it though the Tool…Add-ins dialog. That’s probably still the best-known way to integrate functionality developed in .NET, although it does involve paying a performance price as the COM interface is crossed and recrossed. This would be the place in the world most usually occupied by VSTO. Of course, you could also write your COM library in non-managed code: VB6 is easy (if you can find it) but slow, the choice from others depends on your capacity (or desire) for pain.

On the gripping hand, we have ExcelDNA, which for my money is one of the all-round cunningest things you’re likely to come across in quite a long time. (Unless you’ve already encountered its cunningness, of course, in which case you’re probably already nodding along in sage agreement). How so? How about being able to write your spiffy new functions in C# but without having to incur the needless ins and outs of the wasteful and superfluous COM middleman?

ExcelDNA provides a small XLL that can talk to managed code. There’s one attribute to set in order to make a function visible to Excel and one libary to import (in order to be able to add the attribute). Oh, and a little config file to tell the XLL what to load. That’s it. Actually, that’s the complicated version. In case you didn’t know, the CLR includes a compiler, allowing code to be created and compiled at run-time. The simplest way to talk to Excel from .NET via ExcelDNA is just to put your code directly into that little config file and let the XLL compile it at load time. OK, there’s a second or so’s overhead, but how simple – anything simpler would probably have to be involve 21st-Century language integration where VBA lives today.

By now you’re probably muttering something like “write code, fat bloke”, which is a little cruel, but a sentiment otherwise understandable. Now only the other day, Dick Kusleika posted his take on the everybody-has-one timeless RangeJoin() UDF topic. Here’s a rather simpler implementation:

<DnaLibrary Language="CS">
<![CDATA[
using ExcelDna.Integration;
using System.Collections.Generic;

    public class MyFunctions
    {
        [ExcelFunction(Description="Joins cell values", Category="My ExcelDNA functions")]
        public static object RangeJoin(object[,] cells)
        {
            List<string> list = new List<string>();

            foreach (object o in cells)
                list.Add(o.ToString());

            return string.Join(",", list.ToArray());
        }
    }
]]>
</DnaLibrary>

To get it running, I saved it as “RangeJoin.dna”, then copied the ExcelDNA.xll into the same folder and renamed that to “RangeJoin.xll”. Because it isn’t currently anywhere on my path, I also put ExcelDna.Integration.dll in the folder. Then I double-clicked my new XLL file to start Excel and load the library. (Pictures below are from Excel 2002, but the code is tested up to 2007).

There’s a tiny extra delay before the usual macro warning appears – the C# code’s being compiled, which is where compile-time failures are displayed. Then you’re up and running. The function shows up in the Insert Function dialog…

… and we can try calling it:

… with the following result:

I did notice the the =NA() output and the Euro amount came out different to what I’d have hoped. It rather looks like I’d have to dig a little deeper into the ExcelDna.Integration.dll and the ExcelReference object in particular in order to be able to access the equivalent of VBA’s Text property.

Still, not bad for a quick exercise, I’d say.

There are some alternative approaches to the dot-net-and-excel-without-going-anywhere-near-COM-add-ins topic:

For Free

Excel4Net is now free – it seems to work by implementing a single worksheet function that calls out to managed code;

The last time I looked at XLW it was focused on C++. That’s changed in the interim – C# and VB.NET are now options.

Possibly Paid-for Propositions

Len Holgate appears to be at beta test stage with JetXLL, which looks like it’ll be a commercial offering;

I also just discovered ExHale. In beta, appears current – can’t see what the terms are likely to be;

Definitely Demanding Dollars

Possibly the first implementation, ManagedXLL is (was?) a paid-for product that looks to have been similar to, but possibly broader in scope than ExcelDNA. It’s not clear whether the company is still active or the product is still available – a curious colleague recently tried to make enquiries and failed.

It’s a long time since I last wrote about Lotus Notes and the unlimited joy that is its, er, idiosyncratic interface, not least since IBM’s decision¹ to host the client in Eclipse.

Too long, really – it’s such a rich source of oddness. For example, today I recevied an invitation to join some colleagues (located in Frankfurt) in a video conference. The heading in the message informed me that:

Which seemed a little odd, since a quick phone call earlier had seen some time on Wednesday morning identified as the preferred time. No matter, I opened the message to accept the invitation (it’s not clear why I couldn’t do that from the inbox/preview, but I can’t). Clicking “Accept” put the meeting into my calendar:

Whoops! Well, it was more in keeping with what I expected. To settle myself, I went for a coffee (you can tell Christmas is coming, btw: Starbucks are using the Red Cups). When I got back, Notes had been busy – the Inbox message now had this:

OK, it’s now accurate, but I’m not sure how I feel about a message being modified in any way after I’ve opened and read it.

Of course, we’re 0.5 of a release behind the current version, so maybe stuff like this has been fixed by now.

 

---

 

The BBC reports that, in an Australian Science magazine article, an Australian psychology expert “who has been studying emotions has found being grumpy makes us think more clearly”.

 

To which I can only say “hmph”.

 

---

¹ I’d love to have been a fly on the wall at that meeting.

 

(stackoverflow rep: 7576, Project Euler 83/257 complete)

Reading Joel’s¹ Duct-Tape Programmer article this morning (in the interests of full disclosure I should admit without additional prevarication that I have a large roll of “Duck” tape in the second drawer of my desk as I type) one sentence smacked me metaphorically between the eyes:

“Shipping is a feature”

I was transported back a couple of decades to the time when the bank for whom I was then working discovered that it was building not one but two settlement systems (the things that ensure that what traders agree should happen actually does) in two locations: London and Zurich. In London we were targeting our DEC VAX/Oracle platform, while the Swiss were designing with their local Tandem Non-Stop installation. And we’d both have gotten away with it if it hadn’t been for that meddling CEO…

It was decreed that The Wise Men (external auditors) be appointed to review the two projects and pronounce which should live and which should consign its members to the dole queue.

The Wise Ones duly decamped to Zurich to spend a few weeks working through the cabinets of meticulously-detailed standards-compliant design documentation that had been lovingly crafted over the past several months, with coding about to start. Then they came to see us. It didn’t look so good.

What documentation we had was months old (from a previous, aborted start of the waterfall) and coated in Tipp-Ex. Remember the white error-correction fluid we used all the time back in the 20th Century? When we still wrote “memos”? After a week of vagueness and frustration a set of presentations were scheduled for the Friday, at which we proposed to try to fill in the gaps.

London won.

Yay us, but how? On most objective measurements we were deficient when compared with our continental rivals, even we agreed on that. But on that Friday afternoon, I got to stand up to summarise the differences, positive and negative between the two projects, as seen by the London team. I think what may have swung it was the part where I got to say “our system has been settling trades since 3 o’clock this morning”.

In about nine months, one team had done everything by the Book (don’t know the title, but I bet it had “Structured” in it) and had reached the point where they had, well, a book. Lots of books, in fact – they’d worked really hard. In the same time, we built a system and even better, shipped it. I don’t think anyone had written any Agile books by then – even if they had, we hadn’t read them.

Our team hadn’t done an awful job by any means, you understand: there’d been a few weeks of up-front requirement-gathering/scoping.  We had a massive data model that we Tipp-Exed down to the minimum needed. We had an outline architecture that, through luck or judgement, proved to be appropriate. Probably best of all, though, we sat with our users while we built their system. Better, as we built different features we moved around so we were always within speaking distance of our domain expert (I don’t think we’d done the whole “domain” thing then – we just called them “users”). So  we seldom got very far off track while stuff got built, and we were, with hindsight, feature-driven and relatively lowly-coupled/highly cohesive at the component level, all Good Things. Mostly written in COBOL, too.

Looking back, we were lucky: we didn’t manage to repeat the magic and fell back into time and cost overruns with the next couple of large projects. At least we were still being paid, unlike our erstwhile colleagues in Switzerland.

---

¹ I call him by his first name because we share so much; we’re only a few slots apart on page 13 of StackOverflow as I write this. Page-mates, don’t you know.

(stackoverflow rep: 7284, Project Euler 83/252 complete)

My main work PC was upgraded to IE7 yesterday. That’s one less IE6-infected machine to worry about. Unrelated to that (I suppose) is that the Aventail VPN product that I have to use each day decided it wanted to upgrade. I’m still trying to figure out how to make that work on IE7 but fortunately I also have an older machine that seems to have been immune to the upgrade, so I switched to that.

After some back-and-forth, I saw the happy news that this was happening:

While this was cogitating, a message popped up, partially obscured by the progress dialog. So I moved it. The dialog, that is, not the message. And I saw this:

How confused must the developer of this part of the installer have been to have built the progress bar as an entirely separate window? And how much more difficult must it have been to do it that way? I amused myself dragging the main dialog all over my desktop while the progress bar stayed resolutely where it was until the install completed.

(stackoverflow rep: 4824, Project Euler 72/241 complete)

Not a month ago (about seven , actually) I started to gibber about source code version control and Excel. Then Stuff got In The Way and I was derailed. Well, Ruby-on-Railed, really – I’ve been almost totally immersed in intranet development and related matters and mostly having lots of fun.

Then along came Internal Audit (shudder). Some degree of consternation appeared to exist about the potential (albeit slight) vulnerability of our Source Control solutions (SourceSafe for Visual Studio and Excel stuff, subversion for the webby bits). Combined with the newly-available – and rather sexy – new enterprise SourceForge that had materialised, we bit the bullet and set out to put everything into the Company-Approved Solution. Which dragged me seven months into the past to ask again – how does one diff an XLS?

That’s not a rhetorical question, I do have a sort of answer. I’m thinking along the following lines:

• Some VBA component-extraction code in an add-in
• Perform the export on a Save or Close
• Save VBA references as a separate text file

I’m not going to get into dumping worksheet content, although there are arguments for doing so.

Excel exposes the handy WorkbookBeforeClose and WorkbookBeforeSave events on the Application object. We’ll need an Application object and it’ll have to be declared WithEvents, so we need a class. I’m going to call it WorkbookDecomposer:

Option Explicit

Public wkbk As Workbook
Public WithEvents xlapp As Application

Private Sub xlapp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Set wkbk = Wb
    Decompose
End Sub

(We’ll have the same code for the Close event). Each component can be exported, and the references can be dealt with something like this:

Public Sub ExportReferences()

Dim ref As Reference

    With New FileSystemObject
        With .OpenTextFile(.BuildPath(wkbk.path, wkbk.Name & ".references"), ForWriting, True)
            For Each ref In wkbk.VBProject.References
                .WriteLine RefToString(ref)
            Next
        End With
    End With

End Sub

To start the thing, we can have a module with a little Sub such as:

Option Explicit
Option Private Module

Public decomposer As WorkbookDecomposer

Public Sub StartDecomposer()
        Set decomposer = New WorkbookDecomposer
        Set decomposer.xlapp = Application
End Sub

Now, as long as we don’t lose state, running this code once will give us an object that will automatically export copies of our code for storing separately in our code control system. So we can diff. The decomposer can be extended to shell out useful source control instructions such as commit, perhaps automatically, perhaps not.

I don’t like that “as long as we don’t lose state” thing. When we’re coding in VBA we lose state all the time. The best I can come up with is to use an OnTime macro to keep trying to restart the decomposer object:

    If decomposer Is Nothing Then
        Set decomposer = New WorkbookDecomposer
        Set decomposer.xlapp = Application
        Debug.Print "Decomposer (re)started @ " & Now
    End If
    Application.OnTime Now + TimeSerial(0, 0, 10), "StartDecomposer"

It sort of works, but it’s potentially intrusive when we have incomplete/invalid code (hardly unlikely in the scenario under consideration) and the OnTime macro may fail to fire for many reasons.

What are the alternatives? Custom external scripts? An external process that monitors changes on XLS files and auto-exports?

(stackoverflow rep: 3958, Project Euler 66/236 complete)

Dick Kusleika wrote about some of his personal code construction methods in Daily Dose of Excel recently. Something that caught my eye was this:

I generally end up with a lot of Property statements that do nothing but read and write to a module-level variable.

Probably fairly familiar, goodness knows I’ve written enough of those, and I never used MZ-Tools to automate the process, either. So I was nodding away as I read, muttering “amen brother” and whatnot, when I had a vicious attack of the Whys.

Why do we write a Property Set/Let/Get just to set, let or get a member variable? What’s a Property for, anyway?

I think a Property should probably only be used to assign a value when some processing needs to occur other than simply setting the member variable. Code such as:

Private mName As String
Public Property Let Name(value As String)
    mName = value
End Property

…just doesn’t seem to be adding any value. Rename the member variable, make it Public and move on, secure in the knowledge that you just saved some keystrokes and reduced your carbon footprint (well, maybe) by some infinitesimal fraction of a gram. The world thanks you.

Now if you need to validate the input in some way, a Property Let may be the ideal place to do so. It may not, as well, but that’s not important right now.

What else? What if you need something to happen, other than validation, when you set a property? Here’s where I’d suggest that you’re doing something wrong: I’m not a big fan of hidden side-effects (British understatement warning) – I have too many scars from self-inflicted wounds from doing that. If setting a property causes some activity or state-change, then the code should probably be refactored into a Sub with a suitably informative name.

I suppose write-only properties need a Let/Set (so that we can ensure no Get is written) but how often do we need that?

Otherwise, let’s just access the member variable directly without the indirection (think of the planet).

Getting.

Honestly, how different is a Property Get from a Function?

Given a class defined thus:

Public ws As Worksheet

Public Property Get Wksh() As Worksheet
    Set Wksh = ws
End Property

Public Function WkshF() As Worksheet
    Set Wksh = ws
End Function

… what are the differences between the three Sets here:

Public Sub GetWksh()
Dim wsh As Worksheet
    With New Class1
        Set wsh = .ws
        Set wsh = .Wksh
        Set wsh = .WkshF
    End With
End Sub

I submit, Your Honours, that the only time a Property Get makes much sense over a Function is when there’s no pre- or (ugh) post-processing around the accessing of the member variable. In which case, why bother?

Anyway, Proudhon wouldn’t have liked Properties.

(stackoverflow rep: 3856, Project Euler 63/235 complete)

Good grief. I wrote the first draft of this about a month ago, planning on completing and posting it when the code was done. I expected that to take a few more days. A little more work required on the estimating front, then.

I’m starting to go off Oracle.

Let me put that into context a little. I first encountered Oracle some time in 1998, when version 5 was all the rage. I’d actually taught data analysis, third and fifth normal form, stuff like that for a few years previously but actual hands-on table creation had to wait. Strange but true. Anyway, over the next two or three years, some of which I spent as “Technical Architect” for the investment bank where I worked, I got to be something of a whiz with both version 5 and the swanky new version 6. Heck, I know the query optimiser’s rules off by heart. I’m not just blowing my own trumpet, mind: when I was untimately (fortuitous typo retained) laid off, I was offered a job by Oracle, which I rejected because I didn’t want to take a pay cut.

I spent five more years in Oracle-land with another bank before drifting into the realms of Sybase in its early MS SQL Server guise, and then Sybase itself across three jobs and four years (it seems like longer). Now, fourteen years after we parted company, Oracle and I are back together.

But we’ve both changed. I no longer code in COBOL and have acquired a pathological dislike of business logic in the database. Oracle has a cost-based optimiser, loves to grab all your business rules (more processors = more revenue) and has become a fat bloated porcine creation. Even the free “personal” 10g Express Edition for Windows is a 165MB download.  (OK, SQL Server Express 2008 is even larger, I checked). When running, the thing takes out a 642MB virtual machine. OK, it’s almost entirely swapped out, but still.

But Oracle is still a helluva fast platform. Unoptimised I was seeing about 8K inserts a minute on my development PC, three times that on a real server. Unfortunately our db server currently lives abroad for tax reasons (or something) and the network latency is fierce. About 900 inserts a minute fierce. So I needed to batch up my inserts or enter the living hell that is SQL Loader.

In order to get multiple insert processes working within my Ruby On Rails-based code, I split each file into several fragments, then run a separate process on each fragment. This takes a bit of doing, generating lots of CMD files that run ruby scripts with “START [/WAIT] CMD /C whatever_I_want_goes_here“.

My file-splitting code, I thought, was rather spiffy – it needs to put the headings from the original to each fragment (because they’re used to figure out what’s in the file) then it starts dealing out the records:

def create_fragment_files(paths)
  File.open(file_path, 'r') do |fin|
    hdgs = fin.readline.chomp
      files = paths.map { |path| File.open(path, 'w+') }
      files.each { |fout| fout.puts hdgs }
      fin.each_line do |line|
        files.first.puts line
        files.push files.shift # the first shall be last...
      end
    files.each { |fout| fout.close }
  end
end

There are faster ways, I’m sure – I could calculate the “ideal” file size and dump records into a file until it’s reached, but this is fast enough (well under a minute for an 85MB file) and it pleases me.

There’s a handy little library, ar-extensions, that makes batching of inserts possible within ActiveRecord (which is the default data mapping library within Rails). It works nicely with MySQL, but turned out to have the Oracle code stubbed and invalid. It only took me a day or two to find a solution to that problem, although I still haven’t figured out how to push an update through a proxy server to github. Finally a chance to do something open sourceful, and I’m thwarted at every turn.

So all in all, it’s taken a month. OK, a month in which a lot of other stuff got done, but still.On the plus side, I just fired it up and I’m watching about 36,000 inserts a minute go through. It’ll be faster when the lookup tables are fully populated. (Another day on, and I’m looking at it: 46,000 – and I still have a few tricks up my sleeve)

While the nearly-two years’ of data is backfilling I now get to rewrite the front end.

And the point of this post? In no small part, to remind me of what I actually spent the lion’s share of the last month doing. Also, to record my first-ever open-source contribution, even if I still haven’t worked out how to get my source out into the open.

If you have been, thanks for your forebearance.

(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)

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.

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.

Considering the origins of the various Office components (and a light bulb may be flickering dimly in the minds of those who can remember back that far) we’ve come a long way in the application automation stakes. Can you remember Access Basic ? WordBasic? Excel macros pre-VBA? Did earlly versions of Outlook have macros?

Office application macro capabilities have come a long way but they’ve been pretty much stuck at the last version of the VB6 runtime. That’s about a decade with no significant change. In that time Microsoft have hammered their way up to version 3.5 of .NET, but with only half-arsed (my opinion) gestures made towards improving/extending/renewing the internal automation aspects of Office.

I say “half-arsed”, which, I dunno, might be a little harsh, but the whole VSTO thing just seems like a thin wrapper on COM Interop, which is itself a wrapper to permit communication between shiny new .NET code and skanky old legacy stuff. Why do I need to use VSTO at all? If I need complex, compiled high-performance extensions then I’m probably better off getting one of my C++ literate colleagues to write a “proper” XLL add-in that won’t have to deal with COM at all. If I don’t need high-performance then any scripting language that can talk to COM will do the job. Heck, I can use Ruby (and do) – David Mullett has a whole blog on the topic of Windows automation with Ruby.

Microsoft want to get away from VBA, I think that’s clear. They’re never, never, never going to get the current host of non-technical VBA users to switch to VSTO. Forget it, it’s not going to happen. Hell, I don’t want to have to use VSTO and I’m one who should benefit from raising the cost of entry to macro programming. Do MS want to get away from COM? Maybe. They wanted to get away from DDE too, but it’s still lurking somewhere not too deep inside Windows.

But here we have the Dynamic Language Runtime, which sits on top of the .NET CLR and allows fun things such as IronPython, IronRuby and others. Snappy performance, ability to use .NET libraries, interoperability between languages, sounds like fun. According to Wikipedia, the plan is to move VB to run on it. Now there’s a thought: why shouldn’t Excel be rebuilt in sexy modern managed code, with VBA ported to the DLR and the old COM interfaces reduced to a shim to keep backwards compatibility? Then we’d have macro programming where it should be, in the application, with the billions of lines of legacy code still runnable, and I’d be able to hit Alt-F11 and work in Ruby.

Seems like a win-win scenario to me.

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.