SteadyLatch

Guides · Version control

Excel VBA Version Control with Git: Export and Verify

Published · Tested with Microsoft 365 Excel 16.0.20430.20092 (64-bit), PowerShell 7.6.6, Git 2.52.0, Windows 11 Pro

Git can store a .xlsm, but to git it is a ZIP file: a one-character fix in a macro shows up as "binary files differ". Export the VBA to text and you get real diffs, reviews and history. This guide does it with two PowerShell scripts and adds a SHA256 manifest, so CI can tell when the exported code was edited by hand.

What git can track

The VBA editor exports each component of a project to its own file:

.bas
Standard modules.
.cls
Class modules, and the code behind ThisWorkbook and each sheet.
.frm + .frx
UserForms: the code and layout as text, the binary resources in the .frx. Git stores the .frx but cannot diff it.

Cell contents, formulas and formatting are not part of the export. To track what the workbook produces, add a golden file of its output: see Regression Test an Excel VBA Macro with a Golden File. And keep the delivered .xlsm as well: the text export is the source you review, not a backup you can open.

One setting to know about

Reading a VBA project from code requires Trust access to the VBA project object model (File › Options › Trust Center › Trust Center Settings › Macro Settings). It lets any program that automates Excel read and change VBA code, so it is a security decision, not a convenience switch: turn it on deliberately, on your own development machine, and consider turning it off again when you are done. The script below never changes it; when it is off, the script stops with a message saying so.

Step 1: export the modules

The example is the report.xlsm workbook from Run an Excel Macro from PowerShell with a Timeout, with one standard module, Report.

# export-vba.ps1: exports every VBA module of a workbook to text, plus a SHA256 manifest.
# Opens the workbook read-only with macros disabled. Needs "Trust access to the VBA
# project object model" (File > Options > Trust Center), which this script never changes.
param(
    [Parameter(Mandatory)] [string] $Workbook,
    [Parameter(Mandatory)] [string] $OutputDir
)
$ErrorActionPreference = 'Stop'
$extensions = @{ 1 = '.bas'; 2 = '.cls'; 3 = '.frm'; 100 = '.cls' }  # module, class, form, sheet/workbook

Add-Type -Namespace Win32Tools -Name User32 -MemberDefinition @'
[DllImport("user32.dll")]
public static extern uint GetWindowThreadProcessId(System.IntPtr hWnd, out uint processId);
'@

