Excel Macro Script: How It Works and How to Create One

VBA & macros · Updated November 14, 2026

An Excel macro script is a set of instructions that automates actions in a workbook; in desktop Excel, macros are commonly written in Visual Basic for Applications (VBA). You can record a sequence of actions or write VBA yourself, then run the macro again whenever you need the same task done.

What an Excel macro script does

A macro can perform repetitive work such as formatting a report, cleaning imported data, filling a template, or preparing a workbook for printing. Instead of clicking through the same commands each time, you run the macro once and let its instructions carry out those actions. A macro can also respond to information in cells, make decisions, and repeat operations across a range.

“Macro script” is an informal phrase rather than a separate Excel file type or programming language. In desktop Excel, it usually means VBA code saved in a workbook or in a separate macro workbook. A macro is the runnable procedure; VBA is the language used to write many Excel macros.

For example, a short macro might bold a table’s header row and adjust the widths of its columns. A more involved one could import files, check for missing values, and create a summary. What a macro can do depends on its code and the permissions available to it, so a script should be reviewed before you run it.

How macros work in Excel

Excel’s VBA environment interacts with workbook objects: workbooks contain worksheets, worksheets contain ranges and cells, and those objects have properties and methods. A property describes or changes something, such as a cell’s font color. A method performs an action, such as clearing a range or sorting data.

A macro usually begins with a procedure declaration such as Sub NameOfMacro() and ends with End Sub. When you run the procedure, Excel executes its statements in order. Some macros use the currently selected cells; others refer to a named worksheet or a defined range. References to specific sheets and ranges generally make a macro less dependent on what happens to be selected.

Excel can also record actions and translate many of them into VBA statements. The recorder is useful for learning the general shape of VBA, but its output may be longer or less flexible than hand-written code. It records actions, not your intention: a recorded instruction may refer to a particular cell even if you meant “the last row of the current table.”

Record a macro without writing code

For a straightforward, repeatable sequence, recording can be the easiest starting point. In Excel for Microsoft 365 on Windows, enable the Developer tab if it is not visible: choose File > Options > Customize Ribbon, then select Developer. On a Mac, look under Excel > Settings > Ribbon & Toolbar and enable Developer; wording can vary by Excel release.

  1. Choose Developer > Record Macro.
  2. Give the macro a name that begins with a letter and contains no spaces. You can also assign a shortcut, but avoid one you rely on for a standard Excel command.
  3. Choose where to store it. This Workbook keeps the macro in the current file; Personal Macro Workbook makes it available in Excel on that computer.
  4. Perform only the actions you want repeated, then choose Developer > Stop Recording.
  5. Test the macro on a copy of the workbook or on sample data before using it on important work.

The recorder captures many formatting and editing actions, but it is not a general-purpose recording of everything you do. For instance, it does not turn spoken instructions into logic, and it cannot infer that a range should expand when new rows are added. Inspect or edit recorded code through Developer > Visual Basic on Windows or the Visual Basic Editor on Mac.

Write and run a simple VBA macro

The following example formats the first row of the selected range as a header. Select a table range with its header row at the top before running it. It makes that row bold, applies a pale fill, and autofits the columns touched by the selection.

Save a copy of the workbook before running any macro: macro actions cannot be undone with Ctrl+Z. This example changes formatting rather than cell values, but the copy is still a useful safeguard.

Sub FormatSelectedHeader()
    ' Format the first row of the selected range as a table header.
    Dim target As Range

    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range that includes the header row."
        Exit Sub
    End If

    Set target = Selection.Rows(1)
    target.Font.Bold = True
    target.Interior.Color = RGB(217, 225, 242)
    target.EntireColumn.AutoFit
End Sub

To add it, open the VBA editor with Developer > Visual Basic or press Alt+F11 on Windows. On many Mac keyboards, Option+F11 opens the editor; you may need Fn as well, or you can use the Developer tab. In the editor, choose Insert > Module and paste the code into the module window. Return to Excel, select the range, and run the procedure from Developer > Macros. You can also run a procedure from the VBA editor.

Keep in mind that the macro formats the first row of the selection, not a row Excel has independently identified as a header. Selecting the wrong range can therefore format the wrong row. The example is deliberately small; a production script may need checks for an empty selection, merged cells, protected sheets, or tables with special formatting.

Save the workbook in a macro-compatible format

