VBAAugust 2026 · 12 min read

Excel Macros and VBA: Complete Beginner's Guide

Macros automate repetitive Excel tasks — formatting reports, cleaning data, copying sheets — so a job that takes 20 minutes runs in 3 seconds. You do not need to be a programmer to start.

Fastest Way to Start

Turn on the Developer tab: File → Options → Customize Ribbon → check Developer. Then use Record Macro — perform any action in Excel and it writes the VBA code for you automatically. Edit the recorded code to make it smarter.

Step 1 — Enable the Developer Tab

The Developer tab is hidden by default. To enable it: File → Options → Customize Ribbon → in the right panel, check "Developer" → OK. You will now see a Developer tab in the ribbon with Record Macro, Macros, and Visual Basic buttons.

Step 2 — Enable Macros Safely

Go to Developer → Macro Security (or File → Options → Trust Center → Trust Center Settings → Macro Settings). Choose "Disable all macros with notification" — the safest setting. Excel will prompt you each time you open a macro-enabled file. Only click Enable Content for files you created or trust completely.

Step 3 — Record Your First Macro

  1. Developer tab → Record Macro
  2. Give it a name (no spaces), optionally assign a keyboard shortcut (e.g. Ctrl+Shift+F)
  3. Choose where to store it: "This Workbook" (available in this file only) or "Personal Macro Workbook" (available in all Excel files)
  4. Perform the actions you want to automate — click cells, apply formatting, enter data
  5. Developer tab → Stop Recording

Run it: Developer → Macros → select your macro → Run. Or use the keyboard shortcut you assigned.

Step 4 — View and Edit the VBA Code

Press Alt+F11 to open the VBA editor. In the left panel, find your workbook → Modules → Module1. You'll see the VBA code that Excel recorded. The recorded code is a starting point — edit it to add loops, conditions, and variables.

Core VBA Concepts

Sub and End Sub

Every VBA macro is a "Sub" (subroutine). It starts with Sub MacroName() and ends with End Sub. Everything in between runs when the macro is called.

Sub FormatReport()
    ' Your code goes here
End Sub

Range and Cells

Range("A1") refers to cell A1. Range("A1:C10") refers to a block. Cells(1,1) refers to row 1, column 1 — useful for loops where you need to change the row or column number.

Range("A1").Value = "Hello"
Range("B2:B10").Font.Bold = True
Cells(1, 1).Interior.Color = RGB(255, 255, 0)

Variables and data types

Declare variables with Dim. Common types: String (text), Long (whole number), Double (decimal), Boolean (True/False), Range (a cell or range object).

Dim lastRow As Long
Dim ws As Worksheet
Dim productName As String

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For...Next loop

Repeats a block of code a set number of times. Combine with Cells() to process each row in a dataset.

Dim i As Long
For i = 2 To 100
    If Cells(i, 3).Value > 1000 Then
        Cells(i, 4).Value = "High"
    Else
        Cells(i, 4).Value = "Low"
    End If
Next i

For Each...Next loop

Loops through every object in a collection — every sheet in a workbook, every cell in a range, every row in a table.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Tab.Color = RGB(0, 112, 192)
Next ws

With block

Applies multiple properties to the same object without repeating the object name. Cleaner and faster than multiple separate lines.

With Range("A1:A10")
    .Font.Bold = True
    .Font.Size = 12
    .Interior.Color = RGB(200, 230, 200)
    .NumberFormat = "#,##0"
End With

4 Useful Macros to Copy

Delete all blank rows

Loops from the bottom up (critical — deleting rows from top shifts row numbers down and causes skips).

Sub DeleteBlankRows()
    Dim lastRow As Long, i As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lastRow To 1 Step -1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

Copy sheet to new workbook

Creates a new workbook and copies the active sheet into it — useful for distributing individual sheets from a master file.

Sub CopySheetToNewWorkbook()
    Dim newWb As Workbook
    ActiveSheet.Copy
    Set newWb = ActiveWorkbook
    newWb.SaveAs Filename:="C:OutputReport_" & Format(Now, "YYYYMMDD") & ".xlsx"
End Sub

Apply consistent formatting to all sheets

Loops through every sheet and applies the same header formatting — font, colour, column width.

Sub FormatAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws.Rows(1)
            .Font.Bold = True
            .Font.Size = 11
            .Interior.Color = RGB(0, 70, 127)
            .Font.Color = RGB(255, 255, 255)
        End With
        ws.Columns.AutoFit
    Next ws
End Sub

Find last row dynamically

The single most useful snippet in VBA — finds the last row of data in a column, so loops don't process blank rows.

Sub ExampleWithLastRow()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    ' Now use lastRow in your loop
    Dim i As Long
    For i = 2 To lastRow
        ' Process row i
    Next i
End Sub

Saving Macro-Enabled Files

Regular .xlsx files cannot contain macros. When you save a workbook with macros, Excel will prompt you to save as .xlsm (Excel Macro-Enabled Workbook). Always save macro files as .xlsm — if you save as .xlsx, the macros are deleted.

Macro Security Reminder

Never enable macros in a workbook you received from an email attachment from an unknown sender. Malicious macros can run system commands and download files. The safe practice: only enable macros in files you created or received from trusted colleagues through internal systems.

VBA vs Python in Excel

Excel 365 now includes native Python (=PY() function). Python is better for data science, machine learning, and statistical analysis. VBA is better for automating Excel-specific tasks — formatting, workbook management, interacting with the Excel UI, creating custom forms, and running on any Windows Excel version. For most day-to-day automation in Excel, VBA remains the most direct tool.

Frequently Asked Questions

How do I enable macros in Excel?

File → Options → Trust Center → Trust Center Settings → Macro Settings → select "Disable all macros with notification." Then Excel will prompt you when opening macro-enabled files — click Enable Content only for files you trust.

What is the difference between a macro and VBA?

A macro is a recorded or written sequence of actions. VBA is the programming language macros are written in. Recording a macro automatically generates VBA code which you can then edit.

Is VBA hard to learn?

VBA is one of the more accessible entry points into programming. The macro recorder writes most of the code for you. Basic automation tasks can be learned in hours; more complex logic takes more practice.

Related Guides

Python in Excel

Python for data analysis inside Excel cells — the modern alternative to VBA for analytics tasks.

Read →

Excel Keyboard Shortcuts

Assign keyboard shortcuts to macros — combine shortcuts and macros for maximum speed.

Read →

AI Formula Generators

AI tools like ChatGPT can write VBA macros from plain-English descriptions.

Read →

Excel + AI Hub

AI-powered automation — from formula generation to full macro writing.

Read →