VBA I — Recorder to Real Code

Learning objectives

By the end you can:

1. Record a macro honestly, use Developer ▸ Record Macro to capture a task, then open the result in the VBE and read it, and explain why the recorder's output is a starting point, never a finished program.
2. Navigate the Visual Basic Editor fluently: the Project Explorer, the Properties window, a standard code module, and above all the Immediate window, in which you can run one line of code and see its answer at once.
3. State the Excel object model hierarchy (Application ▸ Workbook ▸ Worksheet ▸ Range/Cells) and read a dotted expression like ThisWorkbook.Worksheets("Sales").Range("B6") as a path down that tree.
4. Write a complete Sub procedure, address cells with both Range("A1") and Cells(row, column), and choose the right one for a fixed reference versus a looped or computed one.
5. Refactor recorder output, mechanically removing every .Select/.Activate/Selection/ActiveCell and replacing it with a direct, explicitly-qualified object reference.
6. Collapse repeated object references with a With block, and explain the readability and (minor) speed reasons it is preferred.
7. Declare variables with Dim and the core types (Long, Double, String, Range, Variant) and say what each is for and what it costs.
8. Adopt the Option Explicit law, and describe precisely the class of silent bug (the mistyped variable that becomes an empty Variant) it eliminates.
9. Configure macro security through the Trust Center, save a workbook in the .xlsm (or .xlsb) macro-enabled format, and run a macro five ways: F5, Alt+F8, a worksheet button, the Quick Access Toolbar, and the Immediate window.

Prerequisites & connections

Builds on. The entire formula and dynamic-array stack of Phase EX so far. EX0.01's Table discipline (Ctrl+T), EX1.02's lookups, and especially the mental model that a workbook is a system of references rather than a pile of typed numbers. VBA takes that same "never hardcode what can be fetched" ethic and pushes it one level deeper: instead of writing a formula in a cell, you write code that writes the formula, or that reads the cells and computes directly. You also need comfort reading a cell address (B6, $A$2:$E$5) and knowing what SUM does, because your first macros will reproduce, in code, things you already do in formulas, which is exactly why they are a gentle on-ramp.

Feeds forward. This is VBA I; it opens the automation track. EX6.02 (VBA II, control flow) turns the single straight-line Subs here into programs that make decisions (If/Select Case) and repeat (For/For Each/Do) with intent; EX6.03 (VBA III, event handling and forms) makes code run automatically when a sheet changes or a workbook opens, and adds buttons and user forms. The With-block and explicit-reference habits you build now are the difference between VBA that scales and VBA that collapses under its own .Selects. On the analyst track, macros are how you industrialise a model: M3.08's three-statement model can be refreshed and re-scenario'd by a macro; the monthly reporting pack of EX7.02 is a recorded-then-refactored formatting routine; and the "one button rebuilds the deck" reflex that senior analysts prize starts with the record → read → refactor loop of this very node. Everything downstream assumes you can write a clean, explicitly-qualified Sub without a single .Select in it.

The macro recorder, honestly

First principles. The recorder answers one question: "what VBA corresponds to the thing I just did by hand?" You turn it on, you perform a task with the mouse and keyboard, you turn it off, and Excel has written (into a module in the VBE) the VBA statements that reproduce your actions. This is genuinely miraculous as a discovery mechanism. You do not need to memorise that the fill colour lives at Range.Interior.Color or that bold is Font.Bold; you record yourself doing it once and the recorder tells you the property names. Used this way (to find out which object and which property Excel reaches for) it is the single fastest way to learn the object model.