Recording a Macro

Excel writes the code for you

The Macro Recorder watches what you do in Excel and writes the equivalent VBA into a module. You do not have to know any syntax to use it — which makes it the fastest way to discover how something is expressed in code.

How to record

• Start — Developer → Record Macro, or the small record button on the status bar.
• Name it — no spaces; FormatReport, not Format Report.
• Store it in — This Workbook for a macro belonging to this file, or Personal Macro Workbook to have it available in every file you open.
• Do the steps — carry out the task exactly as normal.
• Stop — Developer → Stop Recording, or the stop button on the status bar.

Then go and look at it

Press Alt+F11, open the module in the Project Explorer, and read what Excel wrote. That reading step is the whole point: recording without ever opening the code teaches you nothing and leaves you with a macro you cannot fix.

What gets recorded and what does not

Almost every action on the sheet is captured: typing, formatting, sorting, filtering, inserting sheets, changing page setup. What is not captured is anything conditional or repetitive — the recorder cannot produce an If statement or a loop, because it only sees what you did once, not why. Scrolling and simply looking at cells produce nothing.

Plan the take

The recorder captures your mistakes and your corrections as faithfully as the real steps, so it is worth rehearsing. Decide where the cursor should start, do the task once for practice, then record a clean run without wandering off to check other sheets.

It records against the wrong file

A recorded macro often refers to whichever workbook and sheet you happened to be in, by name. Move the file or rename the tab and the macro fails. Fixing that is what the rest of this course is about.