Excel VBA Macro Basics: A Beginner’s Guide

VBA & macros · Updated November 22, 2026

Excel VBA (Visual Basic for Applications) macros are recorded or written sequences of instructions that automate repetitive tasks in Excel, such as formatting, data entry, or calculations. To create your first macro, press Alt+F11 to open the VBA editor, insert a new module, and type a simple subroutine like Sub Hello() : MsgBox "Hello" : End Sub, then press F5 to run it. This guide explains the core concepts, how to write and use macros safely, and answers common questions beginners ask.

What Is a Macro and What Can It Do?

A macro is a set of programming instructions written in VBA that Excel can execute. Macros can perform almost any action you can do manually, such as:

  • Formatting cells (fonts, colors, borders)
  • Copying and pasting data between sheets or workbooks
  • Creating charts and pivot tables
  • Looping through rows to clean or transform data
  • Automating data import and export
  • Showing custom dialog boxes and user forms

For example, a macro can take a raw export from a database, remove blank rows, apply a table style, and generate a summary report—all with a single click. Macros are stored inside the workbook file (with .xlsm extension) or in your personal macro workbook for global use.

How Do Macros Work in Excel?

VBA is an object-oriented programming language. In Excel, you work with an object model: Application (Excel itself), Workbook, Worksheet, Range, and Cell. A macro is a Sub procedure that contains lines of code that manipulate these objects. For instance, the following macro selects cell A1 and types “Hello” into it:

Sub TypeHello()
    Range("A1").Select
    ActiveCell.Value = "Hello"
End Sub

When you run this macro, Excel performs the same steps as if you clicked on A1 and typed. The code uses the Range object to reference a cell, and the .Value property to set its content. You can also use variables, loops, and conditional statements to make macros dynamic.

How to Record a Macro (The Easiest Way)

If you don’t want to write code from scratch, use the Macro Recorder. It translates your manual actions into VBA code. Steps:

  1. Go to the View tab (or Developer tab if enabled) and click Macros > Record Macro.
  2. Give your macro a name (no spaces), assign a shortcut key if desired, and choose where to store it (This Workbook or Personal Macro Workbook).
  3. Click OK and perform the actions you want to automate.
  4. Click Stop Recording.

The recorder generates code that you can inspect and edit in the VBA editor. For example, if you record formatting a range as bold, the code will look like Selection.Font.Bold = True. Note that recorded macros are often inefficient because they select cells unnecessarily, but they are perfect for learning.

Writing Your First Macro in the VBA Editor

To write code manually, open the VBA editor with Alt+F11 (on Mac: Fn+Option+F11). In the Project Explorer, right-click any workbook and choose Insert > Module. This creates a blank code window. Type a subroutine. Here’s a useful macro that loops through column A and bolds any cell that contains the word “urgent”:

Sub BoldUrgent()
    Dim cell As Range
    For Each cell In Range("A1:A100")
        If InStr(1, cell.Value, "urgent", vbTextCompare) > 0 Then
            cell.Font.Bold = True
        End If
    Next cell
End Sub

Before running, save a copy of your workbook (the macro cannot be undone with Ctrl+Z). Run the macro by pressing F5 while the cursor is inside the subroutine, or by assigning it to a button or keyboard shortcut. Always test on a copy first.

Understanding Variables, Loops, and Conditions

These three concepts are the building blocks of any useful macro.

  • Variables store data temporarily. Declare them with Dim, e.g., Dim total As Long.
  • Loops repeat actions. The For Each ... Next loop iterates over a collection (like all cells in a range). The Do While ... Loop repeats until a condition is false.
  • Conditions use If ... Then ... Else to make decisions. For example, If cell.Value > 100 Then cell.Interior.Color = vbYellow.

Here’s a macro that sums values in column B and displays the result:

Sub SumColumnB()
    Dim total As Double
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    total = Application.WorksheetFunction.Sum(Range("B1:B" & lastRow))
    MsgBox "Total: " & total
End Sub

This uses the End(xlUp) method to find the last used row, a common pattern in VBA.

Common Problems and How to Avoid Them

Beginners often encounter these issues:

  • Macros disabled: Excel blocks macros by default for security. To run them, save as .xlsm and enable content when opening, or add the file to Trusted Locations.
  • “Sub or Function not defined” error: Usually a typo in the macro name. Double-check spelling.
  • Run-time error 1004: Often means you tried to access a range that doesn’t exist, or the sheet is protected. Unprotect the sheet or correct the range reference.
  • Infinite loop: If a Do While condition never becomes false, Excel may freeze. Press Esc to interrupt, but always save your work first.
  • Macro not doing anything: Ensure the code is in the correct module and that you haven’t accidentally commented it out with an apostrophe.

Always test macros on a copy of your workbook. Since macros cannot be undone with Ctrl+Z, saving a backup is essential.

Macro Security and Safety

Macros can contain harmful code, so Excel has security settings. By default, macros are disabled. To enable them, you must either:

  • Save the file as .xlsm and click “Enable Content” when the yellow bar appears.
  • Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and choose “Disable all macros with notification” (recommended).
  • Add a folder to Trusted Locations if you frequently run your own macros.

Never enable macros from untrusted sources. Even your own recorded macros can be risky if you accidentally record sensitive actions, but the main danger is opening a workbook from the internet with embedded macros. Always scan files and verify the source.

Alternatives to VBA

VBA is not the only way to automate Excel. Depending on your needs, consider:

  • Power Query: Best for data transformation and import/export. It has a built-in editor and no coding required.
  • Excel Formulas: For simple calculations and lookups, functions like IF, SUMIFS, and XLOOKUP can often replace a macro.
  • Office Scripts: Available in Excel for the web and Microsoft 365, they use JavaScript-like syntax and can automate tasks in the cloud.
  • Power Automate: For workflows that involve multiple apps, not just Excel.

VBA remains the most powerful and widely used option for desktop Excel automation, especially for complex interactions with the user interface.

Frequently Asked Questions

Can I undo a macro? No, macros cannot be undone with Ctrl+Z. That’s why you should always save a copy of your workbook before running a macro, especially one that modifies data.

Why is my macro not working on another computer? The most common reasons are: macros are disabled on that machine, the workbook is saved as .xlsx (which doesn’t store macros), or the macro references a specific sheet or range that doesn’t exist there. Ensure you save as .xlsm and enable content.

What is the difference between a Sub and a Function in VBA? A Sub performs an action and does not return a value. A Function returns a value and can be used in worksheet formulas. For example, a custom function can calculate a tax rate.

How do I run a macro with a button? Insert a shape or button from the Developer tab (or use a form control), then right-click it and choose “Assign Macro”. Select your macro from the list and click OK.

Is VBA the same as Excel formulas? No. Formulas are written in cells and calculate values; VBA is a programming language that controls Excel itself. You can use VBA to write formulas, but they are separate.

Can macros be used in Excel for the web? No, Excel for the web does not support VBA. Use Office Scripts instead, which are available in the browser version.

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.