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

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.

Advertisements

16 Responses to Version control for Excel workbooks, Part 1

  1. jonpeltier says:

    Rob Bovey has built your disassembler and assembler, in his Code Cleaner add-in (http://appspr.com).

  2. So tell us more: what does your xl-unit allow us (you) to do?

  3. mikewoodhouse says:

    I’ve been using the CodeCleaner addin for years, love it dearly and yes, in the first instance it does enough.

    But source control, in all its versioning glory, needs to see all the changes when they happen and I’m all too aware that processes with manual elements have a tendency to fail, especially if I’m the provider of the manual aspect.

    So I think I want something more automated, perhaps an automatic export on every save. Or on every check-in. Somewhere in the cycle.

  4. My tool AutosafeVBE exports Excel’s vbprojects every 10 minutes (which you can change).
    It numbers the exports (01 is discarded and 2-10 renumbered to 1-9, number 10 always the latest one, you can do as much as 20)

    It was never designed as a version control thing though.

  5. Roland Kapl (a fellow Austrian) did SourceTools
    http://www.codeproject.com/KB/office/SourceTools.aspx
    It builds on Rob Bovey’s CodeCleaner.
    It exports and imports all sheets, modules, class modules, and forms
    as text files and then manages these files with subversion.

  6. mikewoodhouse says:

    @Erich – that looks fantastic. Downloaded. Something exciting to play with this weekend!

    Austrians must be a smart bunch – I was hired for my current job by an Austrian…

  7. Pingback: Version control for Excel workbooks, Part 2 « Grumpy Old Programmer

  8. Bruno says:

    Well, I have my own yet another tool (vbaMaven) to export & import VBA code from / to an excel file.

    It is free and works fine as used by my clients (I am freelance IT concultant)

    – Automation based connection to excel
    – Support password protected VBA Project
    – Log file
    – Macro call on PreUnbuil / PostBuild event
    – Possibility to wrap up in scripting tool (Ant, MSBuild with vbaMavenTooltask)
    – Setup : Java / ant download + install
    – Sample: vbaToolbox

    Well give a look and let me konw what you think about it.

    Thanks
    Datatunning Team

  9. Adrian says:

    Thanks, this is a useful post. I haven’t tried any of the tools yet but was very handy to find them all discussed in one place.
    Adrian

  10. laogui says:

    You reckon you’re old an grump – I am the original curmudgeon for sure; 42 years a programmer, but Excel is still a shining light… had to resort to CodeCleaner today but forgot it doesn’t work for code behind worksheets so the workbook is still GPFing out if any changes are made. That’s what brought me here older and grumpier still. I’ll look at part 2 then.

    I am definitely not Austrian, but still ‘Austria’ is part of my country… 🙂

  11. Still Wanting says:

    Sorry – I thought I was going to learn something about either (a) a prcoess for Source control of VBNA, or (b) better yet a Tool for managing VBA source.
    Your ramblings were interesting but I either cannot see the gems buried underyour musings or there simply was nothing concrete offered….

    Do you have a tool or Not?

  12. backlinking says:

    Great posting. Will you please write more about this subject.

  13. Bruno says:

    @laogui
    vbaMaven is exporting / immporting the code behind the worksheet which let your XL file clean of all code.

    The is as well a MSBuild tooltask on codeplex if you have to manage C# integration / migration.

    @Still Wanting
    There are tools like CodeCleaner or vbaMaven that allow you to import / export the code. you then use a source control over the files.

    If you think of something specific, you should give some more explaination.

    Thanks

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: