Excel VBA Macro: How to Create, Run, and Save One Safely

VBA & macros · Updated November 19, 2026

To create a VBA macro in Excel for Microsoft 365 on Windows, turn on the Developer tab, choose Developer > Visual Basic, then choose Insert > Module and enter a VBA procedure. Run it from Developer > Macros. If you would rather begin without writing code, choose Developer > Record Macro and perform actions Excel can record.

A macro is useful when you repeat the same workbook task, such as applying a report format or preparing a recurring export. The steps below explain both ways to create one, how to save it correctly, and what to check before running it.

What an Excel VBA macro does

VBA, or Visual Basic for Applications, is the programming language built into desktop Excel. A macro is a procedure written in VBA that can carry out workbook actions. Depending on its code, it might format cells, sort a range, copy data, or create a new worksheet. A macro does not automatically understand what you intend: it follows the instructions and references in its code.

For example, a formatting macro might apply a fill color and bold text to a report’s heading row. That is different from an Excel formula, which calculates a value in a cell. Macros can change workbook content and formatting, so a mistake may affect many cells at once. Save a copy of the workbook before running an unfamiliar macro; VBA actions cannot be reversed with Ctrl+Z.

Prepare Excel and show the Developer tab

The Developer tab contains the controls for recording, editing, and running macros. It may be hidden by default. In Excel for Microsoft 365 on Windows, enable it this way:

  1. Choose File > Options.
  2. Select Customize Ribbon.
  3. Under Main Tabs, check Developer, then select OK.

The Developer tab should now appear in the ribbon. To open the VBA editor, select Developer > Visual Basic. On Windows, Alt+F11 is a keyboard shortcut for opening the editor; on some keyboards you may need to press Fn as well. On Mac, use Developer > Visual Basic because keyboard shortcuts can depend on the keyboard and macOS settings. Excel for the web does not provide the VBA editor or run VBA macros.

If you are using Excel for Mac and do not see Developer, open Excel > Settings (or Preferences in some releases) > Ribbon & Toolbar and enable Developer. The exact label can differ slightly between Mac releases, but the ribbon customization settings are where the tab is controlled.

Create a macro by recording actions

Recording is a practical way to create a first macro when the task is a sequence of actions you can perform through Excel’s interface. Excel translates supported actions into VBA. It is not a general-purpose recorder: it may not capture every dialog or decision, and a recording usually repeats the same fixed actions rather than adapting intelligently to changing data.

  1. Open the workbook and select Developer > Record Macro.
  2. Enter a short name with no spaces, such as FormatHeading. You can use underscores, as in Format_Heading.
  3. Choose where to store the macro. Choose This Workbook if the macro is meant to travel with this file.
  4. Optionally assign a shortcut and add a description. Avoid assigning a shortcut that you already rely on.
  5. Select OK, perform the actions you want to record, and then choose Developer > Stop Recording.
  6. Test the macro on a copy or on a safe sample of the workbook.

Excel also has a Use Relative References control on the Developer tab. With relative references, recorded actions can apply in relation to the currently selected cell; without them, the recording commonly refers to specific cells or ranges. The setting matters if you want to repeat the same action in different locations. Inspect a recording in the Visual Basic editor before relying on it across workbooks or changing layouts.

Write a simple VBA macro in the editor

To write code yourself, open Developer > Visual Basic. In the editor, choose Insert > Module. A standard module is a suitable place for a basic macro that you want to run from Excel’s Macro dialog. Enter a procedure between Sub and End Sub, then close or switch back to the editor’s Excel window when you are ready to run it.

This example formats cells A1 through D1 on the active worksheet and adjusts the width of columns A through D. It changes formatting, not cell values.

Sub FormatReportHeading()
    ' Format the heading row on the active worksheet.
    Dim ws As Worksheet
    Set ws = ActiveSheet

    With ws.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(217, 225, 242)
    End With

    ws.Columns("A:D").AutoFit
End Sub

Before running it, check that the intended worksheet is active: the code uses ActiveSheet, so it operates on whichever sheet is selected at run time. If the report’s heading is in another row or spans different columns, change the range and column references to match the workbook. The procedure does not test whether the sheet is protected or whether those cells contain something else, so review the target range first.

Run, inspect, and assign a macro

To run a macro in the open workbook, select Developer > Macros, choose its name, and select Run. On Windows, Alt+F8 opens the Macro dialog. On Mac, use Developer > Macros if the shortcut is unavailable or differs on your keyboard. If the macro does not appear, check that its code is in a standard module, that it is a public Sub without required arguments, and that the correct workbook is open.

You can also attach a macro to a button. One straightforward option is to add a button from Developer > Insert, under Form Controls, and then choose the macro when Excel prompts you to assign one. A button makes a repeated task easier to find, but it also makes it easier to run the code accidentally. Give the button a clear label and keep it away from controls that users click frequently.

