Excel Macros & VBA for Beginners: The Complete Guide (2026)
Home›Excel›Macros & VBA
ExcelPillar Guide⏱ 28 min read

Excel Macros & VBA for Beginners: The Complete Guide

Once you learn to record and write macros, tasks that took hours become one-click. VBA is Excel's automation language — it looks intimidating but the entry point is friendly: the macro recorder writes your first code for you. This guide walks from "never opened the Developer tab" to "wrote a macro that formats a report", with the safety warnings, patterns, and pitfalls you'll actually encounter.

Macros vs. VBA

The two words get used interchangeably but they mean different things:

  • VBA (Visual Basic for Applications) is the programming language.
  • A macro is a single procedure written in VBA — a named block of code that does something when you run it.

Every macro is VBA. Not everything in VBA is a macro (there are also functions, event handlers, and class modules) — but for beginners, "macro" and "VBA procedure" are close enough.

The typical beginner path

Record a macro doing something manually. Look at the recorded code. Edit it to make it more useful. Rinse, repeat. Within a few weeks you'll write short macros from scratch. Full-blown application development is years away — and 95% of people never need to go there.

Enabling the Developer tab

Excel hides VBA tools by default. To reveal them:

  1. File → Options
  2. Customize Ribbon (left sidebar)
  3. In the right column, check the box next to Developer
  4. OK

The Developer tab now appears in the ribbon between View and Help. Key buttons:

  • Visual Basic — opens the VBA editor
  • Macros — lists all macros in the current workbook
  • Record Macro / Stop Recording — the recorder
  • Insert — adds form controls (buttons, checkboxes) to sheets
  • Macro Security — sets how strictly Excel treats macros
Keyboard shortcut: Alt + F11 opens the VBA editor from anywhere in Excel

Recording your first macro

Record it

  1. Developer → Record Macro
  2. Give it a name (letters and underscores only, no spaces): FormatHeaders
  3. Optional shortcut key (Ctrl + Shift + F for example)
  4. Store macro in: This Workbook (most common; the macro travels with this file)
  5. Description: what it does
  6. OK — Excel is now recording
  7. Do the thing manually: select row 1, click Bold, apply a fill color, center-align
  8. Developer → Stop Recording

Run it

To see the macro work: delete the formatting you just applied, then run the macro.

  • Developer → Macros → select FormatHeaders → Run
  • Or press the shortcut key you set
  • Or press Alt+F8, pick the macro, Run

Look at the code

Developer → Macros → select the macro → Edit. The VBA editor opens with your recorded code:

Sub FormatHeaders() ' FormatHeaders Macro ' Format header row: bold, centered, filled Rows("1:1").Select Selection.Font.Bold = True Selection.HorizontalAlignment = xlCenter With Selection.Interior .Pattern = xlSolid .Color = 15773696 End With End Sub

That's the entire language showing up: Sub starts a macro, End Sub ends it, comments start with ', and the actions you performed are represented as methods on objects.

The recorder is verbose

Recorded code contains a lot of "Select this, then act on selection". Written-from-scratch VBA usually skips the selects. Range("A1:E1").Font.Bold = True is one line; the recorder produces two. Both work — but as you edit recordings, you'll trim the noise.

The VBA editor

Press Alt+F11 to open it. The editor has three main panes:

  • Project Explorer (top left) — every open workbook and its modules
  • Properties (bottom left) — settings for the selected object
  • Code Window (right) — where you write and edit VBA

What lives where

Under each workbook in Project Explorer:

  • Microsoft Excel Objects — a code sheet for each worksheet plus one for ThisWorkbook. Put event handlers here (Worksheet_Change, Workbook_Open).
  • Modules — code that runs on demand, not in response to events. Regular macros go here.
  • Class Modules — advanced; for defining custom objects. Beginners can ignore.

Inserting a module

Right-click your workbook in Project Explorer → Insert → Module. A blank code window opens. This is where you'll write most new macros.

Anatomy of a macro

Sub MacroName() ' Your code here End Sub

That's the skeleton. Every macro:

  • Starts with Sub followed by the name and empty parentheses
  • Ends with End Sub
  • Contains one or more lines of code in between

Naming rules: letters and underscores, no spaces, can't start with a number. Names are case-preserving but not case-sensitive.

Your first hand-written macro

In the code window, type:

Sub HelloWorld() MsgBox "Hello, world!" End Sub

Press F5 (or Run → Run Sub/UserForm). A message box pops up. You just ran your first from-scratch macro.

Working with cells and ranges

The Range object is VBA's way of referring to cells. Every automation involves ranges.

Referring to cells

Sub RangeBasics() Range("A1").Value = "Hello" ' single cell Range("A1:C3").Value = 42 ' block Cells(1, 2).Value = "in B1" ' row 1, column 2 Range("MyName").Value = "named range" End Sub

Range uses A1-style addresses. Cells(row, col) uses numeric indices — useful in loops. Both refer to the same underlying object.

Reading vs. writing

Setting .Value writes to a cell. Reading .Value retrieves it.

Sub ReadAndWrite() Dim price As Double price = Range("A1").Value ' read Range("B1").Value = price * 1.1 ' write End Sub

Common Range properties

PropertyWhat it does
.ValueCell contents (number, text, or date)
.FormulaCell formula (e.g. "=SUM(A1:A10)")
.Font.BoldTrue/False
.Interior.ColorFill color (RGB or long integer)
.NumberFormatFormat code, e.g. "0.00%" or "yyyy-mm-dd"
.Rows.Count / .Columns.CountDimensions of a range
.Offset(rows, cols)A range shifted from the current position

Referring to sheets

Sub SheetBasics() Sheets("Data").Range("A1").Value = "Hello from macro" Worksheets(1).Name = "Renamed" ' by index ActiveSheet.Range("B2").Clear End Sub

Sheets and Worksheets are near-identical; use either. ActiveSheet is whichever sheet is currently in focus.

Variables and data types

Variables hold values you'll reuse or compute. Declare them with Dim:

Sub Variables() Dim total As Double Dim name As String Dim counter As Long Dim found As Boolean Dim when As Date total = 0 name = "Zain" counter = 1 found = False when = Now End Sub

The main data types

TypeHoldsRange
LongWhole number±2.1 billion
DoubleDecimal numberVery large / precise
StringTextUp to ~2 billion characters
BooleanTrue or False—
DateDate or date-timeJan 1, 100 to Dec 31, 9999
RangeCell range object—
VariantAnything (default if no type given)—
Turn on Option Explicit

At the top of every module, add Option Explicit. This forces you to declare every variable before use — catching typos that would otherwise silently create new empty variables. To make it automatic: in the VBA editor, Tools → Options → Editor tab → check "Require Variable Declaration". Every new module gets Option Explicit added automatically.

Loops and conditionals

If / Else

Sub IfExample() Dim score As Long score = Range("A1").Value If score >= 90 Then Range("B1").Value = "A" ElseIf score >= 80 Then Range("B1").Value = "B" Else Range("B1").Value = "C or lower" End If End Sub

For loop

Sub ForExample() Dim i As Long For i = 1 To 10 Cells(i, 1).Value = "Row " & i Next i End Sub

For Each (iterating a range)

Sub ForEachExample() Dim c As Range For Each c In Range("A1:A100") If c.Value < 0 Then c.Font.Color = vbRed End If Next c End Sub

Do While

Sub DoWhileExample() Dim row As Long row = 1 Do While Cells(row, 1).Value <> "" Cells(row, 2).Value = Cells(row, 1).Value * 2 row = row + 1 Loop End Sub

Runs until it hits an empty cell — a common way to process an unknown-length list.

Message boxes and input boxes

MsgBox

Sub Alerts() MsgBox "Simple message" MsgBox "Alert with title", vbInformation, "Update" MsgBox "Warning: this can't be undone", vbExclamation End Sub

Yes/No confirmation

Sub Confirm() Dim response As VbMsgBoxResult response = MsgBox("Delete all rows?", vbYesNo + vbQuestion, "Confirm") If response = vbYes Then ' user clicked Yes — proceed Range("A2:A100").ClearContents End If End Sub

InputBox

Sub AskUser() Dim firstName As String firstName = InputBox("What's your first name?", "Welcome") If firstName <> "" Then MsgBox "Hello, " & firstName End If End Sub

Ways to run a macro

1. Alt + F8

The universal shortcut — opens the macro list, pick one, click Run.

2. Keyboard shortcut

Assign one when you record the macro, or later via Developer → Macros → select → Options → enter Ctrl+key combination.

3. Button on the sheet

  1. Developer → Insert → Button (Form Control)
  2. Draw the button on the sheet
  3. Assign Macro dialog opens — pick the macro → OK
  4. Right-click the button to rename it

Click the button to run the macro. Good for user-facing tools.

4. Shape as a button

Insert any shape (rectangle, rounded rectangle) → right-click → Assign Macro → pick your macro. Prettier than form buttons.

5. Automatically on events

Place event-handler code on the sheet or ThisWorkbook module:

' Runs whenever a cell in Sheet1 changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MsgBox "A1 changed to " & Target.Value End If End Sub
Disable events during macro runs

Sub that changes cells while a Worksheet_Change event handler is active will cause infinite recursion. Guard with Application.EnableEvents = False at the start and = True at the end.

Macro security & file formats

File formats you'll encounter

ExtensionWhat it is
.xlsxStandard workbook — cannot store macros
.xlsmMacro-enabled workbook — stores VBA code
.xlsbBinary workbook — macro-capable, smaller and faster than XLSM
.xlamAdd-in — a macro-only file loaded silently by Excel to expose macros in every workbook

If you save an .xlsx that contains macros, Excel warns and strips the code. Always Save As .xlsm when your workbook has VBA.

Security warnings

Opening a macro-enabled file from an unknown source shows a yellow banner: "SECURITY WARNING — Macros have been disabled." You click Enable Content to allow them.

Excel's default behavior:

  • Files downloaded from the internet — macros blocked, banner shown
  • Files opened from network shares — macros blocked in newer Excel versions unless the location is trusted
  • Files from Trusted Locations — macros run silently

Setting up a Trusted Location

For your own workbooks:

  1. File → Options → Trust Center → Trust Center Settings → Trusted Locations
  2. Add new location → browse to your folder → OK
  3. Save your macro-enabled workbooks there — no more warnings
Only enable macros in files you trust

VBA has broad access to your computer — files, network, other applications. A malicious macro can delete files, send emails, or install malware. If you don't recognize the sender or don't understand what the code does, don't enable it. When in doubt, Alt+F11 first and read the code.

Debugging basics

Setting a breakpoint

Click the gray margin next to a line of code. A red dot appears. When you run the macro, execution pauses at that line.

