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.
What's in this guide
- Macros vs. VBA
- Enabling the Developer tab
- Recording your first macro
- The VBA editor
- Anatomy of a macro
- Working with cells and ranges
- Variables and data types
- Loops and conditionals
- Message boxes and input boxes
- Ways to run a macro
- Macro security & file formats
- Debugging basics
- Useful macros to try
- Pitfalls to avoid
- Google Sheets equivalent
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.
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:
- File → Options
- Customize Ribbon (left sidebar)
- In the right column, check the box next to Developer
- 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
Recording your first macro
Record it
- Developer → Record Macro
- Give it a name (letters and underscores only, no spaces):
FormatHeaders - Optional shortcut key (
Ctrl + Shift + Ffor example) - Store macro in: This Workbook (most common; the macro travels with this file)
- Description: what it does
- OK — Excel is now recording
- Do the thing manually: select row 1, click Bold, apply a fill color, center-align
- 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:
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.
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
That's the skeleton. Every macro:
- Starts with
Subfollowed 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:
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
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.
Common Range properties
| Property | What it does |
|---|---|
.Value | Cell contents (number, text, or date) |
.Formula | Cell formula (e.g. "=SUM(A1:A10)") |
.Font.Bold | True/False |
.Interior.Color | Fill color (RGB or long integer) |
.NumberFormat | Format code, e.g. "0.00%" or "yyyy-mm-dd" |
.Rows.Count / .Columns.Count | Dimensions of a range |
.Offset(rows, cols) | A range shifted from the current position |
Referring to sheets
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:
The main data types
| Type | Holds | Range |
|---|---|---|
Long | Whole number | ±2.1 billion |
Double | Decimal number | Very large / precise |
String | Text | Up to ~2 billion characters |
Boolean | True or False | — |
Date | Date or date-time | Jan 1, 100 to Dec 31, 9999 |
Range | Cell range object | — |
Variant | Anything (default if no type given) | — |
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
For loop
For Each (iterating a range)
Do While
Runs until it hits an empty cell — a common way to process an unknown-length list.
Message boxes and input boxes
MsgBox
Yes/No confirmation
InputBox
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
- Developer → Insert → Button (Form Control)
- Draw the button on the sheet
- Assign Macro dialog opens — pick the macro → OK
- 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:
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
| Extension | What it is |
|---|---|
.xlsx | Standard workbook — cannot store macros |
.xlsm | Macro-enabled workbook — stores VBA code |
.xlsb | Binary workbook — macro-capable, smaller and faster than XLSM |
.xlam | Add-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:
- File → Options → Trust Center → Trust Center Settings → Trusted Locations
- Add new location → browse to your folder → OK
- Save your macro-enabled workbooks there — no more warnings
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:
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
2. Delete empty rows in a range
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:
4. Save a copy of the workbook with today's date
5. Export current sheet to PDF
6. Speed booster (for macros that touch many cells)
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
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.
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.
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.
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 = TrueDeleting 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.
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 Scriptfunction - VBA
Range("A1").Value→ Apps ScriptSpreadsheetApp.getActiveSheet().getRange("A1").getValue() - VBA event handlers → Apps Script
onEdit,onOpentriggers - VBA MsgBox → Apps Script
SpreadsheetApp.getUi().alert()
See the Sheets Apps Script guide (coming soon in this series) for the equivalent.
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.