Excel VBA Code: How to Write, Run, and Troubleshoot Macros

Excel VBA code is Visual Basic for Applications instructions that automate work in the desktop version of Excel, such as formatting a selected table or repeating a sequence of steps. To try a simple example, select a contiguous range that includes a header row, open the Visual Basic Editor, insert a module, paste the code below, and run the macro; save a copy of the workbook first because Ctrl+Z cannot undo a macro.

What Excel VBA code is

VBA stands for Visual Basic for Applications. It is a programming language built into desktop Microsoft Office apps, including Excel. A VBA procedure, commonly called a macro, tells Excel what actions to perform. Those actions can include changing cell values, applying formatting, creating worksheets, or processing data across a workbook.

VBA is different from an Excel formula. A formula calculates a result in a cell and generally updates when its inputs change. VBA runs a set of instructions when a person starts it or when a programmed event occurs. For example, a formula can total sales; a macro can prepare a report by adding headings, formatting a range, and creating a summary sheet.

VBA is also different from Office Scripts. VBA is primarily used in desktop Office applications, while Office Scripts are designed for supported Microsoft 365 workflows, including Excel on the web. Which tool fits depends on where a workbook must run and what it needs to automate.

A small VBA example

This macro formats the range currently selected in Excel. It sets the font, adds borders, bolds the first row of the selection, and adjusts column widths. Select a single, contiguous table with its headings before running it; the macro does not identify a header automatically.

Option Explicit

Sub FormatSelectedRange()
    Dim target As Range

    ' Stop if the current selection is not a cell range.
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a cell range, then run the macro."
        Exit Sub
    End If

    Set target = Selection

    ' Apply basic formatting to the selected table.
    With target
        .Font.Name = "Aptos"
        .Font.Size = 11
        .Borders.LineStyle = xlContinuous
    End With

    ' Treat the first selected row as the header row.
    target.Rows(1).Font.Bold = True
    target.Columns.AutoFit
End Sub

The procedure starts with Sub and ends with End Sub. The declared variable target refers to the selected cell range. Option Explicit asks VBA to require variables to be declared, which can help catch spelling mistakes in variable names. The comments beginning with an apostrophe explain the intent; VBA ignores them when it runs the code.

This is a deliberately limited example. It changes formatting, not the underlying cell values, but the changes still cannot be reversed with Excel’s normal Undo command after a macro runs. If the selected range does not include a header, the first selected row will still be made bold.

Where VBA code goes

Excel’s Visual Basic Editor (VBE) is where you view and write VBA. In Microsoft 365 for Windows, open it with Alt+F11 or choose Developer > Visual Basic. If the Developer tab is missing, go to File > Options > Customize Ribbon, select the Developer check box under Main Tabs, and choose OK.

In the VBE, choose Insert > Module to create a standard module, then paste a general-purpose macro there. A module is a container for procedures. In the Project Explorer, it appears under the workbook’s project, often inside a folder named Modules. If Project Explorer is hidden, use View > Project Explorer in the editor.

On a Mac, use Developer > Visual Basic or the Option+F11 shortcut; some keyboards may require Fn as well. To show Developer in current Excel for Mac, open Excel > Settings > Ribbon & Toolbar and enable Developer in the Main Tabs list. Ribbon names and keyboard behavior can differ slightly by operating system and keyboard configuration.

How to run a macro safely

For the sample, return to the workbook, select the table, then use Developer > Macros. Choose FormatSelectedRange from the list and select Run. In the VBE, you can also place the cursor inside the procedure and use Run Sub/UserForm. If the macro does not appear in Excel’s macro list, check that the procedure is a public, parameter-free procedure in a standard module and that the correct workbook is active.

Save a separate copy of the workbook before testing unfamiliar code.
Select only the cells the procedure is meant to change, and check that the selection includes the intended header row.
Run the macro once on sample data and review the result before using it on an important workbook.
Save the workbook in a macro-capable format if you need to keep the VBA code.

Macros cannot be undone with Ctrl+Z, and Excel may clear the Undo history when a macro runs. A backup copy is therefore a practical safeguard even for a short formatting procedure. If code deletes sheets, overwrites values, or changes many files, test it on disposable copies first and inspect each instruction before running it.

Saving code in the right file format

A workbook containing VBA should generally be saved as an Excel Macro-Enabled Workbook (.xlsm). In Excel for Windows, use File > Save As, choose a location, and select Excel Macro-Enabled Workbook (*.xlsm) as the file type. If you save a workbook with VBA as a standard .xlsx file, Excel warns that the VBA project cannot be saved in that format. Choose a macro-enabled format if you want to retain the code.

