May 28, 2018

How to git-merge Excel VBA code

Posted by Björn Stiel Comments

xltrail client is a free, open-source Git command line extension for managing Excel workbook files in Git. The xltrail client makes git diff and git merge work for Excel workbooks file formats. It works directly on the workbook file and does not require Excel.

In this post, you’ll learn how you can make Git branching work with with your Excel Add-ins. You’ll learn step-by-step how to use Git to compare and merge branches and to resolve merge conflicts. We have also recorded a 3-min video which you can watch if you scroll down to the bottom of this post.

Before you start, make sure you have Git and xltrail client version 0.2.0 (or newer) installed. The xltrail client installer and docs are available at https://github.com/ZoomerAnalytics/git-xltrail. To confirm it all works, head over to the command line:

C:\Developer>git xltrail version
git-xltrail/0.2.0 (windows; Python 3.6.5); git 2857222

If it shows git-xltrail/0.2.0 or a newer version, you are all set.

An example Excel Add-in

To hit the ground running, I have prepared a simple example repository, available at: https://github.com/ZoomerAnalytics/xltrail-client-example-addin. Clone the repository:

C:\Developer>git clone https://github.com/ZoomerAnalytics/xltrail-client-example-addin.git
C:\Developer>cd xltrail-client-example-addin

And let’s have a look at what’s going on here:

C:\Developer\xltrail-client-example-addin>git branch -a
* master
  remotes/origin/HEAD -> origin/master
  remotes/origin/dev
  remotes/origin/master

There are two branches on remote (GitHub): master and dev. Let’s inspect the differences between master and dev:

C:\Developer\xltrail-client-example-addin>git checkout dev
Branch dev set up to track remote branch dev from origin.
Switched to a new branch 'dev'

C:\Developer\xltrail-client-example-addin>git diff master..dev
diff --xltrail a/Addin.xlam b/Addin.xlam
--- a/Addin.xlam/VBA/Module/Functions
+++ b/Addin.xlam/VBA/Module/Functions
@@ -1,5 +1,5 @@
 Option Explicit

 Public Function GetVersion() As String
-    GetVersion = "0.1.0"
+    GetVersion = "0.2.0"
 End Function

--- a/Addin.xlam/VBA/Module/Subs
+++ b/Addin.xlam/VBA/Module/Subs
@@ -1,5 +1,5 @@
 Option Explicit

 Public Sub ShowMessageBox()
-    MsgBox "Hello world from Add-in"
+    MsgBox "Hello world from Add-in. Current version " + GetVersion()
 End Sub

Both modules Functions and Subs have been modified in both branches. Let’s assume that dev is your colleague’s feature branch which you know want to merge in master.

It is quite likely that, while your colleague was working on the dev branch, master was also modified (maybe another feature branch was merged into master in the meantime). For example, if your colleague changed the GetVersion result in the Functions VBA module and it was also changed on the master branch, you have a version conflict. Git is not able to tell which of the two options you want to keep so you have to do it manually.

Let’s do the merge (remember we want to merge dev into master so we need to make sure what we are on the master branch before doing anything else):

C:\Developer\xltrail-client-example-addin>git checkout master
C:\Developer\xltrail-client-example-addin>git merge dev
C:\Users\Bjoern\Developer\xltrail-client-example-addin>git merge dev
CONFLICT (VBA content): Merge conflict in Addin.xlam/VBA/Module/Functions
--- a/Addin.xlam/VBA/Module/Subs +++ b/Addin.xlam/VBA/Module/Subs
Auto-merging Addin.xlam
CONFLICT (content): Merge conflict in Addin.xlam
Automatic merge failed; fix conflicts and then commit the result.```

Ouch. So, Git was able to merge the changes in the Subs module automatically but stumbled across a merge conflict in the Functions module.

A git status highlights that we need to sort out this merge conflict before we can move on.

C:\Developer\xltrail-client-example-addin>git status
On branch master
Your branch is up-to-date with 'origin/master'.
You have unmerged paths.
  (fix conflicts and run "git commit")

Unmerged paths:
  (use "git add <file>..." to mark resolution)

        both modified:   Addin.xlam

no changes added to commit (use "git add" and/or "git commit -a")

One way of dealing with it is to simply roll back via git merge --abort to restore the pre-merge state. Or, we sort it out by opening Addin.xlam in Excel and editing the offending module in the VBA editor, which looks like this:

Option Explicit

Public Function GetVersion() As String
<<<<<<< Functions:ours

    GetVersion = "0.1.0"
=======

    GetVersion = "0.2.0"
>>>>>>> Functions:theirs

End Function

In this instance we just need to choose between our change (ours, meaning master) and the incoming change (theirs, meaning the dev branch). When you are ready, save Addin.xlam and tell Git you are done:

C:\Developer\xltrail-client-example-addin>git add .
C:\Developer\xltrail-client-example-addin>git commit -m "Resolved merge conflict"

And that’s all there’s to it, merge conflict resolved!

I hope this is helpful for your Excel/VBA work. Right now (as of version 0.2.0), the xltrail client supports diffing and merging of VBA code only but we are working on supporting sheets as well. If you have any questions or ideas, please comment below!

Screencast

And here’s the 3-min screencast running you through the above example. Feel free to share ;-)



Do you want more free Git Excel tips?
About Us
Free Products
Contact Details

Zoomer Analytics GmbH
Eichbühlstrasse 19
8004 Zurich
Switzerland

info@xltrail.com

© 2018 Zoomer Analytics LLC. All rights reserved.