Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 37 ยท Control Flow and Procedures

If...Then...Else in VBA

Make decisions in VBA with If, ElseIf, Else, nested If, single-line If, IIf and comparison tips, including how to avoid non-short-circuit errors.

So far your code runs top to bottom. Decisions let it choose a path based on a condition. The If statement is the workhorse: it runs a block only when a Boolean expression is True.

Block If

VB
Sub CheckTemperature()
    Dim temp As Double
    temp = 31.5
    If temp > 30 Then
        MsgBox "Hot day"
    End If
End Sub

The block begins with If condition Then and ends with End If. Everything between is skipped when the condition is False. Indent the body.

Else

VB
If temp > 30 Then
    Debug.Print "Hot"
Else
    Debug.Print "Not hot"
End If

ElseIf: many branches

VB
Dim grade As String
Dim score As Long
score = 83

If score >= 90 Then
    grade = "A"
ElseIf score >= 80 Then
    grade = "B"
ElseIf score >= 70 Then
    grade = "C"
Else
    grade = "F"
End If

VBA tests the conditions from top to bottom and runs the first True branch only. Order matters: put the most specific or highest threshold first.

Single-line If

For one short statement you can skip End If:

VB
If x < 0 Then x = 0
If isDone Then Exit Sub
If a > b Then t = a Else t = b       ' single-line Else, hard to read

Prefer the block form once anything gets longer than one simple action.

Combining conditions

Join tests with And, Or and Not:

VB
If age >= 18 And age < 65 Then Debug.Print "Working age"
If city = "Paris" Or city = "Rome" Then Debug.Print "Europe"
If Not IsEmpty(Range("A1").Value) Then Debug.Print "A1 has content"

No short-circuiting

Both sides of And and Or are evaluated, even when the answer is already known. This code fails when i is zero:

VB
If i <> 0 And 100 / i > 5 Then ...        ' error 11 when i = 0

Nest the tests instead:

VB
If i <> 0 Then
    If 100 / i > 5 Then
        Debug.Print "ok"
    End If
End If

Same for If obj Is Nothing Or obj.Count = 0, which fails when obj is Nothing.

Nested If

An If inside an If handles dependent decisions. Keep nesting to two or three levels; beyond that, use ElseIf, Select Case or split the logic into functions. An early exit often flattens code:

VB
Sub Process(ByVal fileName As String)
    If fileName = "" Then Exit Sub        ' guard clause
    If Dir(fileName) = "" Then Exit Sub   ' file missing
    ' main work here, no deep nesting
End Sub

IIf: inline choice

IIf(condition, valueIfTrue, valueIfFalse) returns one of two values:

VB
Dim label As String
label = IIf(qty = 1, "item", "items")

Caution: IIf evaluates both result arguments every time. IIf(x = 0, 0, 10 / x) still divides by zero. Use a block If when the false branch is unsafe.

Testing special values

Test Function
Number or numeric text IsNumeric(x)
Valid date IsDate(x)
Empty cell/variable IsEmpty(x) or x = ""
Error value in a cell IsError(x)
Object not set obj Is Nothing
Null from a database IsNull(x)

Comparing strings and floating-point numbers

  • Text comparison is case sensitive unless you use Option Compare Text or LCase.
  • Do not compare decimals with =. Use a tolerance: If Abs(a - b) < 0.00001 Then.

Stand-in: a grading function

This JavaScript stand-in (not VBA) reproduces the ElseIf ladder and the guard-clause idea so you can change the thresholds.

JS Stand-in: ElseIf ladder and guard clause

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. Which keyword ends a block If?

    A multi-line If...Then block always ends with End If.
  2. 2. In an ElseIf ladder, how many branches run?

    VBA stops after the first True condition.
  3. 3. What is wrong with If i <> 0 And 100 / i > 5?

    VBA does not short-circuit. Nest the Ifs.
  4. 4. Why can IIf(x = 0, 0, 10 / x) fail?

    IIf is a function: both arguments are computed before it picks one.

Summary

  • If...Then...ElseIf...Else...End If chooses one branch.
  • Combine conditions with And, Or, Not but remember: no short-circuit.
  • Use guard clauses (Exit Sub) to avoid deep nesting.
  • IIf is compact but evaluates both outcomes.

Next: Select Case