Excel Macro VBA Tutorial: Write and Run a First Macro

An Excel macro is a set of instructions that automates actions in a workbook, and VBA (Visual Basic for Applications) is the language used to write many Excel macros. To try one, save a copy of your workbook as an Excel Macro-Enabled Workbook (.xlsm), open the VBA editor, add a module, paste a small procedure, then run it from Excel’s Macros list.

What Excel macros and VBA do

A macro can repeat a sequence of actions that would otherwise take manual work: format a report, prepare a worksheet for printing, clean a consistent import, or copy information between sheets. VBA provides the instructions behind the macro, including ways to refer to workbooks, worksheets, cells, ranges, and Excel’s built-in features.

For example, a macro can tell Excel to bold a heading, change its fill color, and adjust column widths. It can also use conditions and loops to make decisions or handle many rows. VBA is useful when the steps are repeatable and the workbook structure is reasonably predictable. It is not a magic repair tool: a macro follows its instructions, so an incorrect range or assumption can produce incorrect results.

Macros are different from worksheet formulas. A formula calculates a value in a cell and normally recalculates when relevant inputs change. A macro runs instructions when someone starts it or when a particular workbook event occurs. VBA can enter or change formulas, but the macro itself is not a formula.

Prepare Excel to use VBA

These steps are for the desktop version of Excel for Microsoft 365 on Windows. First, save a working copy of the workbook so you can return to the original if the macro does something unexpected. Then save the copy as an Excel Macro-Enabled Workbook (*.xlsm). The standard .xlsx format does not store VBA macro code; Excel will warn you if you try to save a workbook containing macros in a format that cannot retain them.

Choose File > Save As, select the folder, and choose Excel Macro-Enabled Workbook (*.xlsm) as the file type.
If the Developer tab is not visible, choose File > Options > Customize Ribbon, select the Developer check box under Main Tabs, and choose OK.
Open the editor with Alt+F11 on Windows. On a Mac, Option+F11 is a common shortcut; depending on the keyboard, you may also need to hold Fn. You can use the Developer tab’s Visual Basic command instead.
In the editor, choose Insert > Module. A standard module is a suitable place for a first macro.

Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use desktop Excel for those tasks. Excel for Mac supports VBA in the desktop app, although some Windows-specific features—such as ActiveX controls and Windows API calls—may not work on a Mac.

Record a macro or write VBA?

Excel’s macro recorder can capture many actions performed through the interface. On Windows, choose Developer > Record Macro, perform the actions, and choose Stop Recording. Excel turns supported actions into VBA. This is a practical way to learn the names of objects and commands, or to automate a short, stable sequence of formatting steps.

Recorded code often needs editing. It may include fixed cell addresses, selections, or extra commands that reflect exactly how the actions were performed. That can make a recorded macro fragile if the data moves or grows. Writing a small procedure directly is often clearer when the task has conditions, needs to find a changing last row, or should behave consistently without selecting cells.

The recorder does not capture every Excel feature or every kind of action. If a command is unavailable for recording, VBA may still offer a way to perform it, but that requires knowing the relevant object model and checking the code carefully. Treat recorded code as a starting point, not a guarantee that the automation is robust.

A first VBA macro: format a three-column report

This example assumes the active worksheet has headers in A1:C1 and data, if any, below them. It finds the last used row in column A, applies a pale fill and bold type to the headers, and adjusts the widths of columns A through C. It does not change the cell values. Before running any macro, save a separate copy of the workbook: macro actions generally cannot be reversed with Ctrl+Z.

In the VBA editor’s module, paste this procedure. The code uses the active worksheet, so select the intended sheet in Excel before running it.

Option Explicit

Sub FormatThreeColumnReport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim reportRange As Range

    ' Use the active sheet; column A must contain each data row.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Format the three headers and the used report columns.
    With ws.Range("A1:C1")
        .Font.Bold = True
        .Interior.Color = RGB(217, 225, 242)
    End With

    Set reportRange = ws.Range("A1:C" & lastRow)
    reportRange.Columns.AutoFit
End Sub

Here, Option Explicit requires variables to be declared, helping catch misspelled variable names. ws refers to the active worksheet; lastRow finds the last non-empty cell in column A by moving upward from the bottom of that column; and reportRange represents the range used for the width adjustment. If A1:C1 contains the headings and rows 2 through 20 contain records with a value in column A on every row, the macro formats the headings and autofits columns A:C using rows 1:20 to measure their contents.

If column A has gaps or is not the column that reliably contains every record, this method may identify a row above the true end of the table. Choose a dependable key column or revise the code for the actual layout rather than assuming every worksheet has the same structure. The example also intentionally does not apply a number format, because the correct format depends on whether the values represent money, dates, quantities, or something else.

Run, inspect, and edit the macro

Return to Excel and select the worksheet you want to format. Choose Developer > Macros, select FormatThreeColumnReport, and choose Run. On Windows, Alt+F8 opens the Macros dialog. Option+F8 is commonly used on Mac keyboards, though the function-row setting can affect shortcuts. You can also return to the VBA editor and press F5 while the cursor is inside the procedure.

