SteadyLatch

Guides · Testing

Regression Test an Excel VBA Macro with a Golden File

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

A client asks for one change to a report macro. You make it, and the question that matters is not "does my new code work?" but "is everything else still exactly the same?". A golden-file test answers that directly: approve the output once, then compare every new run with it.

The idea in four steps

  1. Fix the input. Keep one test input file (invented data that still covers the awkward cases: an empty cell, the last day of the month, a store with one row) next to the workbook.
  2. Approve an output. Run the macro, check the result carefully once, and save its values as the golden file.
  3. Compare after every change. Run the changed macro on a copy of the workbook with the same input and compare its values with the golden.
  4. Decide. Each difference is a bug or an intended change. Intended changes are approved again, explicitly. The comparison never updates the golden on its own.

This complements unit tests rather than replacing them. Rubberduck tests procedures from inside the VBA editor; a golden file tests the result the client actually receives.

Compare values, as text

The golden is a CSV of cell values, not a copy of the workbook. That choice buys three things:

Values only means formats, charts, pivot tables and formulas are out of scope: if the formula changes but the result does not, this test passes. Dates come out as Excel serial numbers, which compare exactly.

Step 1: export the values

The example uses the report.xlsm workbook and the run-macro.ps1 runner from Run an Excel Macro from PowerShell with a Timeout: the macro sums units and amounts per store on a Report sheet. This script writes one sheet's values to CSV:

# export-values.ps1: writes the values of one sheet to a CSV that git can diff.
# Opens the workbook read-only with macros disabled; values only, no formats or formulas.
param(
    [Parameter(Mandatory)] [string] $Workbook,
    [Parameter(Mandatory)] [string] $Sheet,
    [Parameter(Mandatory)] [string] $OutputCsv
)
$ErrorActionPreference = 'Stop'
$inv = [Globalization.CultureInfo]::InvariantCulture

function ConvertTo-CellText($value) {
    if ($null -eq $value) { return '' }                         # empty cell: empty, never 0
    if ($value -is [double]) { return $value.ToString('R', $inv) }  # numbers and dates (serials)
    if ($value -is [int]) { return "#ERROR($value)" }              # Value2 returns cell errors as Int32
    return [string] $value
}

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

