Concept
1. Turn on the Developer tab
File → Options → Customize Ribbon → tick Developer → OK. (Mac: Excel → Preferences → Ribbon & Toolbar.)
2. Record your first macro
Task: format a header row — bold, blue fill, white text.
- Click A1.
- Developer → Record Macro.
- Name:
FormatHeader(no spaces; letters, numbers, underscores). - Shortcut:
Ctrl + Shift + H(avoid plain Ctrl + letter — it overrides Excel shortcuts like Ctrl + C). - Store macro in: This Workbook.
- OK → now do the formatting → Developer → Stop Recording.
Run it on another sheet: Developer → Macros (Alt + F8) → select → Run, or press your shortcut.
Personal Macro Workbook stores macros in a hidden file (PERSONAL.XLSB) that opens with Excel, so they work in every file. Good for your own utilities; not for macros you share.
3. Absolute vs relative recording
By default, the recorder saves exact addresses: "select A1:E1". Run it anywhere — it always formats A1:E1.
Turn on Use Relative References (Developer tab) before recording, and it saves movements: "from the active cell, select 5 cells to the right". Now it works on whichever row you're on.
| Mode | Recorded code looks like | Use when |
|---|---|---|
| Absolute (default) | Range("A1:E1").Select |
always the same cells |
| Relative | ActiveCell.Resize(1, 5).Select |
"do this at the current position" |
4. Save as .xlsm
A normal .xlsx file cannot store macros. If you save as .xlsx, Excel warns and deletes them.
File → Save As → Excel Macro-Enabled Workbook (*.xlsm). .xlsb (binary) also keeps macros.
5. Trust settings
File → Options → Trust Center → Trust Center Settings → Macro Settings.
| Setting | Meaning |
|---|---|
| Disable VBA macros without notification | macros never run |
| Disable VBA macros with notification (recommended) | yellow bar "Enable Content" appears |
| Enable all macros | dangerous — any file can run code |
Files from the internet or email carry a "Mark of the Web", and Microsoft 365 blocks their macros completely (red bar). If you trust the file: close it → right-click → Properties → tick Unblock → OK.
Trusted Locations (Trust Center): put your own macro files in a trusted folder and they open without warnings.
Only enable macros in files you trust. A macro can delete files or send data anywhere.
6. What the recorder can't do
It records clicks, not decisions. No loops ("do this for every row"), no conditions ("if stock < 5"), no "find the last row". That's why we learn to edit the code in the next lessons.
Common mistakes
Saving as .xlsx and losing the macros. Recording with absolute references when you wanted relative. Using Ctrl + C or Ctrl + V as a macro shortcut. Enabling all macros globally.