# Export next to the output folder, then swap it in (same drive, so the move is one step).
$target = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($OutputDir)
$staging = "$target.new-" + [guid]::NewGuid().ToString('N')
$excel = New-Object -ComObject Excel.Application
$excelPid = [uint32] 0
[void] [Win32Tools.User32]::GetWindowThreadProcessId([IntPtr] $excel.Hwnd, [ref] $excelPid)
$exported = $false
try {
    New-Item -ItemType Directory -Path $staging | Out-Null
    $excel.DisplayAlerts = $false
    $excel.AutomationSecurity = 3   # msoAutomationSecurityForceDisable
    $book = $excel.Workbooks.Open((Resolve-Path -LiteralPath $Workbook).Path, 0, $true)
    try { $components = @($book.VBProject.VBComponents) }
    catch { throw 'Cannot read the VBA project: "Trust access to the VBA project object model" is off.' }
    foreach ($component in $components) {
        $empty = $component.Type -eq 100 -and $component.CodeModule.CountOfLines -eq 0
        if ($empty) { continue }   # a sheet module with no code: nothing to track
        $component.Export((Join-Path $staging ($component.Name + $extensions[[int] $component.Type])))
    }
    $book.Close($false)
    $exported = $true
}
finally {
    # A failed export leaves nothing behind next to the output folder.
    if (-not $exported) { Remove-Item -LiteralPath $staging -Recurse -Force -ErrorAction SilentlyContinue }
    $excel.Quit()
    [void] [Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
    $process = Get-Process -Id $excelPid -ErrorAction SilentlyContinue
    if ($process -and -not $process.WaitForExit(5000)) { $process.Kill(); [void] $process.WaitForExit(5000) }
}

# Manifest: one SHA256 per exported file, sorted, with no dates, so the same code gives the same file.
$files = @(Get-ChildItem -LiteralPath $staging -File | Sort-Object Name)
$manifest = [ordered]@{
    workbook = Split-Path -Leaf $Workbook
    files    = @($files | ForEach-Object {
            [ordered]@{ name = $_.Name; sha256 = (Get-FileHash -LiteralPath $_.FullName -Algorithm SHA256).Hash.ToLowerInvariant() }
        })
}
$manifest | ConvertTo-Json -Depth 4 | Set-Content -LiteralPath (Join-Path $staging 'vba-manifest.json') -Encoding utf8

# Replace the output folder as a whole: a module deleted from the workbook disappears from it too.
if (Test-Path -LiteralPath $target) { Remove-Item -LiteralPath $target -Recurse -Force }
Move-Item -LiteralPath $staging -Destination $target
Write-Output "Exported $($files.Count) file(s) to $OutputDir"
PS> ./export-vba.ps1 -Workbook report.xlsm -OutputDir vba
Exported 1 file(s) to vba

Step 2: keep the bytes stable

The VBA editor writes files with Windows line endings (CRLF), and the manifest hashes those exact bytes. If git converted them to LF on a Linux CI runner, every hash would fail. Add a .gitattributes at the root of the repository:

*.bas text eol=crlf
*.cls text eol=crlf
*.frm text eol=crlf
*.frx binary

With eol=crlf, git stores the files with LF and always checks them out with CRLF, whatever the platform's settings. We checked it by deleting Report.bas and checking it out again with core.eol=lf: git ls-files --eol reports i/lf w/crlf, and the manifest check passes.

One more thing about the bytes: the exports use the Windows code page (ANSI), not UTF-8. Accented characters in strings or comments may look wrong in a web diff. Leave the files as they are: re-saved as UTF-8, those characters would come back garbled when the file is imported into Excel again.

Step 3: commit and review a change

Commit the folder next to the workbook. Next time someone changes the macro, export again. For the workbook, git can only say that the bytes changed; the export shows the change itself. Here, a loop shortened by one row:

PS> git diff --stat
 report.xlsm           | Bin 16437 -> 16453 bytes
 vba/Report.bas        |   2 +-
 vba/vba-manifest.json |   2 +-
 3 files changed, 2 insertions(+), 2 deletions(-)
PS> git diff -U1 -- vba/Report.bas
@@ -16,3 +16,3 @@ Public Sub BuildReport()
     lastRow = src.Cells(src.Rows.Count, 1).End(xlUp).Row
-    For r = 2 To lastRow
+    For r = 2 To lastRow - 1
         store = CStr(src.Cells(r, 2).Value)

That one line dropped the last row of every report. In a binary .xlsm diff it would have been invisible; in a pull request it is the first thing a reviewer sees.

Step 4: check the export against its manifest

The export is the record of what was in the workbook. If someone edits a .bas file in the repository instead of in Excel, the text and the workbook no longer match. This check catches it, without Excel:

# test-vba-manifest.ps1: checks an exported VBA folder against its manifest. No Excel needed.
# Exit codes: 0 every file matches, 1 a file changed, is missing or is not in the manifest, 2 no manifest.
param([Parameter(Mandatory)] [string] $VbaDir)
$ErrorActionPreference = 'Stop'

$manifestPath = Join-Path $VbaDir 'vba-manifest.json'
if (-not (Test-Path -LiteralPath $manifestPath -PathType Leaf)) { Write-Output "ERROR: no vba-manifest.json in $VbaDir"; exit 2 }
$manifest = Get-Content -LiteralPath $manifestPath -Raw | ConvertFrom-Json

$problems = [Collections.Generic.List[string]]::new()
foreach ($entry in $manifest.files) {
    $path = Join-Path $VbaDir $entry.name
    if (-not (Test-Path -LiteralPath $path -PathType Leaf)) { $problems.Add("$($entry.name): missing"); continue }
    $hash = (Get-FileHash -LiteralPath $path -Algorithm SHA256).Hash.ToLowerInvariant()
    if ($hash -ne $entry.sha256) { $problems.Add("$($entry.name): changed since the export") }
}
$listed = @($manifest.files.name) + 'vba-manifest.json'
foreach ($file in Get-ChildItem -LiteralPath $VbaDir -File | Where-Object Name -notin $listed) {
    $problems.Add("$($file.Name): not in the manifest")
}

if ($problems.Count -eq 0) { Write-Output "OK: $(@($manifest.files).Count) file(s) match the manifest"; exit 0 }
Write-Output "FAIL: $($problems.Count) problem(s)"
$problems | ForEach-Object { Write-Output "  - $_" }
exit 1
PS> ./test-vba-manifest.ps1 -VbaDir vba
OK: 1 file(s) match the manifest
PS> Add-Content vba/Report.bas "' quick fix"
PS> ./test-vba-manifest.ps1 -VbaDir vba
FAIL: 1 problem(s)
  - Report.bas: changed since the export

A missing file and a file that is not in the manifest fail the same way. In GitHub Actions:

name: vba-check
on: pull_request
permissions:
  contents: read
jobs:
  manifest:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7.0.1
      - name: VBA export matches its manifest
        shell: pwsh
        run: ./tools/test-vba-manifest.ps1 -VbaDir vba

We tested the scripts on Windows; the manifest check uses only PowerShell 7 and .NET, but run it once on your CI image before relying on it.

What this does not do

Just want the scripts? The Scripts Pack · €12 has the code of this guide and the other guides ready to run, with examples and tests. The code on this page stays free.

The code on this page was written for this guide and tested on the versions above. The workbook and its data are invented.