$excel = New-Object -ComObject Excel.Application
$excelPid = [uint32] 0
[void] [Win32Tools.User32]::GetWindowThreadProcessId([IntPtr] $excel.Hwnd, [ref] $excelPid)
try {
    $excel.DisplayAlerts = $false
    $excel.AutomationSecurity = 3   # msoAutomationSecurityForceDisable: no macro runs while we read
    $book = $excel.Workbooks.Open((Resolve-Path -LiteralPath $Workbook).Path, 0, $true)  # read-only
    $values = $book.Worksheets.Item($Sheet).UsedRange.Value2
    $book.Close($false)
}
finally {
    $excel.Quit()
    [void] [Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
    # Quit is a request: if this Excel is still there after 5 s, stop it (only this one).
    $process = Get-Process -Id $excelPid -ErrorAction SilentlyContinue
    if ($process -and -not $process.WaitForExit(5000)) { $process.Kill(); [void] $process.WaitForExit(5000) }
}

if ($values -isnot [array]) { $single = $values; $values = [object[,]]::new(1, 1); $values[0, 0] = $single }  # one used cell
$rows = foreach ($r in $values.GetLowerBound(0)..$values.GetUpperBound(0)) {
    $cells = foreach ($c in $values.GetLowerBound(1)..$values.GetUpperBound(1)) {
        '"' + (ConvertTo-CellText $values[$r, $c]).Replace('"', '""') + '"'
    }
    $cells -join ','
}
$rows | Set-Content -LiteralPath $OutputCsv -Encoding utf8
Write-Output "Exported $($rows.Count) row(s) of '$Sheet' to $OutputCsv"

It opens the workbook read-only with macros disabled (AutomationSecurity = 3, msoAutomationSecurityForceDisable), so reading an output never runs its code. Value2 returns numbers as Double and cell errors as Int32, which is how the script tells them apart.

Step 2: approve the golden

PS> ./run-macro.ps1 -Workbook report.xlsm -Macro Report.BuildReportSafely -OutputPath out.xlsm
OK: Report.BuildReportSafely ran; output saved to out.xlsm
PS> ./export-values.ps1 -Workbook out.xlsm -Sheet Report -OutputCsv golden/report.csv
Exported 4 row(s) of 'Report' to golden/report.csv
PS> Get-Content golden/report.csv
"Store","Units","Amount"
"North","4","53.75"
"South","6","58.09"
"East","5","60"

Check those numbers against the input by hand, once. Then commit the file with a message that says what you checked, for example git commit -m "Approve report golden: totals checked against Input". The commit is the approval record: who approved which values, and when.

Step 3: compare

# compare-values.ps1: compares an output CSV with the approved (golden) CSV.
# Rows are matched by a key column; numbers within a per-column tolerance are equal.
# Exit codes: 0 same, 1 differences found, 2 a file is missing or unreadable.
param(
    [Parameter(Mandatory)] [string] $Golden,
    [Parameter(Mandatory)] [string] $Actual,
    [Parameter(Mandatory)] [string] $Key,
    [hashtable] $Tolerance = @{}
)
$ErrorActionPreference = 'Stop'
$inv = [Globalization.CultureInfo]::InvariantCulture

function Read-Table([string] $Path) {
    if (-not (Test-Path -LiteralPath $Path -PathType Leaf)) { throw "missing file: $Path" }
    $rows = @(Import-Csv -LiteralPath $Path)
    if ($rows.Count -eq 0) { throw "no data rows: $Path" }   # an empty output never passes
    return $rows
}

function Test-SameValue([string] $Column, [string] $Expected, [string] $Found) {
    if ($Expected -eq '' -or $Found -eq '') { return $Expected -eq $Found }   # empty is not zero
    $e = 0.0; $f = 0.0
    if ([double]::TryParse($Expected, 'Float', $inv, [ref] $e) -and [double]::TryParse($Found, 'Float', $inv, [ref] $f)) {
        $allowed = if ($Tolerance.ContainsKey($Column)) { [double] $Tolerance[$Column] } else { 0.0 }
        return [Math]::Abs($f - $e) -le $allowed
    }
    return $Expected -ceq $Found
}

try {
    $goldenRows = Read-Table $Golden
    $actualRows = Read-Table $Actual
}
catch {
    Write-Output "ERROR: $($_.Exception.Message)"
    exit 2
}

$differences = [Collections.Generic.List[string]]::new()
$goldenColumns = $goldenRows[0].PSObject.Properties.Name
$actualColumns = $actualRows[0].PSObject.Properties.Name
foreach ($column in $goldenColumns | Where-Object { $_ -notin $actualColumns }) { $differences.Add("column '$column' is missing") }
foreach ($column in $actualColumns | Where-Object { $_ -notin $goldenColumns }) { $differences.Add("column '$column' is new") }
if ($Key -notin $goldenColumns) { Write-Output "ERROR: key column '$Key' is not in $Golden"; exit 2 }
# Rows are matched by key only when every row has exactly one: a repeated key would hide a row.
$canMatch = $Key -in $actualColumns
if (-not $canMatch) { $differences.Add("key column '$Key' is missing: rows cannot be matched") }
foreach ($table in @{ Name = 'golden'; Rows = $goldenRows }, @{ Name = 'output'; Rows = $actualRows }) {
    if ($Key -notin $table.Rows[0].PSObject.Properties.Name) { continue }
    foreach ($group in $table.Rows | Group-Object -Property $Key | Where-Object Count -gt 1) {
        $differences.Add("row $Key=$($group.Name) appears $($group.Count) times in the $($table.Name)")
        $canMatch = $false
    }
}

if ($canMatch) {
    $actualByKey = @{}
    foreach ($row in $actualRows) { $actualByKey[$row.$Key] = $row }
    foreach ($expected in $goldenRows) {
        $id = $expected.$Key
        $found = $actualByKey[$id]
        if (-not $found) { $differences.Add("row $Key=$id is missing"); continue }
        $actualByKey.Remove($id)
        foreach ($column in $goldenColumns | Where-Object { $_ -in $actualColumns -and $_ -ne $Key }) {
            if (-not (Test-SameValue $column $expected.$column $found.$column)) {
                $differences.Add("row $Key=$id, column '$column': expected '$($expected.$column)', found '$($found.$column)'")
            }
        }
    }
    foreach ($id in $actualByKey.Keys) { $differences.Add("row $Key=$id is new") }
}

if ($differences.Count -eq 0) {
    Write-Output "Match: $($goldenRows.Count) row(s), 0 differences"
    exit 0
}
Write-Output "Mismatch: $($differences.Count) difference(s)"
$differences | ForEach-Object { Write-Output "  - $_" }
exit 1

The rules are deliberately strict:

The bug it caught

Suppose a change to the macro shortens its loop by one row (the kind of slip that happens when someone adds a total line):

-    For r = 2 To lastRow
+    For r = 2 To lastRow - 1

The macro runs without an error and writes a plausible report. The comparison says otherwise:

PS> ./compare-values.ps1 -Golden golden/report.csv -Actual output/report.csv -Key Store -Tolerance @{ Amount = 0.005 }
Mismatch: 2 difference(s)
  - row Store=South, column 'Units': expected '6', found '2'
  - row Store=South, column 'Amount': expected '58.09', found '19.99'

The last input row (South, 30 September) was dropped. Exit code 1, so a script or a CI job stops there. Run compare-values.ps1 from a PowerShell prompt as shown: -Tolerance takes a hashtable, which pwsh -File cannot pass.

When a difference is intended

If the client asked for the change, the differences are the expected ones and nothing else should move. Read the list, confirm each line is part of the request, then export the new output over the golden and commit it with the reason (git commit -m "Approve report golden: amounts now include shipping, request #12"). The pull request shows the golden's diff next to the code's diff, which is exactly what a reviewer needs.

Run the comparison in CI

Microsoft does not support running Office unattended (Microsoft Learn), so the macro and the export stay on your machine. What CI can do is compare the output you committed with the golden on every pull request:

name: report-check
on: pull_request
permissions:
  contents: read
jobs:
  compare:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@3d3c42e5aac5ba805825da76410c181273ba90b1  # v7.0.1
      - name: Compare the committed output with the golden
        shell: pwsh
        run: ./tools/compare-values.ps1 -Golden golden/report.csv -Actual output/report.csv -Key Store -Tolerance @{ Amount = 0.005 }

One limit to keep in mind: CI compares what you commit. It cannot tell whether output/report.csv was produced by the workbook in the same commit, so regenerate it before you push. (We tested the scripts on Windows; the comparison uses only PowerShell 7 and .NET, but run it once on your CI image before relying on it.)

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.