Macro (Excel)

Glossary Term • Intermediate • 7 min read

Audience: Model Developers • Auditors
Last Reviewed: July 2026
Updated: Version 1.0

Executive Summary

An Excel macro is a recorded or programmatically written sequence of instructions that automates tasks in Microsoft Excel. Macros are written in Visual Basic for Applications (VBA), the scripting language embedded in Microsoft Office applications. When executed, a macro performs a series of operations on the workbook — such as formatting cells, copying data, running calculations, or generating reports — without requiring manual input. In financial modelling, the term macro refers specifically to VBA-based automation within an Excel workbook (.xlsm or .xlsb file format). It is distinct from Excel functions, formulas, and add-ins.

Key Takeaways

✓ An Excel macro is a VBA-based automation sequence that can modify model content without leaving visible evidence in the worksheet.
✓ Macros can overwrite formula results, hide content, modify calculation settings, and connect to external data.
✓ Event macros run automatically and may modify outputs before the user or auditor sees the model.
✓ Auditors must inspect all VBA code, test macro vs non-macro outputs, and verify UDF implementations.
✓ Macro use in financial models submitted to lenders or investment committees should be minimised and fully documented.

Definition¶

An Excel macro is a recorded or programmatically written sequence of instructions that automates tasks in Microsoft Excel. Macros are written in Visual Basic for Applications (VBA), the scripting language embedded in Microsoft Office applications. When executed, a macro performs a series of operations on the workbook — such as formatting cells, copying data, running calculations, or generating reports — without requiring manual input.

In financial modelling, the term macro refers specifically to VBA-based automation within an Excel workbook (.xlsm or .xlsb file format). It is distinct from Excel functions, formulas, and add-ins.

Why It Matters¶

Macros introduce a category of complexity and risk that does not exist in formula-only financial models. For model auditors and risk managers, macros are significant because:

They are invisible in normal operation. A formula in a cell is visible to anyone who clicks on it. VBA code runs behind the cell interface and is not visible unless the reviewer opens the Visual Basic Editor (Alt + F11). An auditor reviewing only the worksheet view of a model with macros sees an incomplete picture.

They can modify model outputs. A macro can overwrite formula results with hardcoded values, change cell formatting to obscure errors, hide rows or sheets, or modify assumptions — all without leaving a trace in the worksheet that is visible to a non-technical reviewer.

They can override calculation logic. A macro that runs automatically (via Workbook_Open or Worksheet_Change events) can alter the model's outputs before the user sees them, making the displayed result different from what the formulas would produce if calculated directly.

They introduce version and security risk. Macros can contain malicious code. Organisations that distribute models with macros must ensure they have appropriate security controls, including code signing and macro execution policies.

Technical Background¶

Types of Excel Macros¶

Type	Description	Financial Model Use
Sub procedures	Named sequences of VBA instructions executed on demand	Automating report generation, formatting, data refresh
Function procedures	Custom functions returning a value to a cell	User-defined calculations not available as standard Excel functions
Event macros	Macros triggered automatically by workbook or worksheet events	Auto-calculation, auto-formatting, data validation on input
Class modules	Object-oriented VBA code	Rare in standard financial models; found in complex automation

Workbook Events That Trigger Macros Automatically¶

The following events can trigger a macro without any user action:

Workbook_Open — runs when the file is opened
Workbook_BeforeSave — runs before the file is saved
Worksheet_Change — runs when a cell value on a sheet changes
Worksheet_Calculate — runs when a sheet recalculates
Workbook_SheetActivate — runs when a sheet tab is clicked

An auditor must check whether any of these events contain code that modifies model outputs before assessing the model's formula integrity.

Macro-Enabled File Formats¶

Format	Extension	Notes
Excel Macro-Enabled Workbook	.xlsm	Standard macro-enabled format
Excel Binary Workbook	.xlsb	Binary format; faster for large models; harder to audit without opening
Excel Add-In	.xlam	Contains reusable VBA functions; used to extend Excel functionality

A model submitted as .xlsx (standard Excel format) cannot contain macros. A model received as .xlsm or .xlsb may contain macros and should be reviewed accordingly.