After it runs, check the headers and column widths, then save the workbook. If the result is not what you expected, close the file without saving the changes or restore the separate copy you made. Ctrl+Z does not normally undo macro operations, even if the macro made only a formatting change.

To change the macro, edit the code in the module and save the workbook. A procedure in a standard module can usually be run from the Macros dialog. Code placed in a worksheet’s code module or the ThisWorkbook module is often tied to events or that particular object, so it may not appear in the dialog in the same way. For a first macro, a standard module keeps the setup straightforward.

Macro security and safe use

VBA can make broad changes to files and workbook contents, which is why Excel treats macros as a security risk. A workbook arriving by email or downloaded from a website should not be trusted just because it looks familiar. Do not enable macros unless you trust the source and understand why the file needs them. A macro can do more than format cells, including deleting data or interacting with other files.

Excel’s security settings may block macros or show a warning when a file is opened. On Windows, the settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid choosing a setting that enables all macros globally just to run one file. Follow your organization’s security policy; in managed environments, administrators may control these options. If a trusted workbook is blocked because it came from an internet source, use only the organization-approved process for reviewing or unblocking it.

Macros can also be digitally signed, which helps identify whether code came from a particular publisher and whether it has changed since signing. A signature does not prove that code is harmless; it is one part of deciding whether the source is trustworthy. Keep a clean backup, test on sample data, and review code before using an unfamiliar macro.

Common VBA problems and how to diagnose them

The macro is not listed. Confirm the workbook is saved as .xlsm, the procedure is in a standard module, and its declaration begins with a runnable procedure such as Sub FormatThreeColumnReport(). Procedures declared Private or procedures that require arguments may not appear as ordinary runnable choices.
Excel says macros are disabled. Check the warning bar and security policy rather than lowering protection for every workbook. The file may be blocked or macros may be restricted by an administrator.
The macro formats the wrong sheet. The sample uses ActiveSheet. Select the correct tab before running it, or deliberately change the code to refer to a named worksheet. If the named sheet is renamed or absent, code that depends on that name needs attention.
The last row is detected incorrectly. The example looks only in column A. Blank cells in that column, a heading in a different row, or data that starts elsewhere can make the detected range unsuitable. Match the range-finding logic to the workbook’s actual layout.
The code stops with an error. If Excel highlights a line in the editor, check that object and range names are valid and that the workbook has the expected sheet and cells. Compile and test changes on a copy, especially before running code over a large or important workbook.

VBA errors are not always syntax errors. A procedure can compile but still refer to the wrong worksheet or make an incorrect assumption about the data. Testing with a small sample that includes blank rows, unusual values, and the expected edge cases is more informative than testing only a perfect-looking sheet.

When VBA is the right tool—and alternatives

VBA is a strong option when desktop Excel users need a button, a repeatable workflow, or automation that interacts with workbook objects. It is especially useful for older workbooks and processes already built around Excel. It can also become difficult to maintain when a workbook depends on undocumented code, one person’s computer, external files, or Windows-only components.

For recurring data imports and reshaping, Power Query can often provide a refreshable workflow without a VBA procedure. For calculations that should update as values change, ordinary worksheet formulas are usually easier to inspect. Office Scripts can automate tasks in Excel for the web and is a different technology from VBA; its availability and features depend on the Microsoft 365 plan and environment. Choose based on where the workbook runs, who maintains it, and whether the task is a calculation, a data transformation, or an interface-driven action.

Consider the people who will use the finished file. They need a compatible desktop version, permission to run the macro, and a clear way to understand what it changes. A macro that works only on one person’s machine because it uses a local path or installed component is not a portable solution. Document assumptions such as sheet names, expected columns, and input locations near the code or in the workbook.

Frequently Asked Questions

How do I start writing a VBA macro in Excel?
Save the workbook as .xlsm, open the VBA editor with Alt+F11 on Windows, choose Insert > Module, and add a procedure beginning with Sub and ending with End Sub. Test it on a copy of the workbook.

Can I run VBA macros in Excel for the web?
No. Excel for the web can work with many macro-enabled files, but VBA macros cannot be created, edited, or run there. Open the workbook in desktop Excel to use VBA.

Can I undo a VBA macro with Ctrl+Z?
Usually not. Running VBA generally clears Excel’s ordinary undo history. Save a backup before running code, particularly if it changes or deletes data.

Why did my .xlsx file lose its macro?
The .xlsx format does not preserve VBA code. Save a workbook containing macros as .xlsm, and choose to keep the macro-enabled format if Excel warns that another format cannot store the code.

Is it safe to enable macros in a workbook?
Only enable them when you trust the source and have a reason to run the code. A macro can change workbook data and interact with files, so inspect unfamiliar code and follow your organization’s security rules.

Does Excel for Mac support VBA?
Yes, the desktop Mac app supports VBA, but some Windows-specific code and components are not cross-platform. Test the workbook on the Mac version users will rely on, especially if the macro uses external automation or controls.

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.