sodcalculator

10 Tech How-To

How to Create a VBA Macro in Excel: Step-by-Step

Guide 10-54491 · Updated · Step by step

This guide walks you through creating a VBA macro in Excel, from opening the Visual Basic Editor to running your first automated task. You’ll learn the essential steps, common pitfalls, and troubleshooting tips to get your macro working smoothly. No prior coding experience is needed—just Excel and a few minutes.

What You’ll Need

  • Microsoft Excel (2010 or later; steps are similar across versions)
  • A computer with the Developer tab enabled (instructions below)
  • Basic familiarity with Excel worksheets and cells

Step-by-Step

  1. Enable the Developer Tab
    If you don’t see the Developer tab in the ribbon, right-click anywhere on the ribbon and choose “Customize the Ribbon.” In the right panel, check the box next to “Developer” and click OK. This tab gives you access to the Visual Basic Editor and macro tools.
  2. Open the Visual Basic Editor
    Click the Developer tab, then click “Visual Basic” (or press Alt + F11). This opens a separate window where you’ll write your macro code.
  3. Insert a Module
    In the Visual Basic Editor, go to the menu bar and click Insert → Module. A blank white code window appears—this is where your macro will live. Modules are like containers for your code; you can have multiple modules in a workbook.
  4. Write Your First Macro
    In the module window, type the following code:
    Sub HelloWorld()
    MsgBox "Hello, world!"
    End Sub

    The word Sub declares a subroutine (macro), and End Sub marks its end. The line in between is the action—here, it shows a message box.
  5. Run the Macro
    Place your cursor anywhere inside the Sub block, then press F5 or click the green “Run” arrow in the toolbar. A dialog box should appear with your message. Click OK to close it.
  6. Save Your Workbook as Macro-Enabled
    To keep the macro, you must save the file as a macro-enabled workbook. Click File → Save As, choose “Excel Macro-Enabled Workbook (*.xlsm)” from the file type dropdown, and save. If you save as .xlsx, Excel will strip out the macro.
  7. Create a More Useful Macro
    Try a practical example: formatting a selected range. In the same module, add:
    Sub FormatRange()
    Selection.Font.Bold = True
    Selection.Interior.Color = RGB(255, 255, 0)
    End Sub

    Select a range in your worksheet, return to the editor, and run FormatRange. The selected cells become bold with a yellow background.
  8. Assign the Macro to a Button
    To run the macro with one click, go back to Excel. On the Developer tab, click “Insert” and choose the first button icon (Form Control). Draw a button on the worksheet. In the dialog that appears, select your macro (e.g., FormatRange) and click OK. Now clicking that button runs the macro.

Common Mistakes

  • Forgetting to Save as .xlsm – If you save as .xlsx, Excel warns that macros will be removed. Always use the .xlsm format.
  • Typing Code in the Wrong Place – Code must be inside a module, not in the “ThisWorkbook” or sheet objects unless you intend event-driven code.
  • Misspelling Object Names – VBA is case-insensitive, but it must match exactly. For example, Selection is correct, but Selectoin will cause an error.
  • Running a Macro on the Wrong Sheet – If your macro uses Selection, it acts on whatever is selected. Be sure to select the intended range before running.

Tips

  • Use the Macro Recorder – If you’re new to VBA, record a macro (Developer → Record Macro), perform your actions, then stop recording. The generated code is a great learning reference.
  • Comment Your Code – Add lines starting with an apostrophe (') to explain what each part does. This helps when you revisit the code later.
  • Test on a Copy – Before running a macro on important data, test it on a backup copy or a small sample range.
  • Use Range Instead of Selection When Possible – For example, Range("A1").Font.Bold = True is more predictable than relying on the user’s selection.

Troubleshooting

Macro doesn’t run when I press F5
Make sure the cursor is inside the macro (between Sub and End Sub). If it’s not, Excel may run a different macro or show a “Select a macro” dialog.

I get a “Compile error: Sub or Function not defined”
This usually means you misspelled a keyword or object name. Check the line that’s highlighted and correct any typos.

Macros are disabled with an error message
Excel’s security settings may block macros. Go to File → Options → Trust Center → Trust Center Settings → Macro Settings, and select “Disable VBA macros with notification” (recommended) or “Enable all macros” (if you trust the file). Then reopen the workbook and enable macros when prompted.

My macro works on my computer but not on a colleague’s
Ensure the file is saved as .xlsm and that the colleague has macros enabled. Also, if your macro references specific sheet names, those must match exactly in the other workbook.

Frequently Asked Questions

Can I undo a macro’s actions?
Usually no. Macros do not support the undo feature. Always save your workbook before running a macro, or test on a copy.

How do I delete a macro?
Open the Visual Basic Editor, find the module containing the macro, and delete the entire Sub block. You can also remove the whole module by right-clicking it in the Project Explorer and choosing “Remove Module.”

Can I run a macro automatically when opening a workbook?
Yes. Use the event Workbook_Open in the “ThisWorkbook” object. For example, Private Sub Workbook_Open() followed by your code. This runs whenever the workbook is opened.

What’s the difference between a macro and a VBA script?
In Excel, the terms are often used interchangeably. A macro is a set of VBA instructions that perform a task. A “script” can refer to a broader set of VBA code or to Office Scripts in newer versions (which use a different syntax).

Is VBA still supported in Excel 365?
Yes, VBA is fully supported in Excel 365 for Windows and Mac. Microsoft continues to update it alongside the newer Office Scripts feature.

Can I use macros on Excel for Mac?
Yes, Excel for Mac supports VBA. The steps are similar, but some keyboard shortcuts differ (e.g., use Option + F11 to open the editor).

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →