Version Control in Excel
Early in 2010 we started using FogBugz, with the integrated Kiln. In brief, FogBugz is an issue tracking tool, while Kiln is a version control system. Version Control Systems (VCSs) help track releases and allows the ability to easily rollback to an earlier version. Kiln also allows teams of developers to merge their code. To maintain our professional standard we needed a method to track versions and allow rollbacks etc, so we chose FogBugz and Kiln to give us this functionality.
When considering any VCS, you have to consider your business needs. Even if all reviews call a particular option “the best”, that doesn’t promise that your coworkers or employees will use it effectively. It’s important to adopt a system that’s going to make sense for your case. This is our story about this discovery process, and how it relates to Excel development.
Under the right circumstances, FogBugz can act as a very strong driver for development. At the beginning of a project, the project can be broken down into a multitude of tasks and sub-tasks, which can be loaded into FogBugz along with duration estimates, deadlines, and milestone markers. This is great for a project where much of the goal is known at inception. However, our projects usually grow once the client sees the range of tasks that can be automated by using the more advanced Excel functionality. This means that we can’t put much detail into FogBugz to start with, so we don’t yet make use of all of its features.
Because Excel macros / VBA code is stored directly within the workbook, it’s hard to implement decent version control – every upgrade means a new file; there’s no acceptable method to directly access the code, because it’s all intermingled with the existence of particular worksheets, worksheet-specific code, forms, etc. This makes version tracking difficult, because we can’t just do a code merge or a diff.
Even if Kiln were perfectly suited to an Excel-specific form of version control, we also need the version number to be easily visible to our clients. Without this, it quickly becomes difficult for them to know which is the current version, and harder for us tell what version they are talking about. To address this, we append a version number to the end of every file name. Sure, it’s clunky, but it works. We also include the version in the workbook, so that the version number can be dropped from the filename when it’s deployed by the client.
Every time we send a client a copy of the file, we update the version number – so the version number directly corresponds with that communication. After the email has been sent, we append the word ‘dev’ to the sent file; this allows us to easily keep track of whether a file has been modified since it was delivered. Thus, any file that doesn’t have ‘dev’ in the name has been sent to the client, and can be easily found in Google Mail to find the relevant conversation thread. Using Google Mail gives the added benefit of an automatic ‘backup’ of the sent files! We also maintain a daily off-site backup.
Our larger projects require a significant amount of coding for each version, so relying on one constantly-changed file is too risky. The chance of accidentally deleting code, corrupting the file, or even having the HDD fail is too high to not have an off-site, incremental system in place. We needed a way to back up files while retaining past versions. This is where Kiln comes in. One might say that Kiln is overkill for this – we could instead set up a repository on this website and host the data here. Kiln was just more convenient as all we had to do is install the client. Plus we can now take advantage of a few more features of the software.
So what we ended up with:
- All Excel files include a version number.
- Any version sent to a Client is accessible directly through Google Mail, from any location.
- Development versions are tracked within Kiln
- Clients are able to see the displayed version number, so they have a number to refer to (instead of just saying “the current version”)
- Data is stored in at least three places for sub-versions, and at least four for versions sent to a client.
- Any updates that refer to FogBugz tasks are tied into FogBugz automatically.
- Some overhead from maintaining the version number with the file system.
- Many files in the file system and repositories, as we don’t tie the differently-named files together directly, and often do not delete previous versions.
- Remembering to change the version number in both the file system and the file itself is harder than one might think. We will probably write a short Excel macro to extrapolate the version from the file name and automatically update it.
It’s been an interesting process that has developed further and further over the last 8 months. We’ve found shortcomings and have overcome them in really quite simple ways. Perhaps the biggest lesson is that a single tool might not always be enough on it’s own. But then, we’re Excel specialists – so that came as no suprise!