DEV Community

Felix Zumstein
Felix Zumstein

Posted on • Updated on

How to diff Excel VBA code in SourceTree (Git client)

If you rely on Excel for your daily work, your folder most likely contains a lot of different versions of the same file like "myfile_final_v2.xlsx" or "myfile_final_final.xlsx" (you don't want to inadvertently mess up your file, do you?).
In case you know how to use Git version control, using it to track your Excel files is an easy decision to make. This will save you from having to rename your files locally all the time and will give you the option to add a commit message so you know what's going on.
However, it won't take long before you'll miss one Git's core functionality: the "Git diff" feature. For Git, an Excel file is just a binary file and you can't see what changed from one version to the other.
Turns out if you use SourceTree, this is now an easy-to-solve problem for your VBA code!

SourceTree

SourceTree is Atlassian's popular free GUI application for Git, made for users who prefer a desktop application over a the command line. Needless to say, SourceTree doesn't behave differently from any other Git platform or GUI client: it doesn't show you diffs for Excel files out of the box:

Let's see how we can fix this!

Git XL

xltrail is a version control platform for Excel files and works similar like GitHub, BitBucket or GitLab with the difference that xltrail understands the content of Excel files.
While xltrail allows you to integrate with all of these Git providers, it also offers a free and open-source Git extension Git XL that allows you to diff and merge your VBA code locally either on the command line or most recently also through SourceTree.
You can download Git XL here. Note that you do not have to open an account to be able to use it. After clicking your way through the installer, you can activate the extension either globally for all repos (git xl install) or for specific repositories only (git xl install --local while being in a specific repo). For details see the instructions here.
If you now head over to your command line and run a Git diff command, it'll happily show you what changed in your VBA code:

SourceTree with Git XL activated

Once Git XL is installed and activated for your repository, head back to SourceTree where you are now able to see uncommitted diffs in your VBA code without having to change anything in the settings:

Latest comments (20)

Collapse
 
gabriele486 profile image
Gabriele486

Hi! Can I also compare the Excel Workbooks or only the VBA code? Thanks in advance!

Collapse
 
charlesrhall profile image
Charles Hall

Hi, I have installed Git XL and Sourcetree on another computer less than 2 years ago, and at the time, I struggled to get it to work, until all of a sudden it worked.

Now I have a new computer with all the repositories transferred over and am trying it again, but have not had the same luck. I know how it is supposed to work - and it all seems ok, but when I try to compare two versions on the new computer, it does not realise the files are Excel models and so I get the binary file message.

Can you give me some help debugging my install - things to check to make sure I installed correctly. Your instructions above seem simple enough, but something is missing. Is there another source of documentation I could read?

Thanks in advance

Collapse
 
asklim profile image
Andrei Klim • Edited

I am a newbie when it comes to git and sourcetree. Sorry for my English. I'm non-english language. And and so there are problems with displaying the code.
p.s.
preview mode don't show special characters in output!!!.
and I can't see uploaded image.

$ git diff
.Notes = aPlace.Notes

  • .Name = CStr(.place) & "." & Chr(Asc("A") + .Line - 1)
  • .name = CStr(.place) & "." & Chr(Asc("A") + .Line - 1) .FullName = " " & CStr(.Sector) & " " & Format(.Line, "0#") _ & " " & Format(.place, "0#") End With

or

  • '///////////////////////////////////
  • '/// row 0 of dataset
  • ''
  • '/// Col1 : GID
Collapse
 
robs23 profile image
Robert Roszak

It's helpfull, but I was kind of hoping to get my code up to github to be able to browse it there. It doesn't work like that, what is pushed to remote is binary file. Probably to achive what I want, it would have to treat vba modules as separate files and work only on them. Is there any solution to this?

Collapse
 
fzumstein profile image
Felix Zumstein

Have a look at xltrail.com/ which integrates with GitHub. The issue with your suggestion is always on getting the code back into Excel. It's easy to get out of sync.

Collapse
 
lrn2fly profile image
lrn2fly • Edited

Hi, @fzumstein , a life-saving article. But I can't make SourceTree v3.2.6 work with that despite digging all over the forums. Console works. I'll provide all details from SourceTree and please, if you see how I can make this real for me, pls drop a message here, please...

SourceTree log for a command when I click on xlsm file is showing that GUI is calling git diff in some strange way and still treats it as a binary file:
{
"Command": "git -c diff.mnemonicprefix=false -c core.quotepath=false --no-optional-locks diff --unified=3 -M --no-color -- \"Test_GitXL/Orica Contract Variation.xlsm\"", /* apparently not git diff here, I guess it is a root cause?*/
"Status": "Finished",
"CallerContext": "GetFileDiffAgainstBaseProcess",
"Output": "diff --git a/Test_GitXL/Variation.xlsm b/Test_GitXL/Variation.xlsm\nindex 0a51..1fec 100644\nBinary files a/Test_GitXL/Variation.xlsm and b/Test_GitXL/Variation.xlsm differ\n"
},

Please help me if possible, thanks!

Andrew D

Collapse
 
fzumstein profile image
Felix Zumstein

You'll need SourceTree >= 3.1.3, did you check that?

Collapse
 
lrn2fly profile image
lrn2fly

Actually I believe there is still a problem in some cases: there is one command (from the logs) that is not calling Git XL and the other command is calling it and produces a proper diff output.

The command #1 that works - as it is in my SourceTree log file:
git -c diff.mnemonicprefix=false -c core.quotepath=false --no-optional-locks diff-tree --oneline --unified=3 --root -w -M -C --ext-diff 6b4...3b7 -- \"Test_GitXL/Variation.xlsm\"

This command #2 doesn't work with Git XL:
git -c diff.mnemonicprefix=false -c core.quotepath=false --no-optional-locks diff --unified=3 -M --no-color -- \"Test_GitXL/Orica Contract Variation.xlsm\"

"Output" of second command is : "diff --git a/Test_GitXL/Variation.xlsm b/Test_GitXL/Variation.xlsm\nindex 0a51..1fec 100644\nBinary files a/Test_GitXL/Variation.xlsm and b/Test_GitXL/Variation.xlsm differ\n"

Output from command #1 is meaningfull diff results as it expected to be...

Possibly I'm missing something in my Git knowledge, can't see why #1 works while #2 doesn't.... difference is that #1 has diff-tree and ext-diff... but what does that mean? "Use external diff tool"?

Andrew D

Collapse
 
lrn2fly profile image
lrn2fly

That is magic, but after you answered it started to work!

Thread Thread
 
fzumstein profile image
Felix Zumstein

I didn't know about my spiritual capabilities but glad it's working now!

Collapse
 
charlesrhall profile image
Charles Hall

I am a newbie when it comes to git and sourcetree, but with patience I was able to get my first vba diff to work

Then I discovered that the line numbers I use for error handling makes the diff useless. Every line of my vba code has a prefixed line number so when an error happens I get an error message than indicates which line of code errored out.

However, every so often I need to regenerate the line numbers if I have added more than a few lines of code to a module - however the git diff shows all the lines has having changed, not just the lines that I added.

Any idea how I could limit the diffs to only show changes to the code, not the prefixed line numbers?

Thanks in advance

Collapse
 
fzumstein profile image
Felix Zumstein

How are you prefixing line numbers?

Collapse
 
charlesrhall profile image
Charles Hall

It is a capability/feature in the VBA/VB development environments - one can add line numbers to each line of code, and then add the line number to any error message so that one knows which line of code was being executed when the error occurred.

Thread Thread
 
charlesrhall profile image
Charles Hall

It just occurred to me that the above is not accurate - the prefixing line numbers is part of a developer's addin called MZ-TOOLS - which I have used forever (hence why I thought it was part of the IDE)

Thread Thread
 
Sloan, the sloth mascot
Comment deleted
 
charlesrhall profile image
Charles Hall

I don't think I have explained myself - although the addin creates the numbers, the standard VBA interpreter uses them when an error occurs - without embedded line numbers, the error handling can not tell where the error occurred in a subroutine or function. So they are critical to providing support after the application is released.

Thread Thread
 
fzumstein profile image
Felix Zumstein

Hey just understood now what's going on. Yes, MZ-Tools adds the line numbers as part of the code, so there's nothing really that we can do about that. Except ask Microsoft to add line numbers to the VBA editor ;)

Collapse
 
joshmayberry profile image
Josh

This looks amazing. I tried to follow the instructions above, but have run into some issues.

I have installed xltrial and it works in the command window as intended.
Here is a screenshot of this:
thepracticaldev.s3.amazonaws.com/i...

Unfortunately, it is not showing up in the GUI window as shown at the end of this article.
Here is a screenshot of what I have:
thepracticaldev.s3.amazonaws.com/i...

I am running version SourceTree version 3.1.2.
Is there something else I am supposed to to in order to get this to work correctly?

Collapse
 
fzumstein profile image
Felix Zumstein • Edited

Hi Josh,

it's possible you run into this issue: github.com/xlwings/git-xl/issues/33

Have you tried with an uncommitted change?

Collapse
 
joshmayberry profile image
Josh

Wow, super fast reply. :)

It does the same thing on an uncommitted change.
thepracticaldev.s3.amazonaws.com/i...

I also restarted my computer.
I only have one version of git installed on my computer; the internal one used by SourceTree. So, it's likely not an issue of enabling it on one version of git and not another.