# Model Audit Tool v2.1

A single-file Excel VBA module (`ModelAuditTool.bas`) that audits financial models for common risks and lets you snapshot / diff the workbook against a baseline.

## Install

1. Open your model in Excel (Windows or Mac).
2. `Alt+F11` (Win) or `Opt+F11` (Mac) → open the VBA editor.
3. If an older `ModelAuditTool` module exists, right-click → **Remove** it first.
4. **File → Import File…** and pick `ModelAuditTool.bas`.
5. Close the editor. Press `Alt+F8` to run any of the four macros below.

## Macros

| Macro | What it does |
| --- | --- |
| `RunModelAudit` | Scans workbook + named ranges, writes findings + summary dashboard to `Audit_Report`. Color-codes by severity, adds click-to-navigate hyperlinks. |
| `SaveBaseline` | Snapshots every non-empty cell's formula + value + timestamp to `Audit_Baseline` (`xlSheetVeryHidden`). |
| `CompareToBaseline` | Diffs the current workbook against the snapshot. Writes `Baseline_Diff` with Added / Removed / Formula-changed / Value-drift rows. |
| `ClearBaseline` | Deletes the hidden snapshot. |

## Checks performed

Cell-level:

- Hardcoded literals in formulas (Warning)
- Static values sandwiched between formula neighbors (Warning)
- Error cells — `#REF!`, `#DIV/0!`, `#N/A`, `#VALUE!`, `#NAME?`, `#NUM!`, `#SPILL!`, etc. (Critical)
- External workbook links (Critical)
- Number-format mismatch vs. row neighbors (Warning)
- Formula-pattern break vs. row neighbors (Warning)
- Formula-pattern break vs. column neighbors (Warning) — *new v2*
- Volatile functions: `OFFSET`, `INDIRECT`, `NOW`, `TODAY`, `RAND`, `RANDBETWEEN`, `INFO`, `RANDARRAY` (Warning) — *new v2*
- Merged cells (Warning) — *new v2*
- Circular references, per sheet (Critical) — *new v2*
- Hidden rows / columns (Warning)
- Hidden vs. very-hidden worksheets flagged distinctly (Warning / Critical)

Named-range hygiene — *new v2.1*:

- Dangling named ranges (refer to `#REF!`) (Critical)
- Named ranges that no longer resolve to a real range (Warning)
- Workbook-scope vs. sheet-scope name collisions (Warning)
- Hidden names (`Visible = False`) (Warning)

## Whitelist / ignore list (`Audit_Config` sheet)

Create a sheet named `Audit_Config`. Column A lists rules; wildcards `*` and `?` are supported.

| Column A rule | Effect |
| --- | --- |
| `Hardcoded number in formula` | Suppress this issue type everywhere. |
| `Inputs!*` | Suppress every issue on the `Inputs` sheet. |
| `Assumptions!B5:B99` | Suppress every issue in that range. |
| `Model!C10\|Volatile function` | Suppress only Volatile-function findings at Model!C10. |

Cell **B1** on `Audit_Config` (optional): a comma-separated list overriding the default trivial-numbers list (`0,1,2,4,10,12,52,100,365,1000`).

## Baseline / diff

- `SaveBaseline` writes to `Audit_Baseline` and sets it to `xlSheetVeryHidden` (only reachable from VBA, won't be accidentally edited).
- `CompareToBaseline` uses `VALUE_TOLERANCE = 0.000001` so float noise isn't reported as drift; edit the constant at the top of the module to loosen/tighten.
- `Baseline_Diff` rows are color-coded: green = Added, red = Formula changed, yellow = Value drift, grey = Removed.

## Compatibility

- Windows Excel and Mac Excel. No `Scripting.Dictionary`, no other Windows-only late-bound COM.
- The tool never mutates your data — it only writes to its own sheets (`Audit_Report`, `Audit_Baseline`, `Baseline_Diff`).

## Packaging as an `.xlam` add-in (one-time, ~2 min)

Building an `.xlam` requires Excel itself (VBA + XLAM formats are proprietary), so this is a manual step on your desktop. Do it once:

1. Open a blank workbook in Excel.
2. `Alt+F11` → **File → Import File…** → pick `ModelAuditTool.bas`.
3. Back in Excel: **File → Save As** → change file type to **Excel Add-In (`*.xlam`)**. Save it anywhere (Excel's default add-ins folder is fine).
4. **File → Options → Add-Ins → Manage: Excel Add-Ins → Go…**, tick the new add-in, click OK.
5. Optional but recommended: add a ribbon button.
   - **File → Options → Customize Ribbon**, create a new custom group on the Home tab.
   - From "Choose commands from" pick **Macros**, then add `RunModelAudit`, `SaveBaseline`, `CompareToBaseline`.

After that, every workbook you open in Excel will have the macros available without importing anything.

Publishing to teammates: send them the `.xlam` and a one-line instruction to enable it via **File → Options → Add-Ins → Go…**.

## Notes on testing

This module ships as source, not as compiled binary. I recommend a quick smoke test the first time you import it:

1. Open a workbook with at least one formula, one hardcoded number in a formula, one merged cell, and one named range.
2. Run `RunModelAudit` — expect `Audit_Report` to appear with color-coded rows.
3. Run `SaveBaseline` — expect the confirmation dialog with a cell count.
4. Change one formula in the workbook, then run `CompareToBaseline` — expect a red "Formula changed" row.
5. Run `ClearBaseline` — expect the confirmation dialog.

If any macro throws a `Compile error` on import, most likely cause is a stale copy of the old module still in the project — remove it, then re-import.