Best way to do Version Control for MS Excel

What version control systems have you used with MS Excel (2003/2007)? What would you recommend and Why? What limitations have you found with your top rated version control system?

To put this in perspective, here are a couple of use cases:

1. version control for VBA modules
2. more than one person is working on a Excel spreadsheet and they may be making changes to the same worksheet, which they want to merge and integrate. This worksheet may have formulae, data, charts etc
3. the users are not too technical and the fewer version control systems used the better
4. Space constraint is a consideration. Ideally only incremental changes are saved rather than the entire Excel spreadsheet.

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!

http://tortoisesvn.tigris.org/">TortoiseSVN</a> 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 http://tortoisesvn.tigris.org/ds/viewMessage.do?dsForumId=4061&dsMessageId=878721">here</a>;.

You may find this useful even if NOT using TortoiseSVN.

Let me summarise what you would like to version control and why:

1. What:

• Code (VBA)
• Spreadsheets (Formulae)
• Spreadsheets (Values)
• Charts
• ...

2. Why:

• Audit log
• Collaboration
• Version comparison ("diffing")
• Merging

As others have posted here, there are a couple of solutions on top of existing version control systems such as:

• Git
• Mercurial
• Subversion
• Bazaar

If your only concern is the VBA code in your workbooks, then the approach Demosthenex above proposes or VbaGit (https://github.com/brucemcpherson/VbaGit) work very well working and are relatively simple to implement. The advantages are that you can rely on well proven version control systems and chose one according to your needs (have a look at https://help.github.com/articles/what-are-the-differences-between-svn-and-git/ for a brief comparison between Git and Subversion).

If you not only worry about code but also about the data in your sheets ("hardcoded" values and formula results), you can use a similar strategy for that: Serialise the contents of your sheets into some text format (via Range.Value) and use an existing version control system. Here's a very good blog post about this: https://wiki.ucl.ac.uk/display/~ucftpw2/2013/10/18/Using+git+for+version+control+of+spreadsheet+models+-+part+1+of+3

However, spreadsheet comparison is a non-trivial algorithmic problem. There are a few tools around, such as Microsoft's Spreadsheet Compare (https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986), Exceldiff (http://exceldiff.arstdesign.com/) and DiffEngineX (https://www.florencesoft.com/compare-excel-workbooks-differences.html). But it's another challenge to integrate these comparison with a version control system like Git.

Finally, you have to settle on a workflow that suits your needs. For a simple, tailored Git for Excel workflow, have a look at https://www.xltrail.com/blog/git-workflow-for-excel.

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.

Working upon @Demosthenex work, @Tmdean and @Jon Crowell invaluable comments! (+1 them)

I save module files in git\ dir beside workbook location. Change that to your liking.

This will NOT track changes to Workbook code. So it's up to you to synchronize them.

Sub SaveCodeModules()

'This code Exports all VBA modules
Dim i As Integer, name As String

With ThisWorkbook.VBProject
    For i = .VBComponents.count To 1 Step -1
        If .VBComponents(i).Type <> vbext_ct_Document Then
            If .VBComponents(i).CodeModule.CountOfLines > 0 Then
                name = .VBComponents(i).CodeModule.name
                .VBComponents(i).Export Application.ThisWorkbook.Path & _
                                            "\git\" & name & ".vba"
            End If
        End If
    Next i
End With

End Sub

Sub ImportCodeModules()
Dim i As Integer
Dim ModuleName As String

With ThisWorkbook.VBProject
    For i = .VBComponents.count To 1 Step -1

        ModuleName = .VBComponents(i).CodeModule.name

        If ModuleName <> "VersionControl" Then
            If .VBComponents(i).Type <> vbext_ct_Document Then
                .VBComponents.Remove .VBComponents(ModuleName)
                .VBComponents.Import Application.ThisWorkbook.Path & _
                                         "\git\" & ModuleName & ".vba"
            End If
        End If
    Next i
End With

End Sub

And then in Workbook module:

Private Sub Workbook_Open()

    ImportCodeModules

End Sub

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

    SaveCodeModules

End Sub

Taking @Demosthenex 's answer a step further, if you'd like to also keep track of the code in your Microsoft Excel Objects and UserForms you have to get a little bit tricky.

First I altered my SaveCodeModules() function to account for the different types of code I plan to export:

Sub SaveCodeModules(dir As String)

'This code Exports all VBA modules
Dim moduleName As String
Dim vbaType As Integer

With ThisWorkbook.VBProject
    For i = 1 To .VBComponents.count
        If .VBComponents(i).CodeModule.CountOfLines > 0 Then
            moduleName = .VBComponents(i).CodeModule.Name
            vbaType = .VBComponents(i).Type
        
            If vbaType = 1 Then
                .VBComponents(i).Export dir & moduleName & ".vba"
            ElseIf vbaType = 3 Then
                .VBComponents(i).Export dir & moduleName & ".frm"
            ElseIf vbaType = 100 Then
                .VBComponents(i).Export dir & moduleName & ".cls"
            End If
        
        End If
    Next i
End With

End Sub

The UserForms can be exported and imported just like VBA code. The only difference is that two files will be created when a form is exported (you'll get a .frm and a .frx file for each UserForm). One of these holds the software you've written and the other is a binary file which (I'm pretty sure) defines the layout of the form.

Microsoft Excel Objects (MEOs) (meaning Sheet1, Sheet2, ThisWorkbook etc) can be exported as a .cls file. However, when you want to get this code back into your workbook, if you attempt to import it the same way you would a VBA module, you'll get an error if that sheet already exists in the workbook.

To get around this issue, I decided not to try to import the .cls file into Excel, but to read the .cls file into excel as a string instead, then paste this string into the empty MEO. Here is my ImportCodeModules:

Sub ImportCodeModules(dir As String)

Dim modList(0 To 0) As String
Dim vbaType As Integer

' delete all forms, modules, and code in MEOs
With ThisWorkbook.VBProject
    For Each comp In .VBComponents
    
        moduleName = comp.CodeModule.Name
        
        vbaType = .VBComponents(moduleName).Type
        
        If moduleName <> "DevTools" Then
            If vbaType = 1 Or _
                vbaType = 3 Then
                
                .VBComponents.Remove .VBComponents(moduleName)
                
            ElseIf vbaType = 100 Then
            
                ' we can't simply delete these objects, so instead we empty them
                .VBComponents(moduleName).CodeModule.DeleteLines 1, .VBComponents(moduleName).CodeModule.CountOfLines
            
            End If
        End If
    Next comp
End With

' make a list of files in the target directory
Set FSO = CreateObject("Scripting.FileSystemObject")
Set dirContents = FSO.getfolder(dir) ' figure out what is in the directory we're importing

' import modules, forms, and MEO code back into workbook
With ThisWorkbook.VBProject
    For Each moduleName In dirContents.Files

        ' I don't want to import the module this script is in
        If moduleName.Name <> "DevTools.vba" Then
        
            ' if the current code is a module or form
            If Right(moduleName.Name, 4) = ".vba" Or _
                Right(moduleName.Name, 4) = ".frm" Then
                
                ' just import it normally
                .VBComponents.Import dir & moduleName.Name
                
            ' if the current code is a microsoft excel object
            ElseIf Right(moduleName.Name, 4) = ".cls" Then
                Dim count As Integer
                Dim fullmoduleString As String
                Open moduleName.Path For Input As #1
                
                count = 0              ' count which line we're on
                fullmoduleString = ""  ' build the string we want to put into the MEO
                Do Until EOF(1)        ' loop through all the lines in the file
                    
                    Line Input #1, moduleString  ' the current line is moduleString
                    If count > 8 Then            ' skip the junk at the top of the file
                        
                        ' append the current line `to the string we'll insert into the MEO
                        fullmoduleString = fullmoduleString & moduleString & vbNewLine
                        
                    End If
                    count = count + 1
                Loop
                
                ' insert the lines into the MEO
                .VBComponents(Replace(moduleName.Name, ".cls", "")).CodeModule.InsertLines .VBComponents(Replace(moduleName.Name, ".cls", "")).CodeModule.CountOfLines + 1, fullmoduleString
                        
                Close #1
                
            End If
        End If
        
    Next moduleName
End With

End Sub

In case you're confused by the dir input to both of these functions, that is just your code repository! So, you'd call these functions like:

SaveCodeModules "C:\...\YourDirectory\Project\source\"
ImportCodeModules "C:\...\YourDirectory\Project\source\"

I use git, and today I ported [this (git-xlsx-textconv)][1] to Python, since my project is based on Python code, and it interacts with Excel files. This works for at least .xlsx files, but I think it will work for .xls too. [Here's][2] the github link. I wrote two versions, one with each row on its own line, and another where each cell is on its own line (the latter was written because git diff doesn't like to wrap long lines by default, at least here on Windows).

This is my .gitconfig file (this allows the differ script to reside in my project's repo):

[diff "xlsx"]
    binary = true
    textconv = python `git rev-parse --show-toplevel`/src/util/git-xlsx-textconv.py

if you want the script to be available for many different repos, then use something like this:

[diff "xlsx"]
    binary = true
    textconv = python C:/Python27/Scripts/git-xlsx-textconv.py

my .gitattributes file:

*.xlsx diff=xlsx

[1]: https://github.com/tokuhirom/git-xlsx-textconv "this (git-xlsx-textconv)" [2]: https://gist.github.com/nmz787/c43bc109db915064f188 "Here's"

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

I would like to recommend a great open-source tool called Rubberduck that has version control of VBA code built in. Try it!

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 have been looking into this too. It apears that the latest Team Foundation Server 2010 may have an Excel Add-In.

Here is a clue:

http://team-foundation-server.blogspot.com/2009/07/tf84037-there-was-problem-initializing.html

After searching for ages and trying out many different tools, I've found my answer to the vba version control problem here: https://stackoverflow.com/a/25984759/2780179

It's a simple excel addin for which the code can be found here

There are no duplicate modules after importing. It exports your code automatically, as soon as you save your workbook, without modifying any existing workbooks. It comes together with a vba code formatter.

Actually there only a handful of solutions to track and compare changes in macro code - most of those were named here already. I have been browsing the web and came across this new tool worth mentioning:

XLTools Version Control for VBA macros

• version control for Excel sheets and VBA modules
• preview and diff changes before committing a version
• great for collaborative work of several users on the same file (track who changed what/when/comments)
• compare versions and highlight changes in code line-by-line
• suitable for users who are not tech-savvy, or Excel-savvy for that matter
• version history is stored in Git-repository on your own PC - any version can be easily recovered

VBA code versions side by side, changes are visualized

There is also a program called Beyond Compare that has a quite nice Excel file compare. I found a screenshot in chinese that briefly shows this:

Original image source

There is a 30 day trial on their page

You might have tried using Microsoft's Excel XML in zip container (.xlsx and .xslm) for version control and found the vba was stored in vbaProject.bin (which is useless for version control).

The solution is simple.

1. Open the excel file with LibreOffice Calc
2. In LibreOffice Calc
   1. File
   2. Save as
   3. Save as type: ODF Spreadsheet (.ods)

3. Close LibreOffice Calc
4. rename the new file's file extension from .ods to .zip
5. create a folder for the spreadsheet in a GIT maintained area
6. extract the zip into it's GIT folder
7. commit to GIT

When you repeat this with the next version of the spreadsheet you'll have to make sure you make the folder's files exactly match those in the zip container (and don't leave any deleted files behind).

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

I wrote a revision controlled spreadsheet using VBA. It is geared more for engineering reports where you have multiple people working on a Bill Of Material or Schedule and then at some point in time you want to create a snapshot revision that shows adds, del and updates from the previous rev.

Note: it is a macro enabled workbook that you need to sign in to download from my site (you can use OpenID)

All the code is unlocked.

Rev Controlled Spreadsheet