The Excel VBA macro recorder turns many actions you perform in a workbook into VBA code that you can run again. In Microsoft Excel for Microsoft 365 on Windows, open the Developer tab, choose Record Macro, perform the task, and choose Stop Recording; then review the generated code before relying on it.
What the Excel VBA macro recorder does
A macro is a set of instructions that automates work in Excel. VBA, or Visual Basic for Applications, is the programming language Excel uses for many desktop macros. The recorder provides a way to create basic VBA without writing each instruction yourself: it observes supported actions, such as selecting cells, changing formatting, or entering values, and translates them into code.
The recorder is useful for turning a repeatable sequence into a starting point for automation. For example, if you regularly format a report’s heading row, apply number formats, and adjust column widths, you can record one pass and run the resulting macro on similar workbooks. The recording is code, not a video or a saved sequence of mouse clicks: Excel translates actions into operations on workbook objects.
A recorded macro is not necessarily a complete, flexible program. It generally reproduces the actions and references captured during recording. It does not automatically understand your goal, decide what to do with new data, or create a loop just because you repeated the same action several times.
How recording works
When recording is active, Excel monitors supported commands and writes corresponding VBA instructions. If you select a range and make its font bold, the code might refer to that range and set its Font.Bold property. If you type a value, the code may assign it to a cell’s Value property. Excel may also write instructions to select objects before changing them.
This explains why a recorded macro can be more fragile than its simple appearance suggests. If it refers to cell B2, it may keep working on B2 even when the next report’s data starts in B3. If it uses ActiveSheet, it acts on whichever sheet is active when you run it. If it selects a range, a different selection or workbook context can affect what happens. The code records operations, but the person using it must check the assumptions.
Record a macro in Excel for Windows
If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select the Developer check box. To make a basic recording:
- Open the workbook and, if appropriate, select the cell or range where the task should begin.
- Choose Developer > Code > Record Macro.
- Enter a macro name. Use a name that starts with a letter and contains no spaces; underscores can separate words. Choose where to store the macro and, if useful, enter a description.
- Optionally assign a keyboard shortcut. Choose it carefully because a shortcut can take precedence over an existing Excel shortcut while the macro is available.
- Perform only the actions you want repeated. Avoid unrelated selections and corrections that would become part of the recording.
- Choose Developer > Code > Stop Recording when the task is complete.
The Store macro in menu offers choices such as This Workbook, New Workbook, and Personal Macro Workbook. This Workbook keeps the macro with the current file. The Personal Macro Workbook is intended for macros you want available in Excel on that computer; it is not automatically included when you send an ordinary workbook to someone else. A macro saved in a separate new workbook is stored with that workbook instead.
Excel for Mac also supports VBA and macro recording, though some commands and object-model features differ from Windows. If needed, show the Developer tab in Excel > Settings > Ribbon & Toolbar, then use the recording controls on that tab. Names and settings can vary somewhat between Mac releases. Excel for the web can open workbooks containing macros, but it cannot create, run, or edit VBA macros; use the desktop app for those tasks.
Absolute and relative references
One important recording choice is whether cell references should be absolute or relative. By default, a recorded action may point to the same address each time—for instance, cell C5. That is suitable if the task always applies to a fixed location. It is unsuitable if you want the macro to operate relative to wherever you start.
In Excel for Windows, Developer > Code > Use Relative References toggles relative recording. Turn it on before recording when the operation should follow the active cell. For example, a macro that formats the cell one column to the right of the starting cell can be recorded relative to that start, rather than always targeting one fixed column. Turn it off to return to absolute references. Check the button’s state before recording; otherwise the macro may work in the wrong place without displaying an error.
Relative references are not a substitute for reviewing the code. The final result depends on the active cell and on how Excel recorded the actions. Try the macro on a copy and on a small, representative range before using it on a full report.
Read and improve the recorded VBA
To inspect a recorded macro in the Windows desktop app, choose Developer > Code > Macros, select its name, and choose Edit. This opens the Visual Basic Editor. You can also open the editor with Alt+F11 on Windows; on many Mac keyboards, Option+F11 opens it, sometimes with the Fn key as well. A recorded procedure typically appears between a Sub line and End Sub.
Recorder output often includes Select and Selection statements because Excel is translating interactive steps. Those lines are not always needed. For example, instead of selecting a range and then formatting the selection, VBA can apply formatting directly to the range. The following is a compact example of that style. It formats A1:D1 on the active sheet:
Option Explicit
Sub FormatHeader()
Dim ws As Worksheet
Set ws = ActiveSheet
' Format the heading cells on the active sheet.
With ws.Range("A1:D1")
.Font.Bold = True
.Interior.Color = RGB(217, 225, 242)
End With
End Sub
This example is not a promise that the recorder will produce precisely these lines; it illustrates how a recorded formatting task can be made more direct. The code assumes the intended worksheet is active and the heading occupies A1:D1. Change those references if the workbook layout differs. Before running any macro, save a copy of the workbook: macro actions cannot be reversed with Ctrl+Z.
Run, edit, and save a macro
In desktop Excel for Windows, run a macro from Developer > Code > Macros: select it and choose Run. You can also use a shortcut assigned during recording. The macro list may show macros available in the current workbook and other open macro-enabled locations. Before running one, confirm the correct workbook and worksheet are active, especially if the code uses ActiveWorkbook, ActiveSheet, Selection, or unqualified cell references.
If a workbook contains VBA, save it in a macro-capable format such as Excel Macro-Enabled Workbook (*.xlsm). Saving a workbook with VBA as the standard .xlsx format removes its VBA project; Excel warns about this during saving. A macro in the Personal Macro Workbook is stored separately, so saving the current report as .xlsx does not put that personal macro inside the report.
Editing the code can make a recording more reliable. Common improvements include replacing a fixed range with the actual data range, adding checks before overwriting cells, and removing unnecessary selections. Make one change at a time and test on a separate copy. A macro can alter or delete data quickly, and its effects are not covered by Excel’s normal undo history.
What the recorder cannot do well
The recorder is strongest when the task is a stable sequence of supported Excel commands. It is less suitable when the workbook must make decisions based on changing values, repeat actions for an unknown number of rows, handle errors, or interact with systems outside Excel. Those requirements usually call for editing the VBA or writing a procedure directly.
- Changing data layouts: recorded fixed addresses may miss new rows or columns.
- Conditional logic: a recording does not infer rules such as “format only overdue items.” Add VBA logic or use a built-in feature such as conditional formatting.
- Repetition over variable data: recording the same action several times generally creates several instructions, not a flexible loop.
- Unsupported or context-dependent actions: some interface operations do not translate cleanly into VBA, and actions involving dialogs or add-ins may depend on the Excel version or setup.
Some operations are better handled without a macro. For repeatable data reshaping, Power Query may be a better fit; for calculations, formulas or PivotTables may be easier to inspect; for appearance rules, conditional formatting may update automatically as values change. The appropriate choice depends on whether the task is a fixed sequence, a data transformation, a calculation, or a rule that should remain active.
Safety and macro security
VBA can change workbook contents and, depending on the code, interact with files or other applications. Do not run a macro merely because a workbook looks familiar. If you receive a file from someone else, verify its source and inspect the code or ask the sender what it does. A macro-enabled file extension does not prove that a file is safe.
On Windows, macro controls are available under File > Options > Trust Center > Trust Center Settings > Macro Settings. The available policies depend on the organization’s configuration. Avoid enabling all macros globally; use the least permissive setting that supports the work you trust, and follow workplace IT guidance. Excel may also block macros in files from the internet. Do not bypass a warning unless you have verified the source and understand the file’s purpose.
For your own macros, keep a clean original workbook and test on a copy. Review any code that deletes rows, clears ranges, overwrites formulas, or saves changes automatically. Consider documenting the expected sheet names, starting cell, and input format so another person can tell whether the macro is appropriate for their workbook.
Macro recorder versus writing VBA
The recorder is a practical learning aid: it exposes the VBA statements associated with ordinary Excel actions and can supply a first draft. Handwritten VBA is usually more adaptable when the job needs loops, conditions, error handling, clear references to specific workbooks, or input checks. A useful workflow is to record a small part, inspect what Excel generated, then simplify or extend it.
Neither approach is automatically safer. A short handwritten macro can still damage data, and a long recording can be harmless if it only formats a known range. Judge a macro by what it actually does, the workbook context it assumes, and how it has been tested—not by whether it was recorded or typed.
Frequently Asked Questions
Is the Excel macro recorder the same as VBA?
No. The recorder is a tool that creates VBA code from supported actions. The resulting code is VBA, and you can inspect or edit it in the Visual Basic Editor. The recorder does not replace the VBA language or automatically add decision-making logic.
Can I use the macro recorder in Excel for the web?
No. Excel for the web can display and edit many workbook contents, but it does not create, run, or edit VBA macros. Open the file in desktop Excel for Windows or Mac to work with VBA.
Why does my recorded macro work only on one cell or sheet?
The code may contain a fixed cell address or rely on the active sheet and selection used during recording. Check whether Use Relative References was enabled as intended, inspect the range references in the code, and test the macro from the correct starting cell and worksheet.
Will a recorded macro run on another computer?
It can, if the recipient uses a desktop Excel version that supports the VBA features in the code and receives the macro-enabled workbook or the required macro file. Differences between Windows and Mac, missing add-ins, workbook structure, and security policies can affect whether it works. A macro in your Personal Macro Workbook is not automatically transferred with a workbook.
Can I undo a macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s ordinary undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running a macro that changes data, and test uncertain code on sample data first.
Related
- Excel Split Cell Data Into Multiple Columns
- Excel Split Cell Into First and Last Name: A Clear Guide
- Excel Split Cell by Space Delimiter: Methods and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Save a Worksheet as a New File SafelyTo save one Excel worksheet as a separate file with VBA, copy it to a new workbook with…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
- Excel VBA UserForm Tutorial: Build a Data Entry FormTo build an Excel VBA UserForm, open the Visual Basic Editor, insert a UserForm, add controls such as…
- Left, Mid, Right in Excel VBA: How String Slicing WorksIn Excel VBA, Left, Mid, and Right return selected parts of a text string: use Left(text, count) for…
- Delete the Last Row in Excel VBA SafelyTo delete the last data row of an Excel table, use the table’s ListRows collection: tbl.ListRows(tbl.ListRows.Count).Delete. For a…
- Excel VBA Array to Sheet: Write Data with Range.ValueTo write a VBA array to an Excel worksheet, assign a two-dimensional array to a range of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.