Accessing VBA Code in Excel¶

To inspect the VBA code in a macro-enabled workbook:

Open the file with macros enabled (or choose to disable macros first for safe inspection)
Press Alt + F11 to open the Visual Basic Editor
In the Project Explorer panel, expand the workbook to see all modules, class modules, and worksheet code
Review each code module for procedures that modify cell values, hide rows or columns, clear content, or run event-triggered modifications

Risks Specific to Financial Model Macros¶

Hardcoding via macro: A macro can set a cell value directly (e.g. Range("B12").Value = 47382916), overwriting a formula with a hardcoded result. The cell will display a number but contain no formula — indistinguishable from a legitimate hardcoded input unless the macro code is inspected.

Selective calculation: A macro can turn off automatic calculation (Application.Calculation = xlCalculationManual) and run only selected parts of the model, producing results that reflect a partial rather than full recalculation.

Sheet hiding and protection: Macros can hide worksheets and protect them with a password, concealing model sections from reviewers. A comprehensive audit must account for all hidden and protected content.

Data source connectivity: Macros can connect to external data sources (databases, APIs, other files) and refresh data at runtime. The model's outputs may depend on data that is not visible or auditable from the workbook itself.

Audit Considerations¶

1. Identify the Presence of Macros¶

Before reviewing a model, determine whether it contains macros:

Check the file extension (.xlsm or .xlsb indicates macros may be present)
Open the Visual Basic Editor (Alt + F11) and inspect the Project Explorer
Check whether any cell formulas call user-defined functions that are implemented in VBA

2. Review All VBA Code¶

Read every procedure in every module. Pay particular attention to:

Procedures that write values directly to cells
Event macros (Workbook_Open, Worksheet_Change, etc.)
Procedures that modify calculation settings
Procedures that hide, protect, or delete content
Procedures that connect to external data sources

3. Test Macro vs Non-Macro Outputs¶

Where possible, verify that the model produces the same results when macros are disabled (calculation run from formulas only) as when macros are enabled. A discrepancy indicates that macros are modifying formula outputs.

4. User-Defined Functions (UDFs)¶

If the model uses VBA user-defined functions in cell formulas, verify that these functions are correctly implemented. A UDF that contains an error will return an incorrect value to every cell that references it. UDFs are not visible in the formula bar in the same way that built-in functions are — only the function name appears.

5. Macro Documentation¶

A model that relies on macros should include documentation that:

Lists all macros and their purpose
Identifies any event macros that run automatically
Specifies how to enable, disable, and re-run macros
Documents any external data dependencies

The absence of this documentation is itself an audit finding.

6. Security and Code Signing¶

In an institutional context, macros in financial models distributed to third parties should be digitally signed by the author. This allows recipients to verify that the code has not been modified in transit. Unsigned macros from unknown sources should be treated with caution.

Common Errors¶

Error	Description	Risk
Event macro overwriting results	Workbook_Open macro sets cell values before user sees model	Displayed results do not match formula logic
UDF not installed	Model references a UDF that is not available in the reviewer's environment	Cells return #NAME? error or wrong result
Manual calculation mode	Macro sets calculation to manual and is not reset	Model shows stale results until F9 is pressed
Hidden macro code	Relevant logic in a hidden module that is not reviewed	Audit is incomplete
External data dependency	Macro refreshes from an external source not available to reviewer	Results at time of audit differ from operating results
No documentation	Macro purpose and behaviour not described	Reviewer cannot assess scope or impact

Best Practices¶

Minimise macro use in financial models that will be shared with third parties, submitted to lenders, or relied upon for investment decisions. Where macros are necessary, use them only for non-calculation tasks (formatting, report generation, navigation) rather than for modifying model outputs.

Document all macros in a dedicated code documentation section or in inline VBA comments. Every procedure should have a comment header describing its purpose, inputs, outputs, and any event triggers.

Where macros modify cell values, log the modification in an audit trail (a dedicated sheet that records what was changed, when, and by which procedure).

Disable macros and verify formula integrity as a standard first step in any audit of a macro-enabled financial model.

