Introduction
Picture this: you've just inherited a 15-tab financial model from a colleague who left the company. The model drives quarterly revenue forecasts, and leadership is asking you to sign off on it before the board presentation next week. Where do you even start? You open the first sheet and see hundreds of cells — some with formulas, some with raw numbers buried inside formulas, some referencing sheets you haven't found yet. Manually tracing every calculation would take days, and you'd almost certainly miss something.
This is exactly the problem a programmatic audit tool solves. Instead of relying on Excel's built-in "Trace Precedents" arrows (which are visual-only, non-persistent, and collapse the moment you click elsewhere), you build a VBA tool that systematically inspects every cell in the model, maps dependencies, flags structural problems, and writes everything to a clean, actionable report you can hand to a colleague or file as documentation.
By the end of this lesson, you'll have built a fully functional Financial Model Audit Tool: a VBA macro suite that crawls a workbook, identifies hardcoded values embedded in formulas, traces formula precedent chains, checks for structural consistency, and generates a formatted multi-section audit report on a dedicated worksheet. This is production-grade code you can drop into real engagements — not a toy demo.
What you'll learn:
- How to programmatically detect hardcoded values in formulas using string parsing and regex techniques
- How to build a precedent-tracing engine using
DirectPrecedents and recursive range traversal
- How to flag common financial model quality issues (missing error handling, circular references, inconsistent row patterns)
- How to structure and write a formatted audit report worksheet with severity ratings
- How to wire everything together into a single-click audit runner with progress feedback
Prerequisites
You should be comfortable writing VBA procedures, working with ranges and loops, and navigating the Excel object model. If you need a refresher on any of those foundations, the lessons on working with ranges, cells, and worksheets in VBA and VBA variables, data types, and control structures will get you up to speed quickly.
You should also understand basic error handling in VBA — the audit tool itself uses structured error trapping throughout, and error handling and debugging VBA code like a pro covers the patterns we'll rely on.
Before writing a single line of code, it pays to think about what an audit tool actually needs to do. Financial model auditing has four distinct concerns:
1. Hardcode detection — Finding numeric literals embedded directly in formulas (e.g., =Revenue*0.21 where 0.21 is a tax rate that should live in an assumptions cell).
2. Precedent mapping — For each formula cell, understanding where its inputs come from — both direct and indirect precedents — so you can assess whether a formula is pulling from the right source and whether any inputs are dangerously far removed from the calculation.
3. Structural consistency checks — Identifying rows where the formula pattern breaks mid-range (a common source of model errors), checking for unprotected input cells in calculation areas, and spotting cells that reference other workbooks.
4. Report generation — Collecting all findings, categorizing them by severity, and writing a formatted worksheet that a reviewer can actually navigate and act on.
We'll build each of these as a separate module and then wire them together in a master RunAudit procedure. This separation matters: if you ever want to run just the hardcode scan without regenerating the full report, you can.
Note: The tool will audit the active workbook by default, scanning all sheets except the report sheet itself. If you want to audit a different workbook, either make it active before running or adapt the targetWb variable I'll introduce shortly.
Module 1: Setting Up the Data Structures
We'll use a few custom Type definitions and module-level collections to hold findings as the audit runs. Add a new module called mod_AuditCore and start with this foundation:
Option Explicit
' Severity constants
Public Const SEV_HIGH As String = "HIGH"
Public Const SEV_MEDIUM As String = "MEDIUM"
Public Const SEV_LOW As String = "LOW"
Public Const SEV_INFO As String = "INFO"
' Report sheet name
Public Const REPORT_SHEET As String = "AUDIT_REPORT"
' Structure to hold a single audit finding
Public Type AuditFinding
SheetName As String
CellAddress As String
Category As String
Severity As String
Description As String
Detail As String
End Type
' Dynamic array to accumulate findings
Public gFindings() As AuditFinding
Public gFindCount As Long
' Initialize the findings collection
Public Sub InitFindings()
gFindCount = 0
ReDim gFindings(1 To 5000)
End Sub
' Add a single finding to the collection
Public Sub AddFinding(sSheet As String, sCell As String, _
sCat As String, sSev As String, _
sDesc As String, sDetail As String)
gFindCount = gFindCount + 1
If gFindCount > UBound(gFindings) Then
ReDim Preserve gFindings(1 To UBound(gFindings) + 1000)
End If
With gFindings(gFindCount)
.SheetName = sSheet
.CellAddress = sCell
.Category = sCat
.Severity = sSev
.Description = sDesc
.Detail = sDetail
End With
End Sub
The AuditFinding type captures everything we need to populate a report row: where the issue lives, what kind of issue it is, how serious it is, and enough detail for the reviewer to understand what they're looking at without having to hunt down the cell manually.
Tip: Allocating 5,000 slots upfront and then growing by 1,000 at a time is a deliberate performance choice. Calling ReDim Preserve on every single addition is expensive at scale. For a workbook with 50,000 formula cells, this pattern keeps memory reallocations rare. You can read more about this technique in the lesson on VBA arrays and collections for efficient data processing.
Module 2: The Hardcode Detection Engine
Hardcoded numbers in formulas are the single most common model quality problem. The pattern looks harmless — =D14*1.085 — but that 1.085 might be an assumed growth rate that someone will update in three months by hunting for every occurrence rather than changing a single assumptions cell.
The detection logic needs to parse the formula string and identify numeric literals. We'll use VBA's RegExp object for this, since it gives us precise control over what counts as a "suspicious" number.
Add a new module called mod_HardcodeDetector:
Option Explicit
' Patterns we DON'T want to flag as hardcodes
' (small integers that are structural, not assumptions)
Private Const IGNORE_PATTERN As String = "^[01]$"
Public Sub ScanForHardcodes(ws As Worksheet)
Dim cell As Range
Dim usedRng As Range
Dim formula As String
Dim matches As Object
Dim matchItem As Object
Dim re As Object
Dim reIgnore As Object
Dim matchList As String
' Set up regex for numeric literals in formulas
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.IgnoreCase = True
' Match numbers: integers, decimals, negatives, percentages embedded as decimals
re.Pattern = "(?<![A-Z:$])(-?\d+\.?\d*(?:E[+-]?\d+)?)(?![A-Z\(:])"
' Set up ignore pattern for structural constants (0, 1)
Set reIgnore = CreateObject("VBScript.RegExp")
reIgnore.Global = False
reIgnore.Pattern = IGNORE_PATTERN
On Error Resume Next
Set usedRng = ws.UsedRange
On Error GoTo 0
If usedRng Is Nothing Then Exit Sub
For Each cell In usedRng
If cell.HasFormula Then
formula = cell.Formula
' Skip cells whose formulas are just a reference
If Left(formula, 1) = "=" And InStr(formula, "(") = 0 _
And InStr(formula, "+") = 0 And InStr(formula, "*") = 0 _
And InStr(formula, "-", 2) = 0 And InStr(formula, "/") = 0 Then
' Pure reference like =A1 or =Sheet2!B5 — skip
Else
Set matches = re.Execute(formula)
If matches.Count > 0 Then
matchList = ""
For Each matchItem In matches
Dim numVal As String
numVal = matchItem.Value
' Skip structural constants
If Not reIgnore.Test(numVal) Then
matchList = matchList & numVal & ", "
End If
Next matchItem
matchList = Left(matchList, Len(matchList) - 2) ' trim trailing comma
If Len(matchList) > 0 Then
Dim sev As String
sev = ClassifyHardcodeSeverity(matchList)
AddFinding ws.Name, cell.Address(False, False), _
"Hardcode", sev, _
"Numeric literal embedded in formula", _
"Values: " & matchList & " | Formula: " & formula
End If
End If
End If
End If
Next cell
End Sub
Private Function ClassifyHardcodeSeverity(matchList As String) As String
' If the hardcode looks like a rate (between 0.01 and 1, or a large round number)
' flag it HIGH; structural integers (like row offsets) get MEDIUM
Dim parts() As String
Dim i As Integer
Dim val As Double
parts = Split(matchList, ",")
For i = 0 To UBound(parts)
On Error Resume Next
val = CDbl(Trim(parts(i)))
On Error GoTo 0
' Rates and multipliers: between 0.01 and 99 but not a round year
If val > 0.01 And val < 100 And val <> 12 And val <> 365 Then
ClassifyHardcodeSeverity = SEV_HIGH
Exit Function
End If
' Large round numbers might be manual overrides
If val >= 1000 And (val Mod 1000 = 0) Then
ClassifyHardcodeSeverity = SEV_HIGH
Exit Function
End If
Next i
ClassifyHardcodeSeverity = SEV_MEDIUM
End Function
The regex pattern here deserves explanation. The negative lookbehind (?<![A-Z:$]) prevents matching the numeric parts of cell references like A1 or $B$12. The negative lookahead (?![A-Z\(:]) stops us from grabbing numbers that are immediately followed by a column letter (again, cell references) or function call syntax. This isn't perfect for every edge case, but it catches the vast majority of real hardcodes without drowning you in false positives.
Warning: The VBScript.RegExp object is a late-bound COM component. It works on all Windows versions of Excel but is not available in Excel for Mac. If your team uses Macs, swap the regex logic for a manual character-parsing loop — slower but cross-platform.
Module 3: Precedent Tracing
Excel's own DirectPrecedents property returns a range of cells that a given formula directly references. By calling it recursively, we can walk the entire dependency chain and detect things like cross-sheet references that are unexpectedly deep, or inputs that trace back to a different workbook entirely.
Add module mod_PrecedentTracer:
Option Explicit
' Track cells already visited to prevent infinite recursion
Private gVisited As Object ' Scripting.Dictionary
Public Sub TracePrecedentsForSheet(ws As Worksheet)
Dim cell As Range
Dim usedRng As Range
Set gVisited = CreateObject("Scripting.Dictionary")
On Error Resume Next
Set usedRng = ws.UsedRange
On Error GoTo 0
If usedRng Is Nothing Then Exit Sub
For Each cell In usedRng
If cell.HasFormula Then
gVisited.RemoveAll
Dim depth As Integer
depth = 0
CheckPrecedentChain cell, ws.Name, cell.Address(False, False), depth
End If
Next cell
End Sub
Private Sub CheckPrecedentChain(cell As Range, originSheet As String, _
originAddr As String, depth As Integer)
Dim precRng As Range
Dim area As Range
Dim precCell As Range
Dim key As String
Const MAX_DEPTH As Integer = 10
If depth > MAX_DEPTH Then
AddFinding originSheet, originAddr, "Precedent Depth", SEV_MEDIUM, _
"Precedent chain exceeds " & MAX_DEPTH & " levels", _
"Deep dependency chains are hard to audit and prone to error"
Exit Sub
End If
On Error Resume Next
Set precRng = cell.DirectPrecedents
On Error GoTo 0
If precRng Is Nothing Then Exit Sub
For Each area In precRng.Areas
For Each precCell In area
key = precCell.Worksheet.Name & "!" & precCell.Address
' Check for external workbook references
If precCell.Worksheet.Parent.Name <> cell.Worksheet.Parent.Name Then
AddFinding originSheet, originAddr, "External Reference", SEV_HIGH, _
"Formula traces back to external workbook", _
"External source: " & precCell.Worksheet.Parent.Name & _
" | Cell: " & key
End If
' Check for cross-sheet references at shallow depth
If depth = 0 And precCell.Worksheet.Name <> cell.Worksheet.Name Then
' Just log it as INFO — cross-sheet references are normal
' but worth documenting
AddFinding originSheet, originAddr, "Cross-Sheet Ref", SEV_INFO, _
"Formula references another sheet at depth 1", _
"References: " & key
End If
' Don't recurse into already-visited cells
If Not gVisited.Exists(key) Then
gVisited.Add key, depth
If precCell.HasFormula Then
CheckPrecedentChain precCell, originSheet, originAddr, depth + 1
End If
End If
Next precCell
Next area
End Sub
A few design decisions worth noting here. First, we use a Scripting.Dictionary as a visited set to prevent infinite recursion in models with circular-ish reference chains (which are surprisingly common in models that use iterative calculation). Second, we log cross-sheet references at depth 0 as INFO rather than flagging them — they're normal and expected in multi-tab models. But external workbook references at any depth are HIGH severity because they represent fragile dependencies that break when the linked file moves.
Key insight: DirectPrecedents only works reliably on cells in the active workbook. If the formula contains a reference to a closed external workbook, the property will either return nothing or raise an error. Our On Error Resume Next guard handles this, but the finding won't include the external cell's full path. For production use on models with many external links, consider extending the tool to parse the formula string directly to extract external references.
Module 4: Structural Consistency Checks
Beyond individual cells, good audit practice looks at patterns across rows and columns. A common model error is a formula that's consistent across a range except for one cell — for example, every cell in row 12 computes =C12*GrowthRate except cell G12, which someone changed to a hardcoded value or a slightly different formula.
Add module mod_StructureChecker:
Option Explicit
Public Sub CheckStructuralConsistency(ws As Worksheet)
CheckRowFormulaConsistency ws
CheckForUnprotectedInputsInCalcArea ws
CheckForCircularReferences ws
CheckForErrorValues ws
End Sub
Private Sub CheckRowFormulaConsistency(ws As Worksheet)
' For each row in the used range, check if formula-bearing cells
' have consistent patterns. A row where 8 out of 9 cells share the
' same formula structure but one differs is flagged.
Dim usedRng As Range
Dim row As Long
Dim col As Long
Dim lastCol As Long
Dim lastRow As Long
Dim formulaBase As String
Dim matchCount As Long
Dim mismatch As String
Dim cell As Range
Set usedRng = ws.UsedRange
lastRow = usedRng.Rows.Count + usedRng.Row - 1
lastCol = usedRng.Columns.Count + usedRng.Column - 1
For row = usedRng.Row To lastRow
formulaBase = ""
matchCount = 0
mismatch = ""
Dim totalFormulas As Long
totalFormulas = 0
For col = usedRng.Column To lastCol
Set cell = ws.Cells(row, col)
If cell.HasFormula Then
totalFormulas = totalFormulas + 1
' Normalize the formula by replacing cell addresses with tokens
Dim normFormula As String
normFormula = NormalizeFormula(cell.Formula)
If formulaBase = "" Then
formulaBase = normFormula
ElseIf normFormula <> formulaBase Then
mismatch = mismatch & cell.Address(False, False) & " "
Else
matchCount = matchCount + 1
End If
End If
Next col
' Only flag if there are enough formulas to make a pattern
' and there's at least one mismatch
If totalFormulas >= 4 And Len(Trim(mismatch)) > 0 Then
If matchCount >= (totalFormulas - 2) Then
AddFinding ws.Name, "Row " & row, _
"Formula Inconsistency", SEV_HIGH, _
"Row has inconsistent formula pattern", _
"Mismatched cells: " & Trim(mismatch)
End If
End If
Next row
End Sub
Private Function NormalizeFormula(f As String) As String
' Replace absolute and relative column/row references with tokens
' so =SUM(C5:C10) and =SUM(D5:D10) are treated as the same pattern
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\$?[A-Z]{1,3}\$?\d+"
NormalizeFormula = re.Replace(f, "§REF§")
End Function
Private Sub CheckForUnprotectedInputsInCalcArea(ws As Worksheet)
' Flag cells that appear to be input assumptions but are not locked
' in a sheet that is otherwise locked
If Not ws.ProtectContents Then Exit Sub
Dim cell As Range
For Each cell In ws.UsedRange
If Not cell.HasFormula And Not cell.Locked Then
' Unlocked constants on a protected sheet — likely intentional input
' But flag if the surrounding cells are formulas (calc area contamination)
Dim leftCell As Range, rightCell As Range
On Error Resume Next
Set leftCell = cell.Offset(0, -1)
Set rightCell = cell.Offset(0, 1)
On Error GoTo 0
If Not leftCell Is Nothing And Not rightCell Is Nothing Then
If leftCell.HasFormula And rightCell.HasFormula Then
AddFinding ws.Name, cell.Address(False, False), _
"Input in Calc Area", SEV_MEDIUM, _
"Unlocked constant surrounded by formula cells", _
"Value: " & cell.Value & " — verify this is intentional"
End If
End If
End If
Next cell
End Sub
Private Sub CheckForCircularReferences(ws As Worksheet)
' Excel tracks circular references at workbook level
' We check if any circular references exist on this sheet
Dim circRef As Range
On Error Resume Next
Set circRef = ws.CircularReference
On Error GoTo 0
If Not circRef Is Nothing Then
AddFinding ws.Name, circRef.Address(False, False), _
"Circular Reference", SEV_HIGH, _
"Circular reference detected", _
"Cell is part of a circular reference chain. " & _
"Verify iterative calculation setting is intentional."
End If
End Sub
Private Sub CheckForErrorValues(ws As Worksheet)
Dim cell As Range
For Each cell In ws.UsedRange
If IsError(cell.Value) Then
Dim errType As String
errType = CStr(cell.Value)
Dim sev As String
Select Case errType
Case "Error 2042" ' #N/A
sev = SEV_MEDIUM
Case "Error 2023" ' #REF!
sev = SEV_HIGH
Case "Error 2036" ' #NUM!
sev = SEV_HIGH
Case Else
sev = SEV_MEDIUM
End Select
AddFinding ws.Name, cell.Address(False, False), _
"Error Value", sev, _
"Cell contains error value: " & errType, _
"Formula: " & cell.Formula
End If
Next cell
End Sub
The NormalizeFormula function is the key to row consistency checking. By replacing all cell references with a token (§REF§), we turn =SUM(C5:C10)/C3 and =SUM(D5:D10)/D3 into identical normalized strings. This means an entire projection row where every column uses the same formula structure will produce no findings — only the cell where someone accidentally deleted the formula or changed it will flag.
All the scanning work means nothing without a readable output. The report sheet needs to be self-contained: headers, severity color coding, a summary section, and detailed findings a reviewer can filter and sort.
Add module mod_ReportWriter:
Option Explicit
Public Sub GenerateReport(targetWb As Workbook)
Dim ws As Worksheet
Dim reportWs As Worksheet
Dim nextRow As Long
Dim i As Long
' Delete existing report sheet
Application.DisplayAlerts = False
On Error Resume Next
targetWb.Sheets(REPORT_SHEET).Delete
On Error GoTo 0
Application.DisplayAlerts = True
' Create fresh report sheet
Set reportWs = targetWb.Sheets.Add(After:=targetWb.Sheets(targetWb.Sheets.Count))
reportWs.Name = REPORT_SHEET
' ---- HEADER BLOCK ----
WriteReportHeader reportWs, targetWb
' ---- SUMMARY SECTION ----
nextRow = 8
WriteSummarySection reportWs, nextRow
nextRow = nextRow + 8
' ---- DETAIL SECTION ----
WriteDetailHeader reportWs, nextRow
nextRow = nextRow + 1
For i = 1 To gFindCount
WriteDetailRow reportWs, nextRow, gFindings(i)
nextRow = nextRow + 1
Next i
' Apply autofilter to detail section
reportWs.Rows(nextRow - gFindCount - 1).AutoFilter
' Freeze panes below header
reportWs.Activate
reportWs.Range("A" & (nextRow - gFindCount)).Select
ActiveWindow.FreezePanes = True
' Autofit columns
reportWs.Columns("A:F").AutoFit
' Return to first sheet
targetWb.Sheets(1).Activate
End Sub
Private Sub WriteReportHeader(ws As Worksheet, wb As Workbook)
With ws.Range("A1")
.Value = "FINANCIAL MODEL AUDIT REPORT"
.Font.Size = 16
.Font.Bold = True
.Font.Color = RGB(31, 73, 125)
End With
ws.Range("A2").Value = "Workbook:"
ws.Range("B2").Value = wb.Name
ws.Range("A3").Value = "Audit Date:"
ws.Range("B3").Value = Now()
ws.Range("B3").NumberFormat = "yyyy-mm-dd hh:mm"
ws.Range("A4").Value = "Sheets Audited:"
ws.Range("B4").Value = wb.Sheets.Count - 1 ' exclude report sheet
ws.Range("A5").Value = "Total Findings:"
ws.Range("B5").Value = gFindCount
With ws.Range("A2:A5")
.Font.Bold = True
.Font.Color = RGB(68, 68, 68)
End With
End Sub
Private Sub WriteSummarySection(ws As Worksheet, startRow As Long)
Dim r As Long
r = startRow
' Section title
With ws.Cells(r, 1)
.Value = "SUMMARY BY SEVERITY"
.Font.Bold = True
.Font.Size = 12
.Font.Color = RGB(31, 73, 125)
End With
r = r + 1
' Count findings by severity and category
Dim highCount As Long, medCount As Long
Dim lowCount As Long, infoCount As Long
Dim i As Long
For i = 1 To gFindCount
Select Case gFindings(i).Severity
Case SEV_HIGH: highCount = highCount + 1
Case SEV_MEDIUM: medCount = medCount + 1
Case SEV_LOW: lowCount = lowCount + 1
Case SEV_INFO: infoCount = infoCount + 1
End Select
Next i
' Write summary rows
Dim sevData(3, 2) As Variant
sevData(0, 0) = SEV_HIGH: sevData(0, 1) = highCount: sevData(0, 2) = RGB(192, 0, 0)
sevData(1, 0) = SEV_MEDIUM: sevData(1, 1) = medCount: sevData(1, 2) = RGB(255, 102, 0)
sevData(2, 0) = SEV_LOW: sevData(2, 1) = lowCount: sevData(2, 2) = RGB(255, 192, 0)
sevData(3, 0) = SEV_INFO: sevData(3, 1) = infoCount: sevData(3, 2) = RGB(0, 112, 192)
Dim j As Integer
For j = 0 To 3
ws.Cells(r, 1).Value = sevData(j, 0)
ws.Cells(r, 1).Font.Bold = True
ws.Cells(r, 1).Font.Color = RGB(255, 255, 255)
ws.Cells(r, 1).Interior.Color = sevData(j, 2)
ws.Cells(r, 2).Value = sevData(j, 1)
ws.Cells(r, 2).Font.Bold = True
r = r + 1
Next j
End Sub
Private Sub WriteDetailHeader(ws As Worksheet, r As Long)
Dim headers As Variant
headers = Array("Sheet", "Cell", "Category", "Severity", "Description", "Detail")
Dim col As Integer
For col = 0 To 5
With ws.Cells(r, col + 1)
.Value = headers(col)
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(31, 73, 125)
End With
Next col
End Sub
Private Sub WriteDetailRow(ws As Worksheet, r As Long, f As AuditFinding)
ws.Cells(r, 1).Value = f.SheetName
ws.Cells(r, 2).Value = f.CellAddress
ws.Cells(r, 3).Value = f.Category
ws.Cells(r, 4).Value = f.Severity
ws.Cells(r, 5).Value = f.Description
ws.Cells(r, 6).Value = f.Detail
' Color-code severity column
Dim bg As Long
Select Case f.Severity
Case SEV_HIGH: bg = RGB(255, 199, 206) ' light red
Case SEV_MEDIUM: bg = RGB(255, 235, 156) ' light amber
Case SEV_LOW: bg = RGB(198, 239, 206) ' light green
Case SEV_INFO: bg = RGB(221, 235, 247) ' light blue
End Select
ws.Cells(r, 4).Interior.Color = bg
End Sub
Module 6: The Master Audit Runner
Now we wire everything together. This is the procedure you'll call — either from a button, a keyboard shortcut, or a menu. It also handles the performance housekeeping that keeps the tool running at acceptable speed on large models. This approach follows the same patterns as those used when building an automated reporting system with VBA.
Add module mod_AuditRunner:
Option Explicit
Public Sub RunFullAudit()
Dim targetWb As Workbook
Dim ws As Worksheet
Dim startTime As Double
Dim sheetCount As Long
startTime = Timer
Set targetWb = ActiveWorkbook
' Confirm we have something to audit
If targetWb.Sheets.Count < 2 Then
MsgBox "This workbook has only one sheet. " & _
"A useful audit requires a multi-sheet model.", _
vbExclamation, "Wicked Smart Audit"
Exit Sub
End If
' Performance: freeze screen, disable events and auto-calc
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.StatusBar = "Running financial model audit..."
' Initialize findings collection
InitFindings
' Count auditable sheets
sheetCount = 0
For Each ws In targetWb.Sheets
If ws.Name <> REPORT_SHEET Then sheetCount = sheetCount + 1
Next ws
Dim processed As Long
processed = 0
' Main audit loop
For Each ws In targetWb.Sheets
If ws.Name <> REPORT_SHEET Then
processed = processed + 1
Application.StatusBar = "Auditing sheet " & processed & " of " & _
sheetCount & ": " & ws.Name
' Run all three scanners
Application.StatusBar = Application.StatusBar & " [Hardcodes]"
ScanForHardcodes ws
Application.StatusBar = Application.StatusBar & " [Precedents]"
TracePrecedentsForSheet ws
Application.StatusBar = Application.StatusBar & " [Structure]"
CheckStructuralConsistency ws
End If
Next ws
' Generate the report
Application.StatusBar = "Generating audit report..."
GenerateReport targetWb
' Restore Excel settings
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False
Dim elapsed As Double
elapsed = Round(Timer - startTime, 1)
MsgBox "Audit complete." & vbCrLf & vbCrLf & _
"Total findings: " & gFindCount & vbCrLf & _
"Elapsed time: " & elapsed & " seconds" & vbCrLf & vbCrLf & _
"Results written to the '" & REPORT_SHEET & "' sheet.", _
vbInformation, "Wicked Smart Audit"
' Navigate to report
targetWb.Sheets(REPORT_SHEET).Activate
End Sub
Tip: Setting Application.Calculation = xlCalculationManual before iterating through large ranges can cut runtime by 60–80% on complex models, because Excel won't recalculate the workbook every time VBA touches a cell. Always restore it at the end of the procedure — and always restore it in your error handler too. See Excel performance optimization for a deeper treatment of these techniques.
Hands-On Exercise
Now that you have the full tool, put it through its paces on a realistic model. Here's how to construct a meaningful test:
Step 1 — Create a test model. Build a 4-sheet workbook with these sheets: Assumptions, Revenue, Costs, P&L.
On Assumptions, put labeled input cells: growth rate (1.08), tax rate (0.21), headcount (150), average salary (85000).
On Revenue, create a 5-year projection where most formulas reference Assumptions correctly — but deliberately corrupt two cells: embed *1.08 directly in one formula instead of referencing the growth rate cell, and hardcode *0.21 in another.
On Costs, insert one row where column F has a formula that differs from the pattern in columns C through E (e.g., change =Headcount*AvgSalary to a hardcoded =12750000).
On P&L, create a #REF! error by deleting a source cell.
Step 2 — Run the audit. Press Alt+F8, select RunFullAudit, and run it. Within 10–15 seconds for a small model, the report sheet should appear.
Step 3 — Validate the findings. You should see:
- At least 2
HIGH hardcode findings in Revenue
- At least 1
HIGH formula inconsistency in Costs
- At least 1
HIGH error value finding in P&L
- Multiple
INFO cross-sheet reference findings throughout
Step 4 — Extend the tool. Add a fifth check in mod_StructureChecker that flags any named range in the workbook whose RefersToRange is outside the Assumptions sheet. This enforces the discipline that all driver assumptions live in one place — a common standard in investment banking models.
Common Mistakes & Troubleshooting
The tool runs but finds nothing. The most common cause is that the macro ran against the report sheet itself (if it was active when you ran it) or the workbook contains only values rather than formulas (e.g., someone pasted everything as values). Add a check at the start of RunFullAudit that counts formula cells across the workbook and warns if the count is zero.
DirectPrecedents raises error 1004. This happens when a cell's formula references a closed workbook. The On Error Resume Next guard should catch it, but if you're seeing it bubble up, double-check that the guard is in place before every .DirectPrecedents call and that you're not calling the precedent tracer on cells whose HasFormula property returns False.
The regex pattern flags cell references like A1 as hardcodes. This usually means the lookbehind (?<![A-Z:$]) isn't being applied correctly, which can happen if the formula has unusual spacing or non-standard characters. Add a debug line that prints the formula to the Immediate Window (Debug.Print formula) for any cell that generates an unexpected false positive, and tune the pattern accordingly.
Performance is very slow on large workbooks. The precedent tracer is the most expensive component because it can trigger recalculations internally. Beyond setting Calculation = xlCalculationManual, consider adding a cell count guard: if the used range on a sheet exceeds 50,000 cells, skip precedent tracing for that sheet and log a finding that says "Precedent tracing skipped — sheet too large for automated analysis."
The report sheet keeps triggering its own audit. The line If ws.Name <> REPORT_SHEET Then guards against this, but if you rename the constant REPORT_SHEET without updating it everywhere, the guard will fail. Use the constant consistently rather than hard-coding the string "AUDIT_REPORT" anywhere else in the codebase.
Warning: Never run this tool with Application.EnableEvents = False and then close the workbook without restoring it. That leaves Excel in a broken state for all subsequently opened workbooks. Always wrap the audit runner in proper error handling that restores Excel settings even on failure. The error handling and debugging lesson covers the On Error GoTo cleanup pattern you should implement here.
The core tool we built handles the most common audit needs, but real-world financial models have additional quirks worth addressing as you extend it.
Named range validation. Professional models use named ranges for all key assumptions. If you want to enforce this, add a check that scans ActiveWorkbook.Names and flags any formula that references an absolute address (like $B$4) on an Assumptions sheet without going through a named range. This is a higher-order check that separates institutional-quality models from ad-hoc spreadsheets. The Name Manager lesson covers the programmatic side of working with named ranges.
Version-stamping the report. If you're running audits repeatedly as a model evolves, add a version number or commit hash to the report header, and archive previous reports to a new sheet named AUDIT_REPORT_v2, AUDIT_REPORT_v3, and so on. This creates an audit trail.
Packaging as an Add-In. Once the tool is stable, package it as an .xlam add-in so your team can run it against any model without having to copy modules. The lesson on building Excel add-ins with VBA walks through the packaging and deployment process end to end.
Summary & Next Steps
You've built a four-module VBA system that does what no point-and-click Excel feature can: systematically crawl a financial model, apply configurable quality rules, trace formula dependencies recursively, and produce a structured, formatted report that gives reviewers a prioritized action list.
The key architectural ideas to carry forward are:
- Separate concerns into modules. Detection logic, structural checking, and report writing are independent enough that you can extend or replace any one without touching the others.
- Use data structures, not cells, as your scratchpad. Accumulating findings in a typed array and writing them to the sheet once is far faster than writing each finding as you find it.
- Normalize before comparing. The
NormalizeFormula function is a small piece of code that unlocks an entire class of structural checks that would be impossible without it.
- Respect Excel's calculation engine. Disabling screen updating and auto-calculation isn't optional polish — it's the difference between a 10-second audit and a 3-minute one.
Where to go next: if you want to add scenario-based testing to the models you audit — not just checking that formulas are correct, but verifying that outputs change as expected when inputs change — look at Advanced What-If Analysis with Scenario Manager, Goal Seek, and Solver. And if you want to build automated tests that verify your audit tool itself doesn't regress as you extend it, the VBA testing framework lesson will show you how to unit-test VBA code the right way.
The next time someone hands you a black-box model and says "can you sign off on this?", you'll have a tool that does the mechanical scanning in seconds — leaving your attention free for the judgment calls that actually require a human.
