The Analyst's Path

Phase 6 · Investing philosophy, mental models and behavioural edge · free

VBA IV — Automation Capstones

EX6.04 · 11,159 words

You have spent three modules earning the right to be here. VBA I taught you to read the recorder's output and rewrite it as clean object code; VBA II gave you logic, loops, Dictionary objects, your own worksheet functions, and the On Error family; VBA III put…

Read the whole module in the app All Phase 6 modules

Learning objectives

By the end you can:

  1. Build a one-click report generator that formats a report range and exports one or more sheets as a single PDF using ExportAsFixedFormat, with a dated, idempotent filename and an output folder it verifies (and creates) first.
  2. Write the canonical cleanup handler: capture prior ScreenUpdating / Calculation / EnableEvents state, enter fast mode, do the work under On Error GoTo Cleanup, and restore every setting (and the StatusBar) in the label so a crash never leaves Excel wedged.
  3. Build a folder consolidator that iterates a directory with Dir, opens each workbook read-only, reads its total, appends a row to a master table, writes a grand total, and closes each source without saving.
  4. Make any appending automation idempotent: clear or key the target so a second run reconciles to the same result instead of double-counting.
  5. Build a model-checker that flags formula cells containing hardcoded numbers (by stripping cell-reference tokens and testing for a residual numeric literal), enumerates error cells via SpecialCells(xlCellTypeFormulas, xlErrors), and lists broken external links from LinkSources.
  6. Explain and prevent the model-checker's characteristic false positives, legitimate constants like 0, 1, dates, and digits inside function names or TRUE/FALSE.
  7. Package utilities into a Personal Macro Workbook (PERSONAL.XLSB in XLSTART) and a distributable .xlam add-in, and expose a macro through a ribbon callback or a Quick Access Toolbar button, understanding that an add-in acts on ActiveWorkbook, not ThisWorkbook.
  8. Sign a macro project (self-signed for your own machine, a CA-issued code-signing certificate for the organisation), and explain Trusted Publishers, Trusted Locations, and why an unsigned macro is blocked by a locked-down security policy.
  9. Add progress feedback (Application.StatusBar) and provide meaningful failure messages, so a long-running tool tells the user what it is doing and what went wrong.

Prerequisites & connections

Builds on. This is the fourth and final VBA module, and it assumes all three before it. From EX6.01 (VBA I. Recorder to Real Code) you bring Option Explicit, the Workbook/Worksheet/Range object model, With blocks, and the reflex to avoid .Select. From EX6.02 (VBA II (Logic, Functions & Events) you bring If/Select Case, For Each, Dictionary, string handling, your own UDFs, and) most importantly here, the On Error GoTo / Err object error-handling patterns that the cleanup handler is built from. From EX6.03 (VBA III. UserForms, Files & Speed) you bring the Dir folder loop, Workbooks.Open, the FileSystemObject, and the speed trio (ScreenUpdating, Calculation, EnableEvents) that every capstone here toggles. You also need the analyst's habit, drilled since EX0.02, of never hardcoding a number that should be an input, because Capstone 3 is a machine that hunts exactly that sin.

Feeds forward. The two black-belt modules consume this one directly. EX7.01 (Professional Modeling Standards) formalises the inputs/calcs/outputs layout and the "checks & flags" row; the model-checker you build here is the automated enforcer of those standards, and its hardcode hunt is the mechanical version of EX7.01's inputs-vs-hardcode audit. EX7.02 (Excel Capstones, the Analyst's Stack), the Excel black belt, asks for a "VBA one-click refresh-and-print" over a live three-statement model and a Power Query portfolio tracker, that is Capstone 1 of this module, grown up. On the analyst path, every model from M3.08 (three-statement) onward benefits from a model-checker pass before you trust its output, and the consolidator is the pattern behind any "roll up the regional books" or "combine the broker statements" task you will ever be handed. Learn these as reusable spines, not one-off scripts.

Core teaching content

A note on how to read the code. Every listing below is complete and runnable, paste it into a standard module (Alt+F11 → Insert ▸ Module) in a macro-enabled workbook and it works. Each starts with Option Explicit implied (declare it once at the top of every module, it is law from VBA I). Paths and sheet names are written as constants at the top so you change them in one place. Read each listing twice: once for what it does, once for how it protects itself when something goes wrong.

This page is an excerpt

The full module runs to 11,159 words and carries the worked examples, the tables, the quiz that gates the next module and the spaced-repetition deck built from it. All of it is free and none of it needs an account.

Read the whole module All 228 modules
Previous: VBA III — UserForms, Files & Speed Next: Professional Modeling Standards