The macro had a narrow job
The starting expense workbook already contained the synthetic source table, Power Query and report formulas. We saved a separate .xlsm copy, inserted a reviewed research module and called that module through Excel’s Application.Run. No macro-security setting or account configuration was changed.
The macro refreshed the Actuals query synchronously, rebuilt calculation, checked for 480 clean documents and compared Report!C9 with $686,500 using a half-cent tolerance. Only after those checks did it write a success receipt into Read me!A14 and save the workbook.
This demonstrates native VBA execution in the tested environment. It does not demonstrate running arbitrary downloaded macros, bypassing Trust Center or deploying an unsigned macro under a customer’s policy. Treat approved code and the environment’s permission to run it as explicit inputs to the task.
Name the workbook and module
Microsoft documents Application.Run as the method for running a macro or function. Our invocation includes the workbook filename and PanesResearch.RefreshAndReconcile so it targets the intended copy. The test captures the resulting receipt rather than relying solely on the controller command completing.
The full published script includes the macro source. Review that source before executing the downloadable workbook. A production workflow should use its organization’s approved macro distribution and trust policy; this experiment does not choose that policy for you.
The code raises errors when its financial assertions fail. Microsoft also documents that an unhandled error inside a called macro can display a runtime dialog and stop unattended execution. Our observed run passed. Do not treat this happy-path fixture as a tested failure-reporting framework; add and test a bounded error-return contract before adapting it to unattended production use.
$macro = "'" + $wb.Name + "'!PanesResearch.RefreshAndReconcile"
$xl.Run($macro)
$receipt = $wb.Worksheets.Item('Read me').Range('A14').Value2
if ($receipt -ne 'VBA research check passed: 480 documents / USD 686500') {
throw 'Expected macro receipt is missing'
}A receipt records a checked stage
The saved receipt is useful because the macro writes it after its two assertions. It is not an independent audit of every report cell. The query could still need additional tests for date boundaries, duplicate conflicts, mapping completeness or downstream pivots.
For this research fixture, the receipt establishes the tested document count and report total. Keep the source file, macro source and result log together. That lets a reviewer distinguish what the code checked from what a later narrative claims.
The test also required programmatic access to the VBA project because it created its own module. Running an already approved macro and generating new VBA code are different operations. If your organization permits the former and blocks the latter, use its approved workbook rather than weakening that policy.
Use a fresh research copy
Download the macro-enabled output, exact result and script including VBA source. The script expects the synthetic expense workbook named expense-original.xlsx in a new task directory.
Use Panes desktop execution and keep the native Office window available for review. If execution is blocked or a dialog appears, report that state and inspect it. The result should never be described as passed merely because the controller stopped waiting.
For broader acceptance, apply the workbook verification checks and the query-to-pivot dependency test. A successful VBA call is one stage in a checked Office workflow.