VBA Fundamentals

Excel Advanced

Chapter 7 · VBA Fundamentals: Variables, Loops, and the Excel Object Model

Chapter 6's recorder captures literal clicks — it cannot ask "is this value over budget?" or "repeat this for every row until the data runs out." Writing VBA directly adds exactly the logic recording can't: variables that hold a value across multiple steps, loops that repeat an action a controlled number of times, and conditionals that branch based on real data. Every VBA concept in this chapter has a direct equivalent already covered somewhere earlier in this course — the goal here is connecting new syntax to ideas you already understand, not learning them from zero.

The Sub Procedure: VBA's Basic Unit

Every macro — recorded or hand-written — is a Sub procedure: a named block starting with Sub Name() and ending with End Sub, exactly what Chapter 6's recorder generated automatically. To write one directly: Alt+F11 to open the VBA Editor, then Insert → Module, and type directly into the blank code window that appears.

Sub SayHello() MsgBox "Hello from VBA" End Sub

Variables: Naming a Value to Reuse It

A variable is declared with Dim (short for "dimension"), naming it and stating its type:

Dim total As Double Dim productName As String Dim rowCount As Integer Dim dataRange As Range total = 0 productName = "Widget" rowCount = 10

Adding Option Explicit as the very first line of a module forces every variable to be declared with Dim before use — attempting to use an undeclared name (often a simple typo, like toatl instead of total) then raises a genuine compile-time error, rather than silently creating a brand-new, always-empty variable and letting the code run anyway with a wrong result.

The Range Object: VBA's Way of Referring to Cells

Range("A1") refers to a cell exactly the way you'd expect; Cells(row, column) offers a numeric alternative — Cells(2, 1) means row 2, column 1 (cell A1) — which becomes genuinely useful once row and column are variables that change inside a loop, rather than fixed text you'd have to rebuild as a string each time.

Range("A1").Value = 42 Range("A1:A10").Font.Bold = True Cells(2, 1).Value = "Groceries" ' same cell as Range("A1") one row down

The Excel Object Model: Mirroring Chapter 1's Own Hierarchy

Excel Fundamentals Chapter 1 established Workbook → Worksheet → Cell as the fundamental structure of every spreadsheet. VBA's own object model is that exact same hierarchy, expressed as dot notation:

Workbooks("Budget.xlsx").Worksheets("Transactions").Range("A1").Value = 100 ' Workbook Worksheet Cell

Reading right to left, each dot drills one level deeper into the exact same nesting Chapter 1 first described — the workbook is the file, the worksheet is one tab inside it, and Range finally addresses one specific cell within that one worksheet.

Loops: For...Next and For Each

A For...Next loop repeats a block a fixed number of times, using a counter — the programmatic version of Excel Fundamentals Chapter 2's fill handle series, now driven by code instead of a mouse drag:

Dim i As Integer For i = 1 To 10 Cells(i, 1).Value = i * 10 Next i ' fills A1:A10 with 10, 20, 30 ... 100

For Each instead loops directly over every cell in a Range object, without you managing a counter at all:

Dim cell As Range For Each cell In Range("A1:A10") cell.Value = cell.Value * 2 Next cell ' doubles whatever value already exists in every cell from A1 to A10

Conditionals: If...Then...Else in VBA

Excel Fundamentals Chapter 4's IF() formula returns exactly one value. VBA's If...Then...Else is a real control-flow statement — it can run any number of separate statements per branch, not just produce one returned value:

If Cells(i, 3).Value > 100 Then Cells(i, 3).Font.Color = RGB(255, 0, 0) Cells(i, 4).Value = "Over Budget" ElseIf Cells(i, 3).Value > 80 Then Cells(i, 4).Value = "Close to Budget" Else Cells(i, 4).Value = "OK" End If

A Complete Example: Looping Through Data and Flagging Overspending

Combining every piece above — variables, a loop, the Range/Cells object, and a conditional — into one working procedure, in the spirit of Excel Fundamentals Chapter 10's own Budget Tracker capstone, now automated:

Sub FlagOverspending() Dim i As Integer Dim lastRow As Integer lastRow = Worksheets("Dashboard").Cells(Worksheets("Dashboard").Rows.Count, 1).End(xlUp).Row For i = 2 To lastRow If Worksheets("Dashboard").Cells(i, 3).Value > Worksheets("Dashboard").Cells(i, 2).Value Then Worksheets("Dashboard").Cells(i, 3).Font.Color = RGB(255, 0, 0) End If Next i End Sub

Cells(Rows.Count, 1).End(xlUp).Row is a common VBA idiom: start from the very bottom of the sheet and jump upward to the last cell with data in it, finding the true last row automatically — the code equivalent of Excel Fundamentals Chapter 1's own Ctrl+End shortcut.

VBA constructAlready-familiar Excel equivalent
For...Next loopFill handle dragging a series (Fundamentals Ch.2)
For Each cell In RangeIterating every cell in a selected range, without tracking a position by hand
If...Then...ElseIf...ElseThe IF() function (Fundamentals Ch.4) — but able to run multiple actions per branch, not just return one value
Workbooks(...).Worksheets(...).Range(...)The Workbook → Worksheet → Cell hierarchy (Fundamentals Ch.1), expressed as dot notation
Option Explicit turns a silent typo into a visible error
Every earlier chapter's own "gotcha" boxes shared one theme: Excel almost never raises an error for a mistake, it just quietly computes the wrong thing (Chapter 3's unlocked reference, Chapter 9's Count-instead-of-Sum). Option Explicit is the one place in this entire course where a typo genuinely does get caught immediately, as a real compile error, rather than silently producing a wrong result — always include it at the top of every module.
An unqualified Range or Cells reference silently uses whichever sheet is currently active
Writing Cells(i, 1).Value = ... with no worksheet named in front of it doesn't fail — it simply operates on the ActiveSheet, whatever that happens to be at the exact moment the code runs. If a user has a different tab selected than the one the code was written for, the macro runs successfully but silently edits the wrong worksheet, with no error at all. Always fully qualify references with an explicit worksheet — Worksheets("Dashboard").Cells(i, 1) — exactly as this chapter's complete example does throughout.

Hands-On Exercises

Exercise 1

Write a Sub procedure named FillSquares that uses a For...Next loop to fill cells A1 through A10 with each row number squared (A1=1, A2=4, A3=9, ... A10=100). Include Option Explicit and properly declared variables.

📄 View solution
Exercise 2

Write a Sub procedure that uses For Each to loop through Range("B2:B20") on a worksheet named "Prices" and multiplies every cell's existing value by 1.1 (a 10% increase), fully qualifying every reference so it always affects the "Prices" sheet regardless of which sheet is currently active.

📄 View solution
Exercise 3

A colleague's macro contains the line Cells(i, 4).Value = "Done" with no worksheet named anywhere in the procedure. They run it while a different sheet than intended happens to be active, and it appears to work with no error — but the data ends up on the wrong sheet. Explain exactly why no error occurred, and rewrite the line so this mistake becomes impossible.

📄 View solution

Chapter 7 Quick Reference

  • Sub Name() ... End Sub — the basic unit of a macro, written or recorded
  • Dim name As Type — declares a variable; use with Option Explicit to catch typos as real errors
  • Range("A1") vs. Cells(row, col) — the same cell, two addressing styles; Cells is the natural choice inside a loop
  • Workbooks(...).Worksheets(...).Range(...) — the object model, mirroring Fundamentals Chapter 1's own hierarchy
  • For i = 1 To n ... Next i — a counted loop; For Each cell In Range(...) ... Next — loops every cell directly
  • If...Then...ElseIf...Else...End If — real branching logic, multiple statements per branch
  • Always fully qualify with Worksheets("Name") — an unqualified reference silently uses whatever sheet is currently active