A standard .xlsx workbook does not retain VBA macro code. If the code belongs in the workbook, use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). Excel may warn that a feature cannot be saved in a macro-free format if you try to keep VBA in an .xlsx file. Choose a macro-enabled format if you intend to preserve the code.

If you stored a macro in the Personal Macro Workbook, it is kept separately from the workbook you are editing. That can be convenient for personal utilities, but it means sharing the spreadsheet does not automatically share the macro. A colleague may need the code or an appropriately saved macro-enabled workbook to use the same automation.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Open the file in the desktop Excel application to use its VBA macro. If someone sends you an .xlsm file, the extension alone does not establish that its code is safe.

Macro security and safe use

VBA macros can make extensive changes to files and workbook contents. A macro from an unknown source could delete or alter data, access resources, or perform other actions you did not intend. Do not enable macros simply because a workbook displays a warning or claims that enabling them is necessary; first verify who provided the file and why the code is needed.

On Windows, macro options are available under File > Options > Trust Center > Trust Center Settings > Macro Settings. Organizations may manage these settings centrally. Excel may also block macros in files it considers to have come from the internet. Do not weaken security settings to bypass a warning. If a trusted organization supplied the workbook, follow its approved process or ask its support team.

  • Use code from a source you trust, and inspect unfamiliar code before running it.
  • Keep an unchanged backup of important workbooks and test on a copy first.
  • Be cautious with macros that ask for unusual permissions, open external files, or send information elsewhere.
  • Share macro-enabled files only with people who need the code, and explain what the macro does.

These precautions are useful even for benign automation: a coding mistake can overwrite cells just as easily as an intentional instruction can. Since Ctrl+Z does not reverse VBA macro actions, recovering from a mistake may require closing without saving or restoring a backup.

Common macro problems and fixes

If a macro does not appear in the list, check that it is stored in the workbook you opened or in the Personal Macro Workbook, and that it is a standard procedure Excel can run. A procedure declared as Private, or one that requires arguments, may not appear as a normal runnable macro. Confirm that the file is saved as .xlsm if the code should travel with it.

If Excel blocks the macro, check the workbook’s source and your organization’s security guidance rather than turning on all macros. If the macro runs but affects the wrong cells, review whether its code relies on Selection or ActiveSheet. Those references depend on the current interface state. Referencing a specific worksheet and range can make a script more predictable, provided those names match the workbook.

Another common issue is a macro that works on one dataset but not another. The code may assume that data always starts in the same cell, that columns are in a fixed order, or that there are no blank rows. Test with realistic variations, including empty or unusual inputs, and add checks for assumptions that matter. When a procedure stops with a VBA error, the editor generally highlights the statement that could not run; the error message and workbook state help identify the cause.

VBA macros, Office Scripts, and alternatives

VBA is a good fit for many desktop Excel tasks, especially established workflows that use workbook objects and desktop features. It runs in Excel for Windows and Mac, although some Windows-specific features, integrations, and code may need changes on Mac. A macro that depends on Windows-only components should not be assumed to work unchanged on a Mac.

Office Scripts are a separate automation option in supported Microsoft 365 environments. They use TypeScript rather than VBA and are designed for modern Excel workflows, including use with Power Automate where available. Access depends on the account, license, administrator settings, and Excel environment. The Automate tab may not be available to every user. Office Scripts are not a way to run VBA in Excel for the web.

For a one-time task, ordinary Excel features may be simpler and safer than a macro. Tables, built-in formatting, formulas, Power Query, and the Data tools can handle many repeatable jobs without VBA. Choose a macro when the sequence is sufficiently repetitive, the steps are supported, and maintaining code is worth the convenience. For a shared business process, consider who will maintain the automation and how users will be told what it changes.

Frequently Asked Questions

Is an Excel macro script the same as VBA?
A macro is an automated procedure; VBA is the programming language commonly used to write desktop Excel macros. People often use “macro script” to mean the VBA code that carries out the automation.

Can I run an Excel macro in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run its VBA code. Office Scripts are a separate option in supported environments, not VBA macros.

How do I save an Excel macro?
Save the workbook containing the code as an Excel Macro-Enabled Workbook (.xlsm). A regular .xlsx file does not preserve VBA code.

Why is my macro disabled?
Excel may block macros based on security settings, file origin, or an organization’s policy. Verify the source and follow approved guidance; do not enable all macros just to get one file working.

Can I undo a macro with Ctrl+Z?
No. Excel’s normal undo history does not undo VBA macro actions. Save a separate copy before running code that changes a workbook, especially if it edits or deletes data.

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.