Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 93 ยท Automation and Collaboration

VBA Loops, If/ElseIf, Select Case and Procedures

Go deeper with VBA control flow: For, For Each, Do While and Do Until loops, nested If, Select Case, operators, Sub versus Function, ByVal and ByRef, and Exit statements.

VBA Basics showed one loop, one If and one Function. Real macros need more control: loops that stop early, decisions with many branches, and procedures that call each other and pass information back and forth. This lesson is the toolbox for that. Once you know it, the object lessons that follow (objects and ranges, arrays and strings) become a matter of vocabulary.

Excel and VBA cannot run on this site, so every VBA listing is read-only. The TryIts are small JavaScript stand-ins: the same logic written in JavaScript, so you can watch what the loops and procedures do. They are not VBA, and each one says where it differs.

Loops

For ... Next

Use For when you know the count. Step changes the increment, and a negative step counts down.

VB
Dim r As Long
For r = 2 To 10              ' 2, 3, ... 10
    Cells(r, 4).Value = r * 2
Next r

For r = 10 To 2 Step -1      ' bottom to top: safe when deleting rows
    If Cells(r, 1).Value = "" Then Rows(r).Delete
Next r

Deleting rows must run bottom to top. If you delete row 5 while going downward, the old row 6 becomes row 5 and your loop skips it.

For Each ... Next

For Each visits every member of a collection or range, with no counter to manage.

VB
Dim cell As Range, ws As Worksheet
For Each cell In Range("B2:B20")
    If IsNumeric(cell.Value) And cell.Value < 0 Then cell.Font.Color = vbRed
Next cell

For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next ws

Do While and Do Until

Do loops repeat on a condition rather than a count. Do While runs as long as the condition is True; Do Until runs until it becomes True. Put the test at the top (maybe zero runs) or at the bottom with Loop While (always at least one run).

VB
Dim r As Long
r = 2
Do While Cells(r, 1).Value <> ""     ' walk down until the first empty cell
    r = r + 1
Loop

Do
    answer = InputBox("Enter a number from 1 to 10")
Loop Until IsNumeric(answer) And Val(answer) >= 1 And Val(answer) <= 10

Always make sure something inside the loop changes the condition. A loop that never changes r runs forever; press Ctrl+Break to stop it.

Leaving a loop early

Exit For and Exit Do jump out of the nearest loop. VBA has no Continue statement; to skip to the next item, wrap the work in an If, or use a GoTo to a label at the bottom of the loop.

VB
For Each cell In Range("A2:A500")
    If cell.Value = "STOP" Then Exit For
    If IsEmpty(cell) Then GoTo NextCell
    total = total + cell.Offset(0, 1).Value
NextCell:
Next cell

Decisions

If, ElseIf, Else and nesting

A multi-line If can chain ElseIf tests. The first true branch wins, so order the tests from most specific to least. Branches can contain further If blocks.

VB
If region = "North" Then
    If sales >= 50000 Then
        bonus = 0.1
    Else
        bonus = 0.05
    End If
ElseIf region = "South" And sales >= 40000 Then
    bonus = 0.08
Else
    bonus = 0
End If

Deep nesting gets hard to read. Combine conditions with And / Or when you can, or leave early with Exit Sub.

Operators

Kind Operators
Arithmetic + - * /, \ (integer division), Mod (remainder), ^ (power)
Comparison = <> < > <= >=, Like (pattern), Is (object identity)
Logical And, Or, Not, Xor
Text & joins text (+ also works but can add numbers by accident)

One trap: And and Or in VBA do not short-circuit. In If x <> 0 And 10 / x > 1, VBA still evaluates 10 / x when x is 0 and raises an error. Use nested If blocks for guards. And Like is handy for patterns: "A-123" Like "[A-Z]-###" is True.

Select Case

When one value picks between many branches, Select Case is clearer than a ladder of ElseIf. A Case can hold a list, a range with To, or a comparison with Is.

VB
Select Case score
    Case Is >= 90
        grade = "A"
    Case 80 To 89
        grade = "B"
    Case 70 To 79, 65, 66
        grade = "C"
    Case Else
        grade = "Below C"