Continue Reading¶

Prerequisites¶

What Makes an Excel Financial Model Reliable? — the parent pillar

Named Range

How OXXON tests thisRun a free structural check with FMAE

Frequently Asked Questions

Can a financial model work correctly without enabling macros?

It depends on the model. Some models use macros only for non-essential tasks (formatting, navigation) and function correctly without them. Others rely on macros for calculations or data refresh and will produce incorrect or incomplete results without them. Testing the model with macros disabled is a standard audit step.

Are user-defined functions the same as macros?

User-defined functions (UDFs) are VBA functions that can be called from cell formulas. They are a type of macro but differ from standard macros in that they return a value to a cell rather than performing operations on the workbook. UDFs are subject to all the same audit considerations as macros.

Is it acceptable to use macros in a financial model submitted to a lender?

Practice varies. Some lenders require models to be formula-only and will not accept macro-enabled workbooks. Others accept macros provided the code is disclosed and reviewed. Practitioners should confirm the lender's requirements before submitting a macro-enabled model as a condition precedent deliverable.

How do I identify all macros in a workbook?

Open the Visual Basic Editor (Alt + F11) and review each item in the Project Explorer: ThisWorkbook (for workbook-level events), each sheet module (for sheet-level events), and each standard module and class module. Check for any hidden or very-hidden sheets that may contain additional VBA code.

What Makes an Excel Financial Model Reliable?

An Excel financial model is a structured spreadsheet used to represent, calculate, and forecast the financial mechanics of a business, investment, or transaction. Reliability is not a function of how sophisticated a model looks; it is a function of its structure, discipline, and consistency. This page defines what an Excel financial model is, the structural characteristics that separate a reliable model from a fragile one, and the standards and terminology that underpin every other page in the FMAE Knowledge Centre that references a specific modelling concept. This is a crowded educational topic, and most existing content in this space is course marketing rather than a neutral reference. This page is written as the latter: a vendor neutral definition of reliable modelling practice, not a sales page for a training course.

Formula Consistency in Financial Models

Formula consistency in a financial model means that cells in the same row or column that perform the same calculation use identical or structurally equivalent formulas. In a time-series financial model, the formula in the Year 1 column of a revenue line should be structurally identical to the formula in the Year 5 column of the same line, with references shifting as appropriate across periods. A cell that contains a formula materially different from its neighbours in the same row is either performing a different calculation intentionally (which should be documented) or contains an error introduced by manual editing.

Spreadsheet Engineering

Spreadsheet engineering is the application of systematic engineering principles to the construction and maintenance of spreadsheet-based financial models. It treats a financial model as a software artefact subject to design principles, structural requirements, and quality standards analogous to those applied in software engineering, rather than as an ad hoc calculation tool built without formal discipline. The core principles of spreadsheet engineering are: separation of inputs, calculations, and outputs; consistent formula construction; avoidance of circular dependencies; complete documentation; and systematic version control. These principles are codified in recognised standards including the ICAEW Financial Modelling Code and the FAST Standard.

What Is Model Risk?

Model risk is the risk that a decision is wrong not because the underlying business or investment case was flawed, but because the model used to evaluate it was. It is a distinct category of risk from market risk, credit risk, or operational risk, and it applies to any organisation that relies on a financial model, spreadsheet or otherwise, to support a material decision. Most published model risk content addresses statistical and regulatory capital models used inside banks. This page defines model risk specifically as it applies to Excel based financial models, the kind used every day for investment decisions, lending, and transaction evaluation, which is a related but distinct problem from the quantitative model risk literature most search results return.

Named Range

A named range is a label assigned to a specific cell or range of cells in Microsoft Excel (or another spreadsheet application) using the Name Manager. Once named, the label can be used in formulas instead of the cell's coordinate reference (such as B12 or Sheet1!B12), making formulas more readable and reducing the likelihood of reference errors. Named ranges can refer to a single cell, a range of cells, a constant value, or a formula. They are defined at either the workbook level (accessible from any sheet) or the sheet level (accessible only from a specific sheet).