The VBA Macro Recorder in desktop Excel turns a sequence of actions into Visual Basic for Applications (VBA) code that you can run again. In Microsoft 365 for Windows, choose Developer > Record Macro, perform the steps you want to repeat, and choose Developer > Stop Recording; the recorder is a starting point, not a way to capture every kind of Excel task.
What the VBA Macro Recorder does
Excel’s recorder observes many actions you take in a workbook—such as selecting cells, entering values, applying formatting, and using some commands—and writes corresponding VBA statements. You can then replay that procedure to repeat the recorded actions. A macro is the saved set of instructions; VBA is the programming language those instructions use.
For example, if you regularly format a report header, you could record selecting a fixed range, applying bold text, setting a fill color, and adjusting a column width. Excel creates a macro that performs those operations. The result can save time on repetitive work, and you do not need to write the initial code yourself.
The recorder does not understand your goal in the way a person does. It records operations, often including selections and screen-specific details, rather than inferring a general rule such as “format every new report header.” Consequently, a recorded macro may work well on the original layout but need editing before it can handle different ranges or changing data.
How to record a macro in Excel for Windows
Before recording, open the workbook you intend to use and decide what the macro should do. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, select the Developer check box, and choose OK.
- Choose Developer > Record Macro.
- Enter a descriptive name. Macro names cannot contain spaces; use a name such as FormatReport rather than Format Report.
- Optionally enter a shortcut key and a description. Be cautious with shortcuts: assigning a Ctrl combination can replace the usual Excel shortcut while the workbook or macro is available.
- Choose where to store the macro. This Workbook keeps it with the current file; Personal Macro Workbook makes it available in Excel on that computer; New Workbook stores it in a separate workbook.
- Carry out only the actions you want the macro to repeat. Avoid unrelated clicks and edits, because they may become part of the procedure.
- Choose Developer > Stop Recording when you are finished.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), or use another macro-capable format such as Excel Binary Workbook (.xlsb). If you save it as a standard .xlsx file, Excel does not preserve its VBA project. Keep an appropriate copy before testing or running a macro: macro actions cannot be undone with Ctrl+Z.
Absolute and relative references
One important recorder setting determines whether cell references are absolute or relative. With absolute references, a recorded instruction commonly points to the same cells every time, such as selecting A1:C1. If you run that macro while another cell is active, it may still act on A1:C1. That can be useful for a fixed title area, but it is brittle when the report moves or grows.
Choose Developer > Use Relative References before recording when the actions should be performed in relation to the active cell. For instance, you might record formatting the current cell and the two cells to its right, then run the macro from another starting position. The setting is a toggle, so check whether it is on or off before you begin. Relative recording changes how references are captured; it does not make a macro automatically detect the end of a data table.
Think about the intended starting point before choosing. If the macro should always format the same cells, absolute references may be appropriate. If it should repeat a pattern from wherever you start, relative references may fit better. Test the macro from a different starting cell or on a copy of the sheet to confirm that its behavior matches your expectation.
Where the code goes and how to inspect it
Excel stores recorded instructions in a VBA procedure, usually in a standard module in the workbook’s VBA project. To inspect it on Windows, choose Developer > Visual Basic, or press Alt+F11. In the Visual Basic Editor, find the workbook in Project Explorer and open the module containing the procedure. If Project Explorer is not visible, use View > Project Explorer in the editor.
Recorded code often includes commands such as selecting a range, activating a worksheet, and then changing a property of the selection. That style is understandable for a first recording, but it can be fragile: a renamed sheet, a different active workbook, or a changed layout can cause errors or unwanted edits. Reading the code helps you spot what the macro will affect before running it.
A short, edited procedure can be easier to maintain than a long recording. This example applies a number format to a fixed range. It assumes a worksheet named Summary exists; change the sheet name and range to match your workbook.
Sub FormatSummaryAmounts()
' Format the amounts in the Summary sheet.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")
ws.Range("B2:B20").NumberFormat = "$#,##0.00"
End Sub
This example is ordinary VBA, not a claim about what the recorder will produce for every version or action. Before running any macro, save a copy of the workbook. VBA macros cannot be reversed with Ctrl+Z, and a mistaken procedure can alter many cells quickly.
Running, saving, and sharing a recorded macro
To run a macro in Excel for Windows, choose Developer > Macros, select its name, and choose Run. You can also run it from the Visual Basic Editor or assign it to a supported shortcut or a worksheet button. A button is often more discoverable for colleagues than a keyboard shortcut, but document what it does so users know what will happen when they click it.
Where you store the code affects who can use it. A macro stored in This Workbook travels with that macro-enabled workbook. A Personal Macro Workbook is intended for macros you want available in your Excel installation, and Excel loads it in the background when it starts. It is local to that installation, so it does not automatically follow the workbook to another person’s computer.
When sharing an .xlsm or .xlsb file, tell recipients why the macro is included and what it changes. Their Excel security settings may block it or display a warning. A workbook that contains a macro does not mean that the code is safe; users should only enable macros from a source they trust and should inspect unexpected files through their organization’s security process.
Common limitations and troubleshooting
- The macro is missing after saving: Check that you saved in a macro-enabled format such as .xlsm or .xlsb. A regular .xlsx file cannot retain VBA code.
- The macro changes the wrong cells: Check whether the recording used absolute or relative references, and inspect the procedure for fixed ranges, sheet names, or selections.
- It works only on one worksheet: The recorded code may refer to a particular sheet or rely on whichever sheet is active. Verify worksheet names and the workbook being targeted.
- The macro will not run: Excel may have disabled macros because of security settings, or the file may have been opened in a context that does not run VBA. Do not lower security protections just to run an unknown macro.
- The recording contains too many steps: Record again with a clean sequence, or edit the code to remove unnecessary selections and repeated actions. A recorder captures the operations you perform, not just the ones you intended.
Some Excel features and interactions are not recorded as a useful, repeatable VBA procedure. The recorder is especially suited to straightforward interface operations; complex decisions, loops, data validation logic, and automation across changing workbooks generally require VBA knowledge or another automation tool. When a macro fails, the exact error message and the highlighted line in the Visual Basic Editor are useful clues, but do not run unfamiliar code repeatedly on important data.
Excel for Mac and Excel for the web
Excel for Mac also supports VBA macros and includes a Developer tab, although the available commands and some VBA capabilities can differ from Windows. If Developer is hidden, enable it in Excel’s ribbon or settings for the Mac version, then look for the macro recording and VBA commands on that tab. Keyboard shortcuts can differ by keyboard and macOS settings; the Windows Alt+F11 shortcut should not be assumed to work identically on a Mac.
Excel for the web can open and edit many workbook contents, but it cannot create, edit, or run VBA macros. To work with VBA, open the file in the desktop Excel application. Microsoft 365 also offers Office Scripts for certain cloud-based automation scenarios, but Office Scripts are TypeScript-based and are not VBA; their availability and capabilities are distinct from the Macro Recorder.
When to use the recorder—and when not to
The recorder is a good choice when a task is repetitive, consists mostly of ordinary Excel commands, and uses a stable layout. It can also be a practical learning tool: record a small operation, inspect the generated code, and see how Excel represents formatting or worksheet changes. Keep the first recording short so it is easier to understand and test.
Consider another approach when the task must adapt to changing row counts, make decisions based on cell values, process files in a shared online workflow, or run across many users and platforms. Possible options include:
- Excel formulas: Useful when the goal is to calculate or transform values and the result should update as data changes.
- Power Query: Useful for repeatable importing, cleaning, and reshaping of data without relying on recorded mouse actions.
- Office Scripts: A different automation option for supported Microsoft 365 environments, particularly where browser-based workflows are relevant.
- Manually written VBA: More suitable when desktop Excel automation needs conditions, loops, or dynamic ranges that the recorder cannot express cleanly by itself.
These options are not interchangeable in every situation. Choose based on where the workbook will run, who needs to maintain the automation, and whether it must work with VBA-enabled desktop Excel or with a cloud workflow.
Frequently Asked Questions
Is the VBA Macro Recorder the same as VBA?
The recorder is an Excel feature that generates VBA code from many user actions. VBA is the programming language used to write, inspect, and edit that code.
Can I record a macro in Excel for the web?
No. Excel for the web does not create or run VBA macros. Open the workbook in desktop Excel to use VBA; Office Scripts are a separate feature for supported environments.
Why does my recorded macro select the wrong cells?
It may contain absolute cell references, or its recorded actions may depend on a particular active sheet or starting cell. Check the Use Relative References setting and inspect the VBA procedure.
Can I undo a macro with Ctrl+Z?
No. Excel clears its normal undo history when a VBA macro runs, so Ctrl+Z cannot reverse the macro’s changes. Save a copy before running it.
What file format keeps a macro?
Save a workbook containing VBA as .xlsm or another macro-capable format such as .xlsb. Saving as .xlsx removes the VBA project from the saved file.
Related
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Code: How to Write, Run, and Use It SafelyExcel macro code is usually written in Visual Basic for Applications (VBA), a language that automates tasks in…
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- Excel VBA Copy and Paste Formatting Without Changing ValuesTo copy formatting with Excel VBA without copying cell values or formulas, copy the source range and paste…
- VBA Dictionary With Multiple Values: Practical MethodsYes. A VBA Scripting.Dictionary can hold multiple values for one key, but each key still maps to a…
- VBA Sort Cells in Excel: Code, Options, and Common FixesTo sort cells with VBA in Excel, sort the entire data range by a key column—not just one…
- Excel VBA: Add a New Worksheet With a Specific NameIn Excel VBA, add a worksheet with Worksheets.Add, then set its Name property. For example, the key steps…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.