Excel macro code is usually written in Visual Basic for Applications (VBA), a language that automates tasks in desktop Excel. In Excel for Microsoft 365 on Windows, open Developer > Visual Basic, choose Insert > Module, and enter a VBA procedure; the example below formats a worksheet with headings in row 1.
What Excel macro code does
A macro is a set of instructions that Excel can carry out for you. It can format cells, move or summarize data, create worksheets, apply filters, or repeat a sequence of actions. VBA is the programming language commonly used for macros in desktop Excel. A macro can be as small as a few lines or part of a larger application with forms and multiple procedures.
VBA code is not the same thing as a worksheet formula. A formula calculates a result in a cell, while a macro performs actions in the workbook, such as changing formatting or copying a range. Macros can also write formulas into cells, but they are not required for ordinary calculations. If a built-in Excel feature or formula already solves the problem, it may be simpler to use that instead.
A VBA procedure usually starts with Sub and ends with End Sub. Excel runs the instructions between those lines from top to bottom. The procedure name is how you identify and run that macro. VBA is included with desktop Excel, but a workbook must be saved in a format that retains its code.
Where to enter VBA code
On Windows, show the Developer tab if it is not already visible: choose File > Options > Customize Ribbon, select the Developer check box in the list of main tabs, and choose OK. Then open Developer > Visual Basic. In the Visual Basic Editor, choose Insert > Module. A module is a place to store procedures that can be run from Excel.
Paste or type VBA into the module’s code window. Keep the procedure inside the module, and avoid pasting it into a worksheet’s code window unless you specifically need an event procedure that responds to an action such as changing a cell. To return to Excel, close the editor or use its Excel window.
Excel for Mac also supports many VBA macros, but the interface and available features can differ. Use the Developer tab to open Visual Basic and insert a module; if the tab is hidden, enable it in Excel’s ribbon or toolbar preferences. Some Windows-specific controls, libraries, and operating-system calls do not work on a Mac, so code that interacts with those features may need changes. Excel for the web does not create, edit, or run VBA macros.
A simple VBA macro example
This example assumes the active worksheet contains data and that its column headings are in row 1. It makes those headings bold and adjusts the widths of columns used by the data. Before running any macro, save a copy of the workbook: macro actions cannot be reversed with Ctrl+Z.
Sub FormatActiveSheet()
Dim ws As Worksheet
' Work on the currently active worksheet.
Set ws = ActiveSheet
' Stop if the worksheet contains no data.
If WorksheetFunction.CountA(ws.Cells) = 0 Then Exit Sub
' Assumes the column headings are in row 1.
With ws
.Rows(1).Font.Bold = True
.UsedRange.Columns.AutoFit
End With
End Sub
The Dim statement declares a variable named ws that can refer to a worksheet. Set ws = ActiveSheet points it to the sheet currently selected in Excel. The count check avoids formatting an empty sheet. The With block lets the code apply both formatting instructions to that worksheet without repeating its name.
This is intentionally a modest example, not a general-purpose report formatter. It assumes row 1 is a heading row; if your data starts lower down, change the row reference to match. It also acts on whichever sheet is active when you run it, so check the selected sheet first. The code changes formatting and column widths, not cell values, but those changes still cannot be undone using Excel’s normal undo command after a macro runs.
How to run a macro
After entering the code, return to the workbook and select the worksheet you want to format. On Windows, press Alt+F8 to open the Macro dialog, select FormatActiveSheet, and choose Run. You can also choose Developer > Macros, select the procedure, and choose Run. On a Mac, use Developer > Macros and select the procedure; keyboard shortcuts can vary with the keyboard and macOS settings.
If the macro is not listed, check that it is in a standard module, that its procedure begins with Sub, and that it does not require arguments. Also check that the active workbook is the one containing the code. When you close the workbook, choose a macro-enabled format if you want to keep the VBA.
Recording a macro instead of writing code
If you do not know VBA, Excel’s macro recorder can turn some actions into VBA. On Windows, choose Developer > Code > Record Macro, give the macro a name that starts with a letter and contains no spaces, and optionally assign a shortcut or choose where to store it. Carry out the actions you want to automate, then choose Developer > Code > Stop Recording.
Run the recorded macro from Developer > Macros or the shortcut you assigned. The recorder is useful for learning: after recording, open the Visual Basic Editor and inspect the generated code. It can show how Excel represents actions such as applying a number format or setting a cell’s alignment.
Recorded code often refers to specific cells and may include extra selections or scrolling instructions. That can make it fragile if the next dataset has a different size or location. A recorded macro may also use absolute references, which repeat actions on the same cells, or relative references, which act in relation to the selected cell. Choose the recording mode that matches the task, and review the code before relying on it for repeated work.
Saving and sharing a workbook with macros
To retain VBA code, save the file as an Excel Macro-Enabled Workbook (*.xlsm). Choose File > Save As and select that file type. A standard .xlsx workbook does not store VBA project code; if you save a macro-containing workbook as .xlsx and accept the removal prompt, the code is discarded. Keep a separate copy if you need both a macro-enabled version and a macro-free version.
A workbook containing macros may display a security warning when opened. Only enable macros when you trust the file and understand what it is meant to do. A macro can make changes to files and workbook data, and its code may do more than the visible sheet suggests. If you receive a workbook unexpectedly, confirm its source before enabling content. In a workplace, follow your organization’s security policy rather than changing security settings to bypass a warning.
When sharing a macro-enabled workbook, tell recipients what the macro does and which Excel environment it needs. A VBA macro will not run in Excel for the web. A recipient using Excel for Mac may also encounter compatibility issues if the code relies on Windows-only features. Test a copy in the intended desktop version and explain any manual steps.
Common problems and practical checks
- The macro does not appear: Confirm that the code is in a standard module in the workbook you expect, and that the procedure has no required arguments.
- Excel blocks the macro: Treat the warning as a security decision, not a routine obstacle. Verify the sender and file before enabling anything; an administrator may control the available options.
- The macro changes the wrong sheet: The example uses
ActiveSheet. Select the intended worksheet first, or revise the code to refer to a specific worksheet by name. - The result differs on another computer: Check the Excel platform, workbook structure, sheet names, and any external files or libraries the code expects. Windows-only components can prevent otherwise valid VBA from working on a Mac.
- The macro cannot be undone: Ctrl+Z does not reverse VBA actions. Save a copy before running unfamiliar code, especially if it edits or deletes data.
When debugging, read the exact error message and note which line Excel highlights. A common cause is a changed sheet name or a range reference that no longer matches the workbook. Test with a copy containing a small sample, and make changes one at a time. If code comes from another source, inspect it before running it rather than assuming that its name accurately describes its behavior.
VBA macros and alternatives
VBA is a good fit for automating desktop Excel tasks, especially when a process needs to work with workbook objects such as sheets, ranges, and formatting. It is also useful when maintaining an existing workbook that already depends on macros. Its limitations matter: VBA does not run in Excel for the web, and some code is tied to a particular operating system or desktop setup.
For repeatable transformations that do not require custom code, consider built-in tools such as sort and filter, tables, PivotTables, or Power Query. They can be easier for colleagues to inspect and maintain. For automation in supported Microsoft 365 environments, Office Scripts are another option. They use TypeScript rather than VBA and are designed for web-based workflows and integrations; availability depends on the account, license, and environment. Office Scripts are not simply a way to run VBA in a browser, and VBA code cannot be pasted into an Office Script and expected to work.
Choose based on where the workbook will be used, what needs to be automated, and who will maintain it. For a short, repeated desktop task, a VBA macro may be practical. For a one-time change, doing it manually may be safer and faster. For a shared web workflow, check whether an Excel feature or Office Scripts meets the need before building a VBA-dependent file.
Frequently Asked Questions
Is Excel macro code the same as VBA?
Most traditional Excel macros are written in VBA, but “macro” describes the automation, while VBA is the language used to write it. Excel also has other automation options, including Office Scripts in supported Microsoft 365 environments.
Can I run VBA macros in Excel for the web?
No. Excel for the web can open a workbook that contains VBA, but it cannot create, edit, or run those macros. Use a compatible desktop version of Excel to work with VBA.
How do I keep a macro when I save an Excel file?
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx removes the VBA project if you accept the prompt to save without macro features.
Why is my macro missing from the Macro dialog?
Check that the code is in a standard module, that it is a public procedure with no required arguments, and that you are looking in the workbook where it was saved. Also confirm that the file is macro-enabled.
Can I undo a macro with Ctrl+Z?
No. Excel’s undo history does not reverse actions performed by VBA macros. Save a backup before running code, and test unfamiliar macros on a copy of the workbook.
Related
- Conditional Formatting for Duplicates in Excel
- How to Merge Cells in Excel: Steps, Options, and Fixes
- Split Cell Contents Into Multiple Columns in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
- Input Box in Excel VBA: Use InputBox and Get DataIn Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict…
- Filter a Dictionary in Excel VBA: Practical ExamplesExcel VBA has no built-in Filter method for a Scripting.Dictionary. The usual approach is to loop through the…
- Excel VBA Data Validation List From a Range in CellsTo create an Excel drop-down list from a range with VBA, use the target cell’s Validation.Add method and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.