Learn Writing Your First VBA Procedure | Introduction to VBA and the Excel Object Model

Swipe to show menu

Recording macros teaches you vocabulary; writing one from scratch is where you start thinking like a programmer.

The Anatomy of a Sub Procedure

Every reusable block of VBA code that performs an action (rather than returning a value, which is a Function) is written as a Sub:

Sub ProcedureName()
    ' instructions go here
End Sub

With your cursor anywhere inside a Sub, press F5 (or click the green Run arrow) to execute it. Comments — any line starting with an apostrophe (') — are ignored by VBA entirely; use them generously to explain why code does something, not just what it does, since the what is usually obvious from reading the code itself.

Formatting Habits that Pay Off Immediately

Type Option Explicit at the very top of every module — it forces you to declare every variable, which catches typos before they become bugs;
Indent code inside loops and If blocks by one Tab — the VBE won't force this on you, but unindented code becomes unreadable fast;
Use descriptive names (HighEarnerCount, not x) — you will thank yourself in three weeks.

Worked Example: Highlighting High Earners

Let's write, by hand, a procedure that goes through every employee in the table and highlights anyone earning over $55,000 — something the macro recorder could never do, because it requires a decision (an If statement) repeated for each row (a loop).

Option Explicit
 
Sub HighlightTopEarners()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long
 
    Set ws = ThisWorkbook.Worksheets("Employees")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
    For i = 2 To lastRow
        If ws.Cells(i, 6).Value > 55000 Then
            ws.Cells(i, 6).Interior.Color = RGB(198, 239, 206)
        End If
    Next i
 
    MsgBox "Done — high earners are highlighted."
End Sub

Walking through it line by line

Dim ws As Worksheet / Dim i As Long — these declare two variables: ws will hold a reference to the Employees sheet, and i will count through rows;
Set ws = ThisWorkbook.Worksheets("Employees") — this is the object-model habit: name the sheet explicitly instead of relying on whatever happens to be active;
ws.Cells(ws.Rows.Count, 1).End(xlUp).Row — a standard idiom that finds the last used row in column A, no matter how many employees are in the table;
For i = 2 To lastRow ... Next i — a loop that repeats the code once for every data row, starting at row 2 to skip the header;
If ws.Cells(i, 6).Value > 55000 Then — a decision: only rows where the Salary column (column 6) exceeds 55000 get formatted;
MsgBox — a simple pop-up confirming the macro finished.

Type this procedure into modIntro, place your cursor inside it, and press F5. Check the numbers yourself — Emma Davis and Olivia Brown are the two employees earning over $55,000 in the sample table.

Task

Type HighlightTopEarners exactly as shown into modIntro and run it. Confirm two rows turn green.
Duplicate the procedure as HighlightITDepartment, and change the condition so it highlights any row where the Department (column 3) equals "IT" instead of checking Salary.
Add a comment above each procedure explaining, in one sentence, what it does — practice writing comments for the reader, not for yourself.
Save the workbook again (it's still .xlsm, so a normal Ctrl+S is fine).

Hint

Select the entire HighlightTopEarners procedure (from Sub to End Sub), copy it, and paste it right below itself in modIntro.
Rename the second copy's Sub line to Sub HighlightITDepartment() — every procedure in a module needs a unique name, or VBA won't know which one you mean when you try to run it.
The condition to change is the If line. Right now it checks a number:
```
If ws.Cells(i, 6).Value > 55000 Then
```
You need it to check text instead — column 3 (Department) equals "IT". Remember from Chapter 2 that text comparisons need quotation marks around the value you're comparing to, and use = rather than >.
Everything else inside the loop (the Interior.Color line, the loop structure itself) can stay exactly the same — only the condition being tested changes.

Adding Comments

A comment line starts with an apostrophe (') and goes on its own line directly above the Sub line — not inside the procedure.
Write it as if explaining the macro to a coworker who's never seen it, not as a reminder to yourself. Compare:
- Not so useful: ' loops through rows (describes how, which the code already shows)
- More useful: ' Highlights employees earning over $55,000 (describes what it accomplishes, which isn't obvious from a glance)
Do the same for the second procedure — one sentence describing what it highlights and why, in terms of the business purpose (IT department staff), not the mechanics of the loop.

Solution

' Highlights employees earning over $55,000, to flag top earners at a glance.
Sub HighlightTopEarners()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Employees")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 6).Value > 55000 Then
            ws.Cells(i, 6).Interior.Color = RGB(198, 239, 206)
        End If
    Next i

    MsgBox "Done — high earners are highlighted."
End Sub

' Highlights every employee who works in the IT department.
Sub HighlightITDepartment()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Employees")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 3).Value = "IT" Then
            ws.Cells(i, 3).Interior.Color = RGB(198, 239, 206)
        End If
    Next i

    MsgBox "Done — IT department is highlighted."
End Sub

Type both into modIntro, run HighlightTopEarners first and confirm exactly two rows turn green (Emma Davis at $62,000 and Sophia Miller at $59,000 — the other three fall at or below $55,000), then run HighlightITDepartment and confirm Sophia Miller's row highlights instead. Save with Ctrl+S once both are working.

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 6

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 1. Chapter 6