To record a macro in Excel, enable the Developer tab, click Record Macro, perform the steps once, then click Stop Recording. To read the code it produced, press Alt+F11 to open the Visual Basic editor and look in Modules. The recorder writes real VBA, so recording a task and reading the result is the fastest way to see what the code for that task actually looks like.
It is also where most people’s VBA starts and stops, because recorded code is verbose, brittle and full of habits worth unlearning early. This covers both halves: how to record, and how to read what you get.
Turning on the Developer tab
The Developer tab is hidden by default. Go to File → Options → Customize Ribbon, tick Developer in the right-hand list, and click OK. It stays on for every workbook from then on.
Recording your first macro
1 On the Developer tab, click Record Macro.
2 Give it a name with no spaces, such as FormatHeader. Spaces are not allowed in macro names.
3 Choose where to store it. This Workbook keeps the macro in this file. Personal Macro Workbook makes it available in every workbook you open on that computer.
4 Click OK, do the steps you want to capture, then click Stop Recording.
Save as .xlsm or lose the code
A workbook containing macros must be saved as .xlsm. If you save it as .xlsx, Excel warns you once and then discards every macro in the file. There is no way to recover them afterwards.
Reading what the recorder wrote
Press Alt+F11, expand Modules in the Project pane on the left, and double-click Module1. Recording “make A1:D1 bold with a light blue fill” produces something close to this:
Sub FormatHeader()
Range("A1:D1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
End Sub
Every line is genuine VBA and it does work. It is just far more than the job needs.
Why recorded code is longer than it needs to be
The recorder cannot tell which part of what you did was the point. When you set a fill colour it captures every property on that dialog, including the ones you never touched, which is where .PatternTintAndShade = 0 comes from.
It also records selecting as a step, because that is what you physically did. Written by hand, the same job is three lines:
Sub FormatHeader()
With ThisWorkbook.Worksheets("Sheet1").Range("A1:D1")
.Font.Bold = True
.Interior.Color = RGB(221, 235, 247)
End With
End Sub
Shorter, and it names the sheet rather than relying on whichever one happens to be active when it runs.
The recorder is a reference, not a writing tool
Its real value is answering “what is this thing called in VBA?”. Record the action, read the property name it used, then write the version you actually want. Professionals use it that way for years without ever shipping recorded code.
Where your macro ended up
If you chose This Workbook, the macro travels with the file and anyone who opens it gets it, which also means they will see the macro security warning.
If you chose Personal Macro Workbook, Excel created a hidden file called PERSONAL.XLSB that opens invisibly every time you start Excel. That macro is available everywhere on your machine, but it does not travel with the workbook, so a colleague opening the same file will not have it.
Common questions
Why is Record Macro greyed out?
Usually because the sheet or workbook is protected, or a cell is still in edit mode. Press Escape, then check Review → Unprotect Sheet.
Can I edit a recorded macro?
Yes. It is ordinary VBA in a normal module, so you can rename it, delete lines and rewrite it. Editing recorded code is the usual way people learn to write it.
Why does my macro only work on one sheet?
Recorded code often refers to ActiveSheet or an unqualified Range, which means “whatever sheet is in front right now”. Name the sheet explicitly, as in ThisWorkbook.Worksheets("Data").Range("A1"), and it works regardless of what is selected.
Does the recorder capture everything I do?
No. It captures actions on the workbook. It does not record anything conditional, so it cannot produce “if the value is over 100, do this instead”. That kind of logic has to be written.
Can I record a macro in Excel for Mac?
Yes. The Developer tab is enabled under Excel → Preferences → Ribbon & Toolbar, and recording works the same way. Some Windows-only features still will not run on a Mac, which is covered in does Excel VBA work on Mac.
When Formulas Aren’t Enough
Recording gets you started. The jobs that need writing rather than recording tend to look like this:
- Decisions — the steps change depending on what the data says
- Repetition across files — the same process over dozens of workbooks
- Error handling — carrying on sensibly when a file is missing or a value is wrong
- Anything a user touches — buttons, prompts and validation the recorder cannot produce
A custom VBA tool handles all four, and keeps working when the data changes.
Automating the Same Task Every Week?
Tell us what the process is and we’ll build a tool that runs it in one click.
Start a Project