Stepping through code

  • F8 — step to the next line (executes the current line, pauses)
  • Shift+F8 — step over (don't dive into called procedures)
  • F5 — continue running to the next breakpoint or end

Watching values

Hover over a variable while paused to see its current value. Or use the Immediate Window (Ctrl+G) to inspect values:

? Range("A1").Value ? counter

Prints the value in the Immediate Window.

Debug.Print

Sprinkle Debug.Print variable in your code to log values to the Immediate Window as the macro runs.

Common errors

  • Run-time error 1004 — usually a bad range reference (sheet doesn't exist, cell out of bounds)
  • Run-time error 9 — subscript out of range (accessing an array or collection index that doesn't exist)
  • Run-time error 13 — type mismatch (assigning wrong type to a variable)
  • Compile error: Variable not defined — Option Explicit is on and you used a variable without declaring it

Useful macros to try

1. Highlight duplicates in column A

Sub HighlightDuplicates() Dim c As Range For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) If Application.WorksheetFunction.CountIf(Range("A:A"), c.Value) > 1 Then c.Interior.Color = RGB(255, 230, 230) End If Next c End Sub

2. Delete empty rows in a range

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

Loops from the bottom up — critical when deleting rows so indices don't shift under the loop.

3. Timestamp when a cell is edited

Place on the Sheet1 code module:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then ' column B edited Application.EnableEvents = False Cells(Target.Row, 3).Value = Now ' timestamp in column C Application.EnableEvents = True End If End Sub

4. Save a copy of the workbook with today's date

Sub SaveBackup() Dim savePath As String savePath = ThisWorkbook.Path & "\Backup_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm" ThisWorkbook.SaveCopyAs savePath MsgBox "Backup saved: " & savePath End Sub

5. Export current sheet to PDF

Sub ExportToPDF() Dim pdfPath As String pdfPath = ThisWorkbook.Path & "\" & ActiveSheet.Name & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath MsgBox "Exported: " & pdfPath End Sub

6. Speed booster (for macros that touch many cells)

Sub FastMacro() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' Your work here — 10x to 100x faster Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Always restore the settings

If your macro errors out with ScreenUpdating off, Excel looks frozen. Wrap the body in On Error GoTo Cleanup and put the restoration in a Cleanup label at the end. This is defensive coding — but the alternative is Excel that looks broken.

Pitfalls to avoid

Not saving as .xlsm

The most common beginner mistake: writing macros, saving as .xlsx, watching the code disappear on next open. Excel warns you, but the warning is easy to dismiss.

Selecting everything before acting

Recorded macros do Range("A1").Select : Selection.Value = 5. That works, but hurts performance and readability. Skip the Select and write directly: Range("A1").Value = 5.

Hardcoded ranges

Range("A2:A100") breaks the moment your data grows past row 100. Use dynamic ranges: Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row) — finds the last row of data automatically.

No error handling

A macro that fails mid-way can leave the workbook in a broken state (screen not updating, events disabled, filters partially applied). Add error handling for anything that matters:

On Error GoTo Cleanup
' work
Cleanup:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
Deleting rows in a top-down loop

Deleting row 5 shifts row 6 to row 5. If your loop is going up, you skip a row every time you delete one. Always loop bottom-up when deleting: For i = lastRow To 2 Step -1.

Ignoring the debugger

Adding MsgBox lines to trace what a macro is doing works — barely. F8 stepping and Debug.Print are dramatically better. Ten minutes with the debugger is worth an hour of print statements.

Google Sheets equivalent — Apps Script

Google Sheets uses Apps Script (JavaScript-based) instead of VBA. Concepts translate:

  • VBA Sub → Apps Script function
  • VBA Range("A1").Value → Apps Script SpreadsheetApp.getActiveSheet().getRange("A1").getValue()
  • VBA event handlers → Apps Script onEdit, onOpen triggers
  • VBA MsgBox → Apps Script SpreadsheetApp.getUi().alert()

See the Sheets Apps Script guide (coming soon in this series) for the equivalent.

Excel Wizard

VBA code, written from your intent

Writing your first macros is fun. Writing your hundredth "loop through this range and format based on…" macro is tedious. Excel Wizard generates VBA from plain-English intent ("highlight overdue invoices, add a timestamp to the log sheet") — production-ready code with error handling and dynamic ranges built in.

Install Excel Wizard →

Frequently asked questions

What is VBA in Excel?

Visual Basic for Applications — the programming language built into Excel. Macros are individual VBA procedures. VBA also drives Word, PowerPoint, Access, and Outlook.

How do I enable the Developer tab in Excel?

File → Options → Customize Ribbon → check Developer → OK. Only needs doing once per install.

Do I need to know how to code to use VBA?

Not to start. The macro recorder writes code from your actions. Learn to edit, then write from scratch as you get comfortable.

Are macros safe to enable?

From trusted sources, yes. Macros have broad system access, so treat unknown ones with the same caution as any executable. Excel blocks downloaded macros by default.

What's the difference between .xlsx and .xlsm files?

.xlsx cannot contain macros; .xlsm can. Save macro-enabled workbooks as .xlsm or the code disappears on next open.