Yes. In Excel for Microsoft 365 on Windows, enable the Developer tab, press Alt+F11, choose Insert > Module, and paste in your VBA code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), then run the macro with Alt+F8. Make a copy before running unfamiliar code, because a macro’s changes cannot be undone with Ctrl+Z.
What a VBA macro does
VBA, or Visual Basic for Applications, is Excel’s built-in programming language for automating tasks. A macro is a set of instructions that Excel can run for you. It might format a report, clean imported data, create a worksheet, or repeat a sequence of actions across many rows. Unlike an ordinary worksheet formula, a macro performs actions in the workbook or application when it runs.
Macros are useful when a task has clear steps that you repeat. For example, if every monthly report needs the first row formatted as a heading, a short macro can apply that format consistently. VBA can also respond to workbook events, such as opening a file, although event-driven macros need additional setup and care.
A macro does not make every task better. For a one-time change, using Excel’s menus may be quicker. For calculations that need to update automatically as cell values change, formulas are often a better fit. A macro is most useful when you want to automate an action or a repeatable workflow.
Before adding a macro
VBA macros run in the desktop versions of Excel, including Excel for Microsoft 365 on Windows and Mac. Excel for the web can open and edit many workbooks that contain VBA, but it cannot create, edit, or run VBA macros. To work with the code, open the workbook in the desktop app.
On Windows, turn on the Developer tab if it is not already visible: choose File > Options > Customize Ribbon, then select the Developer check box in the list of main tabs and choose OK. The tab provides access to Visual Basic, macro tools, and the macro recorder. On Mac, use Excel’s ribbon to open the Developer tab; if it is hidden, enable it in Excel’s ribbon or toolbar settings. The exact settings layout can vary by Excel for Mac release.
Decide where the macro should live. Adding it to the current workbook makes it available when that workbook is open. If you want a macro available in other workbooks on the same computer, Excel also offers the Personal Macro Workbook, a hidden workbook that loads when Excel starts. For a first macro, keeping it in the workbook you are working on is usually simpler and makes it easier to share the code with that file.
Add VBA code in the Visual Basic Editor
These steps add a standard macro to the current workbook in Excel for Microsoft 365 on Windows:
- Open the workbook you want the macro to use, and check that you are editing the desktop app rather than Excel for the web.
- Press Alt+F11 to open the Visual Basic Editor. You can also choose Developer > Visual Basic.
- In the editor, select the workbook project in the Project Explorer. If that panel is hidden, use View > Project Explorer.
- Choose Insert > Module. Excel adds a standard code module under the selected workbook.
- Paste or type your procedure into the code window, then close or switch away from the editor. The code is saved with the workbook when you save the file.
- Save the workbook as an .xlsm file, then run the macro from Excel. Do not save it as .xlsx if you need to preserve the VBA code.
On Mac, the Visual Basic Editor is available from Developer > Visual Basic. Keyboard shortcuts can depend on the keyboard and function-key settings, so using the ribbon is a dependable way to open it. The editor’s layout and some supported VBA features differ from Windows, especially for code that depends on Windows-specific components.
A simple example macro
This example bolds the non-empty heading cells in row 1 of the active worksheet and gives them a pale blue fill. It is intended for a sheet where row 1 contains column headings. The macro checks that the row is not empty before formatting it.
Sub FormatHeaderRow()
Dim ws As Worksheet
Dim lastCol As Long
' Work on the currently active worksheet.
Set ws = ActiveSheet
' Stop if row 1 has no content.
If Application.WorksheetFunction.CountA(ws.Rows(1)) = 0 Then
MsgBox "Row 1 is empty."
Exit Sub
End If
' Find the last used heading cell in row 1.
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Format the heading cells from column A to the last used column.
With ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol))
.Font.Bold = True
.Interior.Color = RGB(217, 225, 242)
End With
End Sub
To try it, select the worksheet with headings, open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Return to Excel and run FormatHeaderRow. The macro formats row 1 on the active sheet, so confirm that the correct sheet is selected first. If your headings begin on a different row, the code needs to be adjusted rather than simply run as written.
Save and run the macro
Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) as the file type. The .xlsm format can store VBA code; the standard .xlsx format cannot. If you save a macro-containing workbook as .xlsx, Excel warns that the VBA project will be removed if you continue. Choose the macro-enabled format when preserving the code matters.
To run a macro in Excel for Windows, press Alt+F8, select its name in the Macro dialog, and choose Run. You can also use Developer > Macros. For the sample above, select FormatHeaderRow. On Mac, use Developer > Macros to choose and run a macro; keyboard shortcuts may differ depending on the keyboard configuration.
You can assign a macro to a button or a keyboard shortcut, but that is optional. A button can make a routine easier for other workbook users to find, while a shortcut can save time for an individual. Before assigning either, make sure the macro’s name and purpose are clear and that its effects are understood.
Macro security and safe use
VBA can change or delete workbook data, and macros can also interact with files or other parts of the computer. That power is why Excel may block macros in files from sources it does not trust. Do not enable macros just because a workbook asks you to. If you did not expect the file, or cannot verify who provided it and what the code does, leave the macros disabled.
For a workbook you created or trust, inspect the code and use your organization’s security rules. On Windows, Excel’s macro controls are in File > Options > Trust Center > Trust Center Settings > Macro Settings. Options and policies can vary, particularly on managed work computers. Avoid setting Excel to enable all macros indiscriminately; that can allow unsafe code to run in other files. A trusted location or a digitally signed macro may be appropriate in some workplaces, but follow the instructions of your IT administrator.
Keep a separate copy before testing a macro that edits data. Excel does not provide a normal Undo history for VBA actions, so Ctrl+Z will not reverse a macro’s changes. If a macro makes an unexpected change, close without saving if appropriate, or restore the backup. Test code on sample data before using it on an important workbook.
Common problems and fixes
- The Developer tab is missing: On Windows, enable it through File > Options > Customize Ribbon and select Developer. If settings are controlled by an employer, ask the administrator about restrictions.
- The macro does not appear in the Macro dialog: Confirm that the code is in a standard module and that it is a public procedure with no required arguments, such as the example’s FormatHeaderRow procedure. Check that the workbook containing the code is open.
- The code disappears after saving: Check the file extension. Save as .xlsm, not .xlsx, if the workbook must retain VBA.
- Excel blocks the macro: The file may be blocked or the workbook’s macro settings may prevent it from running. Verify the source and follow your organization’s security process; do not bypass a warning for an untrusted file.
- The macro changes the wrong sheet: The sample uses ActiveSheet, so the selected sheet determines where it works. Activate the intended sheet first or change the code to refer explicitly to a worksheet by name.
- The macro runs on Windows but not Mac: The code may rely on Windows-only components, paths, or application features. Excel for Mac supports VBA, but not every Windows-specific feature or external library is available there.
VBA compared with the macro recorder
You do not have to write every macro from scratch. Excel’s macro recorder can capture many actions performed through the interface and produce VBA code. On Windows, choose Developer > Record Macro, perform the actions, and then choose Stop Recording. The recorded procedure can be inspected and edited in the Visual Basic Editor.
The recorder is a useful starting point for straightforward formatting or repetitive menu actions, but its output can be more literal and less flexible than hand-written code. It may record fixed cell references where a reusable macro should find the relevant range dynamically. Recording also does not guarantee that Excel can capture every action. Review the generated code and test it on a copy before relying on it.
For tasks involving repeated data transformations, Power Query may be easier to maintain than VBA. For calculations that should update with cell values, formulas are often more direct. Office Scripts are another automation option in supported Microsoft 365 environments, but they use a different technology and are not VBA macros. The right choice depends on the task, the people who need to run it, and the Excel platform they use.
Frequently Asked Questions
Can I add a VBA macro in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but it cannot create, edit, or run VBA code. Open the file in desktop Excel to work with the macro.
Does saving as .xlsx remove a macro?
Yes. The .xlsx format does not store VBA projects. Choose .xlsm to keep the macro, and read Excel’s warning carefully if you save a macro-enabled file in a format that cannot preserve its code.
Can I undo a macro with Ctrl+Z?
Usually not. VBA actions do not participate in Excel’s standard Undo history. Save a separate copy before running code that changes data, and test on a duplicate when the workbook is important.
Can I add a macro to the Quick Access Toolbar?
Yes. In desktop Excel, you can use the Quick Access Toolbar customization options to add a macro command. This gives you a convenient way to run a macro, but it does not change what the code does or make an untrusted macro safe.
Can I use the same macro on Windows and Mac?
Many basic VBA procedures work on both platforms, but code that depends on Windows-specific features may not. If a workbook will be shared across Windows and Mac, test the macro on both versions and avoid unsupported operating-system components.
Related
- Add Percentages to an Excel PivotTable
- Accounting Year-End Checklist Template for Excel Users
- 3-Way Forecast Excel Template: 3 Models to Compare
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Code: How to Write, Run, and Troubleshoot MacrosExcel VBA code is Visual Basic for Applications instructions that automate work in the desktop version of Excel,…
- Excel VBA Move Worksheet: Code and Key DetailsTo move a worksheet with Excel VBA, use the worksheet’s Move method and specify the sheet it should…
- Excel VBA AutoFilter: Examples, Criteria, and FixesExcel VBA AutoFilter filters a worksheet range or Excel table by applying criteria to one or more columns.…
- Excel VBA Frame With Scroll Bar: Setup and CodeTo make a scrollable area in Excel VBA, add a Frame to a UserForm, set its ScrollBars property…
- VBA Dictionary Class Modules: A Practical How-ToTo use a class module with a VBA Dictionary, define a class to represent one record, create an…
- Excel UserForm: Change Font Size at Design Time or in VBATo change an Excel VBA UserForm’s font size, select the form or a control in the Visual Basic…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.