How to use Git hooks to version control Excel VBA Code

Exporting your VBA code

Exporting your VBA modules into stand-alone .bas 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 .bas file 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 repository

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 files to your repository
  • add these VBA files to your commit via git add *.bas
  • 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:

import os
import shutil
from oletools.olevba3 import VBA_Parser


EXCEL_FILE_EXTENSIONS = ('xlsb', 'xls', 'xlsm', 'xla', 'xlt', 'xlam',)


def parse(workbook_path):
    vba_path = workbook_path + '.vba'
    vba_parser = VBA_Parser(workbook_path)
    vba_modules = vba_parser.extract_all_macros() if vba_parser.detect_vba_macros() else []

    for _, _, _, content in vba_modules:
        decoded_content = content.decode('latin-1')
        lines = []
        if '\r\n' in decoded_content:
            lines = decoded_content.split('\r\n')
        else:
            lines = decoded_content.split('\n')
        if lines:
            name = lines[0].replace('Attribute VB_Name = ', '').strip('"')
            content = [line for line in lines[1:] if not (
                line.startswith('Attribute') and 'VB_' in line)]
            if content and content[-1] == '':
                content.pop(len(content)-1)
                lines_of_code = len(content)
                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, name + '.bas'), 'w') 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 <workbookname>.vba.

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 *.bas

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.

VBA files

How to apply this to your workflow

To summarize, you need:

  • Python with oletools installed and
  • the files pre-commit and pre-commit.py in the .git/hooks directory

From now on, any git commit automatically takes care of dumping the VBA content of your workbooks to your filesystem for easy diffing of your VBA code.