DEV Community

Cover image for Regression-testing an Excel/VBA report with golden files
Andrew R
Andrew R

Posted on AI-assisted

Regression-testing an Excel/VBA report with golden files

#excel #vba #powershell #testing

The VBA that a business depends on is rarely a library. Usually it's one macro that reads a few files, builds a report and saves it. You can unit-test that (Rubberduck has a unit-testing framework for VBA), but the client's question is simpler: is the report the same as yesterday, apart from what I asked you to change?

A golden file answers exactly that.

The idea

  1. Run the macro once on known input and check the output by hand. Once it's right, approve it and keep it as the golden file.
  2. After each change, run the macro on the same input and compare the new output with the golden, cell by cell.
  3. Every difference is a bug or a change you meant to make. If you meant it, approve the new output on purpose. Don't let anything overwrite the golden quietly.

Making the comparison worth reading

  • Match rows by key as well as by position. Sort the report differently and a positional comparison flags every row. Match on a key column (Store, OrderId) and you only see the rows that changed.
  • Set tolerances per column. Currency rounding shouldn't fail the check. An order count should match exactly.
  • Report where and by how much. "Different" tells you nothing. "Row with Store=Central, column Revenue (D4): expected 92.71, found 60.71, delta -32" tells you where to look.
  • Empty is not zero. An empty required cell in the input is its own failure. Don't read it as 0.
  • Unreadable output never passes. If the file won't open, that's a failure. It doesn't mean "no differences".

A real example

In the demo I recorded, the bug is one character in a date filter: <= became <, so the report drops the last day of the month. The macro runs without an error. The comparison against the golden reports:

compare monthly-report-xlsx: Mismatch: 8 differences (1 within tolerance)
  - row with Store=Central, column 'Orders' (cell B4): expected '4', found '3' (delta -1; no tolerance: exact match required).
  - row with Store=Central, column 'Revenue' (cell D4): expected '92.71', found '60.71' (delta -32, outside the tolerance of ±0.005).
  - row with Store=Market, column 'Units' (cell C5): expected '13', found '11' (delta -2; no tolerance: exact match required).
 EXIT 1  fail
Enter fullscreen mode Exit fullscreen mode

Look at the exit code too. 1 means "a check failed", so a script or a CI job can stop right there.

Running Excel is the hard part

Microsoft doesn't support automating Office with nobody at the machine, so hosted CI (GitHub Actions and similar) is the wrong place to run the macro. What I do instead:

  • run the macro locally, on a copy of the workbook, with a time limit, and keep its output only if every step worked;
  • commit that output and let CI compare it with the golden. CI never needs Office.

CI will catch an output that differs. It can't prove the committed output came from the committed workbook, so that part is still on you.

Where the example comes from

The example above comes from a public demo repo, with the four scenarios and the full text of a run: https://github.com/KBroneZ/excel-vba-regression-testing-demo

The short version: before you touch a macro again, approve a golden output, and compare against it afterwards. Any tooling works, a careful diff by hand included.

There's a longer step-by-step version with two PowerShell scripts you can run on your own workbook (one exports the values to CSV, the other compares them by key with tolerances): https://steadylatch.com/guides/excel-vba-regression-testing

AI-assisted: drafted with an AI model and checked line by line by me against the recorded demo.

Top comments (0)