Variables, Loops & Conditions in VBA: A Practical Guide

These three building blocks power almost every macro.

---

Variables

• Dim name As Type
• Prefer specific types (Long, Double, String) over Variant
• Scope: Dim (procedure), Private (module), Public (project)

Option Explicit
Private counter As Long  ' module-level variable

---

Loops

For loop

Dim r As Long
For r = 2 To 100
    Cells(r, "C").Value = r * 10
Next r

For Each

Dim c As Range
For Each c In Range("A2:A100")
    If Len(c.Value) > 0 Then c.Offset(0, 1).Value = "OK"
Next c

Do While / Do Until

Dim i As Long: i = 1
Do While i <= 10
    Debug.Print i
    i = i + 1
Loop

---

Conditions

If x > 0 Then
    MsgBox "Positive"
ElseIf x < 0 Then
    MsgBox "Negative"
Else
    MsgBox "Zero"
End If

Select Case for multiple branches:

Select Case UCase$(status)
    Case "OPEN":   ' ...
    Case "CLOSED": ' ...
    Case Else:     ' ...
End Select

---

Example: Summarize positive values

Function SumPositive(rng As Range) As Double
    Dim c As Range, total As Double
    For Each c In rng.Cells
        If IsNumeric(c.Value) And c.Value > 0 Then
            total = total + c.Value
        End If
    Next c
    SumPositive = total
End Function

Tip: Return values from Functions, orchestrate from Subs.