For code you did not write, inspect what it does before choosing Run. Look for the workbooks, sheets, ranges, files, or external connections it refers to. A macro can do more than format cells, including modifying or deleting data. Do not treat a familiar-looking name such as Update as proof that code is safe.

Save the macro and protect the workbook

Macros need a file format that preserves VBA. For a workbook containing macros, choose Excel Macro-Enabled Workbook (*.xlsm) in Save As. If you save a VBA workbook as the standard .xlsx format, Excel warns that VBA content cannot be kept in that format; choose the macro-enabled format if you need the code. The .xlsb format can also store VBA, but .xlsm is the usual choice when you want a clearly identified macro-enabled workbook.

Saving a macro does not mean Excel will automatically run it the next time the file opens. Excel’s security settings control whether macros are allowed, and settings or warnings can vary with the file’s source and your organization’s policies. Only enable macros when you trust the file and understand why it needs them. Do not select an option to enable all macros globally just to make one workbook work. Files downloaded from the internet may also be subject to additional protections.

If you want a macro available in multiple workbooks on your own computer, Excel offers the Personal Macro Workbook, usually named PERSONAL.XLSB. It is stored locally and opens in the background when Excel starts. That can be convenient for personal tools, but it is not a substitute for storing a workbook-specific macro in the workbook that colleagues need. A macro in your Personal Macro Workbook will not automatically travel when you send another file.

Before testing any macro that changes data, save a separate copy of the workbook. Ctrl+Z does not undo changes made by VBA, and closing without saving may be the only simple way to discard unwanted changes if you have not made other changes you need to keep.

Common problems and how to fix them

  • The Developer tab is missing: Enable it through File > Options > Customize Ribbon on Windows, or the ribbon settings in Excel for Mac.
  • The macro is not listed: Confirm that the code is in the correct open workbook and a standard module. Check the macro name and that it is a Sub procedure Excel can run.
  • The code runs on the wrong sheet: If it refers to ActiveSheet, select the intended sheet first. For a reusable macro, consider specifying the worksheet by name and handling the case where that sheet is missing.
  • Excel blocks the macro: Review the security message and file source. Do not lower security settings or trust an unknown file just to bypass a warning; ask your organization’s administrator if the workbook is part of work.
  • The macro vanished after saving: Check whether the file was saved as .xlsx. Save a macro-enabled copy as .xlsm if you need to retain VBA.
  • The macro changed something you wanted to undo: Ctrl+Z cannot roll back VBA actions. Restore a saved copy or close without saving if appropriate, which is why a backup before testing matters.

Excel desktop, Mac, and web differences

The steps here focus on Microsoft 365 for Windows, where the ribbon commands and Visual Basic editor are available. Excel for Mac also supports VBA, but ribbon settings and some keyboard shortcuts differ. Macros that depend on Windows-only features, file paths, or other applications may need changes to work on a Mac. A workbook can therefore contain valid VBA and still behave differently across operating systems.

Excel for the web can open and edit many workbook contents, including files saved as .xlsm, but it cannot create, edit, or run VBA macros. If you need to run the code, open the workbook in the desktop Excel app. Office Scripts are a separate automation option in some Microsoft 365 environments, especially for web-based workflows; they use a different model and are not VBA macros. Availability depends on the account and organization.

Frequently Asked Questions

Can I create an Excel macro without knowing VBA?

Yes. Use Developer > Record Macro, perform the supported actions, and stop recording. The result is VBA code, and you can inspect it later in the Visual Basic editor. Recording works best for predictable sequences; it does not automatically add decision-making logic for every possible workbook layout.

Why can’t I run a macro in Excel for the web?

Excel for the web does not run VBA macros or include the VBA editor. Open the workbook in desktop Excel for Windows or Mac to use its VBA. Office Scripts may be an alternative for some web automation tasks, but they are different from VBA and may not be available in every account.

Does Ctrl+Z undo a macro?

No. VBA actions do not create the ordinary worksheet undo history that Ctrl+Z uses. Save a copy before running code that changes data or formatting, and test it on a small or disposable workbook first.

What file type should I use for a workbook with a macro?

Use .xlsm for a standard macro-enabled Excel workbook. The regular .xlsx format does not preserve VBA code. Excel may warn you when saving a macro-enabled file as .xlsx, so choose the macro-enabled format if the macro must remain with the workbook.

Can a macro contain a virus?

VBA can perform actions that alter workbook data and may interact with files or other resources. Treat macros from unknown or unexpected sources cautiously, read security warnings, and enable code only when you trust its origin and purpose. A macro-enabled file extension alone does not establish that the file is safe.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.