Excel macro recorder

The recorder answers one question: "what VBA corresponds to the thing I just did by hand?" You turn it on, do a task with the mouse and keyboard, and turn it off. By then Excel has written the VBA statements that reproduce your actions into a module in the VBE. As a discovery tool, that's close to miraculous. You don't need to memorise that the fill colour is Range.Interior.Color or that bold is Font.Bold. Record yourself doing it once and the recorder tells you the property names. Use it like that, to find out which object and which property Excel reaches for, and it's the fastest way there is to learn the object model.

First, make the Developer tab visible. Go to File ▸ Options ▸ Customize Ribbon and tick Developer in the right-hand list. You only ever do this once. To record, choose Developer ▸ Record Macro and a dialog opens. Name the macro (no spaces, and start with a letter), assign a shortcut if you want one, and choose where to store it (This Workbook for now). Click OK, do your task, then Developer ▸ Stop Recording. To see what it wrote, open the editor with Developer ▸ Visual Basic or the shortcut Alt+F11, and look in the module under Modules in the Project Explorer.

Why is recorded code never finished?

So why is the output never finished? Because the recorder writes down everything you did, in the order you did it, navigation included. Click cell A1 and make it bold, and the recorder writes two statements. One selects A1 and the other bolds the selection. A person would write one statement that bolds A1 directly. Multiply that across a real task and the recording turns into a slow, brittle monologue of .Select / Selection pairs. They run slowly, because every select repaints the screen, and they break the instant the sheet isn't the active one. Come back to them six months later and you'll barely be able to read them. The recorder can't use variables, can't make decisions and can't loop. It can't qualify a reference to a specific sheet either. All it knows is "the thing that is selected right now."

How do you turn a recording into clean code?

Here's the loop professionals actually run:

1. Record the task once, and accept the ugly output.
2. Read the generated code and find the real work buried in the navigation noise. That's the two or three property assignments that actually matter.
3. Refactor. Delete every .Select, Selection, .Activate and ActiveCell. Attach each property directly to an explicitly-named Range on an explicitly-named Worksheet. Then bring in variables, and loops once you've learned them.

You'll do this in Worked example 1, turning a recorder's .Select-riddled formatting macro into six clean lines. The recorder found the property names for you. Your job is to write the sentence properly.