VBA code can also be stored in an Excel Macro-Enabled Template (.xltm) or in a separate add-in, depending on the intended use. The Personal Macro Workbook is another option for macros you want available in Excel across workbooks on the same computer. It is a hidden workbook, so it is useful for personal automation but is not a substitute for placing project-specific code in the workbook that needs it.

Excel for the web can open and edit many workbooks that contain VBA, but it cannot create, run, or edit VBA macros. Use desktop Excel for those tasks. A workbook can retain its VBA project when opened in the web app, but the macro itself will not run there.

What VBA can automate—and when to use it

VBA is most useful when a task involves actions that a user would otherwise repeat in desktop Excel, or when workbook-specific logic is needed. Common examples include applying a consistent report layout, copying selected data into a template, creating worksheets from a list, or checking a workbook for missing entries.

Choose the simplest tool that meets the need:

Formulas: Best for calculations and results that should update as cell inputs change. They keep the logic visible in worksheet cells.
Power Query: Often a better fit for importing, cleaning, and combining data through repeatable transformation steps, without writing a macro for each refresh.
VBA: Useful for desktop Excel tasks that control workbook objects, user interactions, or a sequence of actions not handled conveniently by formulas.
Office Scripts: Worth considering for supported Microsoft 365 environments where automation needs to work with Excel on the web or connect to cloud workflows.

VBA is not automatically the best choice just because a task is repetitive. If a formula can calculate the result transparently, or Power Query can refresh a data-cleaning process, those options may be easier for colleagues to understand and maintain. VBA can also depend on desktop Excel, workbook permissions, and platform-specific features.

Common VBA problems and troubleshooting

A macro that does not run is not necessarily a sign that the code is wrong. First check whether the workbook is open in desktop Excel, whether the file is saved as .xlsm, and whether the macro is available in Developer > Macros. Excel for the web does not execute VBA, and a standard .xlsx file cannot store the macro project.

If Excel displays a security warning, do not enable code simply to make the warning disappear. Only allow macros in files you trust and understand. For a trusted file, follow your organization’s policies and use the security prompt or an approved trusted location where appropriate. On Windows, macro settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid setting Excel to enable all macros indiscriminately.

When a procedure stops with a highlighted line in the VBE, read the error message and check the objects and ranges referenced on that line. A range-related error may mean the expected sheet is not active or a named range does not exist. In the sample, the code formats whatever range is selected, so selecting the wrong cells can produce an unintended result even if the macro runs successfully.

For more deliberate debugging, use the VBE’s Debug menu to step through code and inspect values. A breakpoint pauses execution at a chosen line. These tools help reveal where a procedure goes wrong, but they do not make destructive code safe; retain a clean copy before debugging a macro that changes important data.

Security and platform limits

VBA can make significant changes to a workbook, so a macro-enabled file should be treated as executable code rather than as a passive document. A macro from an unknown email attachment or download could perform actions the recipient did not intend. Read code from a trusted source, ask an administrator about workplace policy, and do not bypass security protections for an unfamiliar file.

Most ordinary workbook-oriented VBA can be written for both Windows and Mac, but not every Excel feature is available on both. ActiveX controls and Windows API calls, for example, are not portable to Mac in the same way as standard range formatting. If a workbook must be shared across platforms, keep code focused on Excel’s common object model and test it on each supported desktop platform. Excel for the web remains a separate limitation: it does not run VBA macros.

Frequently Asked Questions

How do I start writing VBA code in Excel?
Open the Visual Basic Editor with Developer > Visual Basic, insert a standard module using Insert > Module, and write a procedure between Sub and End Sub. Save a backup before running code that changes the workbook.

Can I run Excel VBA code in a browser?
No. Excel for the web can open many macro-enabled workbooks, but it cannot run, create, or edit VBA macros. Use desktop Excel for VBA.

Why did my macro disappear after saving?
Check whether the workbook was saved as .xlsx. That format does not retain VBA code. Save a workbook that needs its macro project as .xlsm or another appropriate macro-capable format.

Can I undo a macro with Ctrl+Z?
Usually not. Excel does not provide normal Undo for changes made by VBA, and running a macro can clear the Undo history. Keep a separate copy before testing or running a macro on valuable data.

Is VBA available on Mac?
Excel for Mac includes the Visual Basic Editor and supports VBA, but some platform-specific features differ from Windows. ActiveX controls and Windows API calls are examples of code that should not be assumed to work on Mac.

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.