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.
Rob Bovey has built your disassembler and assembler, in his Code Cleaner add-in (http://appspr.com).
jonpeltier
10 September 2008 at 19:44
The link doesn’t work. Google shows the link should be http://appspro.com but that page seem to be down as well.
Anybody know where to find this Code Cleaner add-in?
Johan
7 May 2009 at 13:11
It was working a minute ago:
http://www.appspro.com/Utilities/CodeCleaner.htm
mikewoodhouse
8 May 2009 at 12:47
So tell us more: what does your xl-unit allow us (you) to do?
Jan Karel Pieterse
11 September 2008 at 05:17
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.
mikewoodhouse
11 September 2008 at 11:34
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.
Jan Karel Pieterse
11 September 2008 at 12:32
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.
Erich Neuwirth
12 September 2008 at 12:27
@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…
mikewoodhouse
12 September 2008 at 16:52
[...] 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, [...]
Version control for Excel workbooks, Part 2 « Grumpy Old Programmer
20 April 2009 at 16:28
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
Bruno
15 May 2009 at 13:38
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
Adrian
6 October 2009 at 13:34
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…
laogui
28 October 2009 at 23:34