Last updated on August 26, 2021
How to use Git hooks to version-control your Excel VBA code
Posted by Björn Stiel - Comments
Exporting your VBA modules into stand-alone .bas
/.cls
/.frm
files is a simple and effective way to make Git aware of code inside your Excel workbooks. The goal is to end up with a separate files for each of your VBA modules so that you can benefit from the Git functionalities.
One common way to achieve this is via Excel’s Workbook.AfterSave
(or Workbook.BeforeSave
) event. Every time you hit “save” in Excel, some VBA code is executed and saves a copy of your workbook’s VBA content to the filesystem. You end up with your VBA files alongside your workbook which can then be pushed to your Git server.
Git hooks
There are a few downsides to using Excel events. You are dependent on Excel, so if you copy your workbook from an email or another folder into your Git repository folder, your VBA export function will not run. Distributing the export function (either via copy and paste or as an Addin) and ensuring it runs reliably is another pain point.
An alternative approach is to exploit Git’s built-in hooks. Hooks are programs you can place in a hooks directory to trigger actions at certain points in Git’s execution. You can find a list of available hooks in https://git-scm.com/docs/githooks. We will use the pre-commit hook to do the following when you call git commit
:
- extract the VBA modules from your workbook and write them as
.bas
or.cls
or.frm
files to your repository - add these VBA files to your commit via
git add -- ./src.vba
- finally execute the
git commit
command
A Python script to extract your VBA code
We use the Python package oletools to extract the VBA code from the Excel file (in fact, this works for any MS Office file). Thus, we no longer have to resort to Excel itself to get hold of the VBA code.
Our script requires Python 3 and oletools. oletools
can be installed via pip, Python’s package manager: pip install -U oletools
.
Create a file named pre-commit.py
in the .git/hooks
folder inside your repository and add the following code:
NOTE: The following code has been updated multiple times according to feedback, last time: 26-Aug-2021
import os
import shutil
from oletools.olevba3 import VBA_Parser
EXCEL_FILE_EXTENSIONS = ('xlsb', 'xls', 'xlsm', 'xla', 'xlt', 'xlam',)
KEEP_NAME = False # Set this to True if you would like to keep "Attribute VB_Name"
def parse(workbook_path):
vba_path = 'src.vba'
vba_parser = VBA_Parser(workbook_path)
vba_modules = vba_parser.extract_all_macros() if vba_parser.detect_vba_macros() else []
for _, _, filename, content in vba_modules:
lines = []
if '\r\n' in content:
lines = content.split('\r\n')
else:
lines = content.split('\n')
if lines:
content = []
for line in lines:
if line.startswith('Attribute') and 'VB_' in line:
if 'VB_Name' in line and KEEP_NAME:
content.append(line)
else:
content.append(line)
if content and content[-1] == '':
content.pop(len(content)-1)
non_empty_lines_of_code = len([c for c in content if c])
if non_empty_lines_of_code > 0:
if not os.path.exists(os.path.join(vba_path)):
os.makedirs(vba_path)
with open(os.path.join(vba_path, filename), 'w', encoding='utf-8') as f:
f.write('\n'.join(content))
if __name__ == '__main__':
for root, dirs, files in os.walk('.'):
for f in dirs:
if f.endswith('.vba'):
shutil.rmtree(os.path.join(root, f))
for f in files:
if f.endswith(EXCEL_FILE_EXTENSIONS):
parse(os.path.join(root, f))
This Python script finds any Excel files that can contain VBA and dumps the content into a subfolder named src.vba
.
Note that this script works unchanged if you only use 1 workbook. If you have more than one Workbook in one repository, you’ll need to replace vba_path = 'src.vba'
with vba_path = workbook_path + '.vba'
and edit your pre-commit
file below accordingly to have a git add -- ./<workbookname>.vba
line for each of your workbooks.
Setting up the Git pre-commit hook
The only missing bit is to set up the Git pre-commit
now. Create the file pre-commit
in .git/hooks
inside your repository and add the following code:
#!/bin/sh
python .git/hooks/pre-commit.py
git add -- ./src.vba
Note: On Mac you need to run chmod +x .git/hooks/pre-commit
, otherwise it will not trigger the hook.
From now on, when you execute git commit
, the hook extracts the VBA code and adds it to your commit. You automatically end up with the .bas
files without having to rely on Excel.
Conclusion
In order to automagically export your workbook’s VBA modules into stand-alone text files
on every git commit
, you need:
- Python with
oletools
installed and - the files
pre-commit
andpre-commit.py
in the.git/hooks
directory
With that in place, any git commit
automatically takes care of dumping your workbooks
VBA content as text files to your filesystem. Which is something that Git understands well.
Before you go: a zero-setup, open-source alternative
If you are less of a DIY person, I recommend checking out our free, open-source Git extension Git XL. Instead of having to rely on a workaround, xltrail client is a Git extension that integrates
directly with Git and makes git diff
work with Excel workbook files. It also supports git merge
for those instances where your colleague works on the same workbook and you need to merge their changes in. Plus, xltrail
client goes a step beyond the VBA-only approach and also understands sheets. For installation instructions, docs and an example video, check out the docs and the GitHub repository.