End Select

Only the first matching Case runs; there is no fall-through.

Run it: loops and decisions in JavaScript

The stand-in grades scores with the same range logic as the Select Case above, counts with a For/Step loop, and shows the non-short-circuit trap by evaluating both sides explicitly (JavaScript's && would short-circuit, so the stand-in forces both sides to be evaluated, like VBA).

JS Select Case, loops and And without short-circuit

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Procedures: Sub and Function

A Sub performs actions and returns nothing. A Function returns one value. Both can take arguments, and both can be called from other procedures.

VB
Sub BuildReport()
    Dim total As Double
    total = SumColumn("Sales", 3)
    PaintTotal total
    MsgBox "Total: " & Format(total, "#,##0.00")
End Sub

Function SumColumn(sheetName As String, col As Long) As Double
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sheetName)
    SumColumn = Application.WorksheetFunction.Sum(ws.Columns(col))
End Function

Sub PaintTotal(amount As Double)
    Range("F1").Value = amount
    Range("F1").Interior.Color = IIf(amount > 1000, vbGreen, vbYellow)
End Sub

Notice three call styles. A function used for its value needs parentheses: total = SumColumn("Sales", 3). A Sub called as a statement takes its arguments without parentheses: PaintTotal total. Call PaintTotal(total) also works and does use parentheses. Mixing these up gives confusing syntax errors.

ByVal and ByRef

Arguments are passed ByRef (by reference) by default: the called procedure works on the caller's own variable, so changes inside the procedure change the original. ByVal passes a copy.

VB
Sub AddTax(ByRef price As Double)     ' changes the caller's variable
    price = price * 1.2
End Sub

Function Doubled(ByVal n As Long) As Long   ' leaves the caller's n alone
    n = n * 2
    Doubled = n
End Function

Use ByVal unless you really want the procedure to change the caller's variable. It prevents surprising side effects.

Optional arguments, Exit and scope

  • Optional rate As Double = 0.2 gives an argument a default.
  • Exit Sub and Exit Function leave a procedure early. A function returns whatever its name was last assigned.
  • A variable declared inside a procedure with Dim lives only during that call. Declared at the top of a module, outside any procedure, it is module-level and shared by every procedure there. Public makes it visible to other modules. Keep module-level variables rare.
  • Static keeps a local variable's value between calls.

Running code from a module

Place the cursor inside a Sub that takes no arguments and press F5, or use Developer > Macros > Run. Procedures with arguments do not appear in the macro list; call them from a parameterless "entry" Sub, or from the Immediate window (? SumColumn("Sales", 3)). Assign an entry Sub to a button as described in VBA events, UserForms and controls.

Run it: ByVal versus ByRef

JavaScript passes numbers by value, so the stand-in models a variable as a small object (a box) to show a by-reference argument. Passing the box is ByRef; passing box.value is ByVal.

JS ByRef and ByVal with a box

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You delete rows that match a condition with a For loop. In which direction should the loop run?

    Deleting a row shifts the rows below it upward. Looping from the bottom means rows you have not visited yet never move, so none are skipped.
  2. 2. What does ByRef mean for an argument?

    ByRef (the default) shares the variable. ByVal passes a copy that the called procedure can change freely.
  3. 3. Which statement leaves the nearest For or Do loop early?

    VBA uses Exit For and Exit Do. It has no Continue or Break statement.
  4. 4. In VBA, what happens in If x <> 0 And 10 / x > 1 when x is 0?

    VBA's And and Or do not short-circuit. Use nested If blocks when the second test is only safe if the first passes.

Summary

  • For counts (with Step), For Each visits members, Do While/Do Until repeat on a condition; Exit For / Exit Do leave early.
  • Delete rows bottom to top.
  • If/ElseIf/Else chain decisions; Select Case handles lists, To ranges and Is comparisons.
  • VBA's And/Or evaluate both sides; guard risky tests with nested If.
  • A Function returns a value by assigning to its own name; a Sub is called without parentheses around its arguments.
  • Arguments are ByRef by default; use ByVal to protect the caller's variables.

Next: VBA Objects: Application, Workbook, Worksheet and Range.