Best way to do Version Control for MS Excel

I've just setup a spreadsheet that uses Bazaar, with manual checkin/out via TortiseBZR. Given that the topic helped me with the save portion, I wanted to post my solution here.

The solution for me was to create a spreadsheet that exports all modules on save, and removes and re-imports the modules on open. Yes, this could be potentially dangerous for converting existing spreadsheets.

This allows me to edit the macros in the modules via Emacs (yes, emacs) or natively in Excel, and commit my BZR repository after major changes. Because all the modules are text files, the standard diff-style commands in BZR work for my sources except the Excel file itself.

I've setup a directory for my BZR repository, X:\Data\MySheet. In the repo are MySheet.xls and one .vba file for each of my modules (ie: Module1Macros). In my spreadsheet I've added one module that is exempt from the export/import cycle called "VersionControl". Each module to be exported and re-imported must end in "Macros".

Contents of the "VersionControl" module:

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i%, sName$

With ThisWorkbook.VBProject
    For i% = 1 To .VBComponents.Count
        If .VBComponents(i%).CodeModule.CountOfLines > 0 Then
            sName$ = .VBComponents(i%).CodeModule.Name
            .VBComponents(i%).Export "X:\Tools\MyExcelMacros\" & sName$ & ".vba"
        End If
    Next i
End With

End Sub

Sub ImportCodeModules()

With ThisWorkbook.VBProject
    For i% = 1 To .VBComponents.Count

        ModuleName = .VBComponents(i%).CodeModule.Name

        If ModuleName <> "VersionControl" Then
            If Right(ModuleName, 6) = "Macros" Then
                .VBComponents.Remove .VBComponents(ModuleName)
                .VBComponents.Import "X:\Data\MySheet\" & ModuleName & ".vba"
           End If
        End If
    Next i
End With

End Sub

Next, we have to setup event hooks for open / save to run these macros. In the code viewer, right click on "ThisWorkbook" and select "View Code". You may have to pull down the select box at the top of the code window to change from "(General)" view to "Workbook" view.

Contents of "Workbook" view:

Private Sub Workbook_Open()

ImportCodeModules

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

SaveCodeModules

End Sub

I'll be settling into this workflow over the next few weeks, and I'll post if I have any problems.

Thanks for sharing the VBComponent code!

TortoiseSVN is an astonishingly good Windows client for the Subversion version control system. One feature which I just discovered that it has is that when you click to get a diff between versions of an Excel file, it will open both versions in Excel and highlight (in red) the cells that were changed. This is done through the magic of a vbs script, described here.

You may find this useful even if NOT using TortoiseSVN.

It depends whether you are talking about data, or the code contained within a spreadsheet. While I have a strong dislike of Microsoft's Visual Sourcesafe and normally would not recommended it, it does integrate easily with both Access and Excel, and provides source control of modules.

[In fact the integration with Access, includes queries, reports and modules as individual objects that can be versioned]

The MSDN link is here.

I'm not aware of a tool that does this well but I've seen a variety of homegrown solutions. The common thread of these is to minimise the binary data under version control and maximise textual data to leverage the power of conventional scc systems. To do this:

• Treat the workbook like any other application. Seperate logic, config and data.
• Separate code from the workbook.
• Build the UI programmatically.
• Write a build script to reconstruct the workbook.

One thing you could do is to have the following snippet in your Workbook:

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i%, sName$

    With ThisWorkbook.VBProject
        For i% = 1 To .VBComponents.Count
            If .VBComponents(i%).CodeModule.CountOfLines > 0 Then
                sName$ = .VBComponents(i%).CodeModule.Name
                .VBComponents(i%).Export "C:\Code\" & sName$ & ".vba"
            End If
        Next i
    End With
End Sub

I found this snippet on the Internet.

Afterwards, you could use Subversion to maintain version control. For example by using the command line interface of Subversion with the 'shell' command within VBA. That would do it. I'm even thinking of doing this myself :)

If you are looking at an office setting with regular office non technical users than Sharepoint is a viable alternative. You can setup document folders with version control enabled and checkins and checkouts. Makes it freindlier for regular office users.

in response to mattlant's reply - sharepoint will work well as a version control only if the version control feature is turned on in the document library. in addition be aware that any code that calls other files by relative paths wont work. and finally any links to external files will break when a file is saved in sharepoint.

Use any of the standard version control tools like SVN or CVS. Limitations would depend on whats the objective. Apart from a small increase in size of the repository, i did'nt face any issues

I found a very simple solution to this question which meets my needs. I add one line to the bottom of all of my macros which exports a *.txt file with the entire macro code each time it is run. The code:

ActiveWorkbook.VBProject.VBComponents("moduleName").Export"C:\Path\To\Spreadsheet\moduleName.txt"

(Found on Tom's Tutorials, which also covers some setup you may need to get this working.)

Since I'll always run the macro whenever I'm working on the code, I'm guaranteed that git will pick up the changes. The only annoying part is that if I need to checkout an earlier version, I have to manually copy/paste from the *.txt into the spreadsheet.

It depends on what level of integration you want, I've used Subversion/TortoiseSVN which seems fine for simple usage. I have also added in keywords but there seems to be a risk of file corruption. There's an option in Subversion to make the keyword substitutions fixed length and as far as I understand it will work if the fixed length is even but not odd. In any case you don't get any useful sort of diff functionality, I think there are commercial products that will do 'diff'. I did find something that did diff based on converting stuff to plain text and comparing that, but it wasn't very nice.

It should work with most VCS (depending on other criteria you might choose SVN, CVS, Darcs, TFS, etc), however it will actually the complete file (because it is a binary format), meaning that the "what changed" question is not so easy to answer.

You can still rely on log messages if people complete them, but you might also try the new XML based formats from Office 2007 to gain some more visibility (although it would still be hard to weed through the tons of XML, plus AFAIK the XML file is zipped on the disk, so you would need a pre-commit hook to unzip it for text diff to work correctly).