Cat On A Spreadsheet

Putting Your VBA Add-In Under Version Control (Git + Automatic Module Export)

In the previous article, we created a structured Excel add-in (.xlam) that centralises our automation code. The add-in gives us a single place to maintain reusable macros and distribute them across workbooks. However, there is still a major limitation. Excel files are binary containers. When a .xlam file is committed to a version control system like Git, the repository cannot meaningfully track changes inside the VBA code. Git only sees that the binary file changed; it cannot show which procedures were modified or why. For developers who want proper version history, meaningful diffs, and safe experimentation, this is a problem.

The solution is to separate source code from the compiled add-in. Instead of relying on the .xlam file as the source of truth, we export every VBA module to text files and store those files in Git. The add-in then becomes a build artifact rather than the primary code repository.

In this article we will build a simple workflow that allows a single developer to maintain a VBA project using proper version control.

Why Binary Files Do Not Work with Git

Git tracks changes by comparing text files line by line. This works perfectly for languages like Python, JavaScript, or C#, where the source code exists as plain text. Excel workbooks and add-ins, on the other hand, store VBA inside a binary container. Even the smallest change in a module alters the entire file, making it impossible for Git to display meaningful differences between versions. The result is that version history becomes opaque. You know a change happened, but you cannot easily see what changed.

Exporting modules to text files solves this. Each module becomes a separate .bas, .cls, or .frm file that Git can track normally.

The Basic Repository Structure

A simple folder structure keeps the project organised.

Excel-Automation Addin AutomationTools.xlam Source Modules Classes Forms Builds

The .xlam file remains in the Addin folder and is used for development and testing. The Source folder contains exported code files. These files will be committed to Git and become the canonical source code. The Builds folder can later hold compiled releases of the add-in.

This separation ensures that the repository contains readable source files rather than opaque binaries.

Exporting VBA Modules Automatically

Instead of manually exporting modules through the VBA editor, we can automate the process. The following procedure exports every component of the project into the Source folder.

Create a new module in the add-in called modExport.

  Public Sub ExportModules()
 
      Dim component As Object
 
      Dim exportPath As String
 
      exportPath = ThisWorkbook.Path & "\\..\\Source\\"
 
      For Each component In ThisWorkbook.VBProject.VBComponents
 
          Select Case component.Type
 
              Case 1
 
                  component.Export exportPath & component.Name & ".bas"
 
              Case 2
 
                  component.Export exportPath & component.Name & ".cls"
 
              Case 3
 
                  component.Export exportPath & component.Name & ".frm"
 
          End Select
 
      Next component
 
 End Sub

When this procedure runs, every module in the add-in is exported as a text file into the Source folder. Standard modules become .bas files, class modules become .cls files and userForms become .frm files. These files are exactly what Git needs in order to track changes.

Importing Modules Back Into the Add-In

To restore code from the repository into the add-in, we also create a companion import routine.

Create a new module named modImport.

  Public Sub ImportModules()
 
      Dim fileName As String
 
      Dim importPath As String
 
      importPath = ThisWorkbook.Path & "\\..\\Source\\"
 
      fileName = Dir(importPath & "*.bas")
 
      Do While fileName <> ""
 
          ThisWorkbook.VBProject.VBComponents.Import importPath & fileName
 
          fileName = Dir
 
      Loop
 
 End Sub

This procedure reads exported module files and imports them back into the add-in project.

With both procedures available, the workflow becomes simple:

Edit code inside the .xlam file.
Run ExportModules.
Commit the exported source files to Git.

If the repository is cloned on another machine, the modules can be imported to reconstruct the add-in project.

Enabling Access to the VBA Project Model

Because these procedures interact with the VBA project itself, Excel requires a specific security setting to be enabled.

Navigate to:

File → Options → Trust Center → Trust Center Settings → Macro Settings

Enable:

Trust access to the VBA project object model

Without this setting, VBA cannot programmatically export or import modules.

Treating the Add-In as a Build Artifact

Once this export workflow is established, the .xlam file should be viewed differently. It is no longer the authoritative source of the code - the text modules stored in Git are the real source. The add-in becomes a compiled container that assembles those modules into a runnable Excel component.

This mirrors how most programming environments work. Developers edit source files, and the application itself is generated from them.

Adopting this mindset dramatically improves maintainability and confidence when modifying automation.

The Next Step

At this point, the project has gained two essential capabilities: the automation code lives inside a reusable Excel add-in, and the source code exists as text files that can be version controlled with Git. However, distributing a macro-enabled add-in inside many organisations introduces a new challenge. Excel security settings often block unsigned macros, preventing the add-in from loading.

In the next article we will solve that problem by introducing digital code signing. We will create a certificate, sign the add-in, and configure Excel to trust it, making the automation safe to distribute across multiple machines.