Day 4 — If, ElseIf and Select Case

Code that always does the same thing is a formula. Code that decides is a macro.

If ... Then ... End If

If condition Then
do this
End If

Sub SimpleIf()
    Dim units As Long
    units = Cells(2, 6).Value

    If units > 2 Then
        Debug.Print "Bulk order:", units
    End If

    If units > 100 Then
        Debug.Print "This line never runs"
    End If
End Sub

ElseIf and Else

VBA checks the branches from the top and takes the first one that is true. Order matters more than people expect.

Sub Grading()
    Dim value As Double
    value = Cells(2, 6).Value * Cells(2, 7).Value

    If value >= 150000 Then
        Debug.Print value, "Large"
    ElseIf value >= 50000 Then
        Debug.Print value, "Medium"
    ElseIf value > 0 Then
        Debug.Print value, "Small"
    Else
        Debug.Print value, "Nothing"
    End If
End Sub

Put the tightest test first Swap the first two branches above and every large order is labelled Medium, because 156000 is also greater than 50000 and VBA stops at the first match. It will not warn you. Silently wrong output is the worst kind.

The operators

Operator	Means	Example
`=`	is equal to	`region = "North"`
`<>`	is not equal to	`region <> "North"`
`>` `<`	greater / less than	`units > 5`
`>=` `<=`	at least / at most	`units >= 5`
`And`	both must be true	`a > 1 And b < 9`
`Or`	either can be true	`a = 1 Or a = 2`
`Not`	flips true and false	`Not found`

One equals sign does two jobs In VBA = means "assign" on the left of a statement and "is equal to" inside a condition. There is no ==. VBA works out which you meant from where it sits.

Sub Combining()
    Dim region As String
    Dim units As Long
    region = Cells(3, 3).Value
    units = Cells(3, 6).Value

    If region = "South" And units > 5 Then
        Debug.Print "Big southern order"
    End If

    If region = "North" Or region = "South" Then
        Debug.Print region & " is a priority region"
    End If

    If Not (units > 10) Then
        Debug.Print "Ten or fewer units"
    End If
End Sub

The one-line If

When there is exactly one thing to do, you can keep it on one line and skip End If. Only do this when it genuinely fits on the line.

Sub OneLiners()
    Dim units As Long
    units = Cells(4, 6).Value

    If units > 10 Then Debug.Print "Bulk"
    If units <= 10 Then Debug.Print "Normal" Else Debug.Print "Bulk again"
End Sub

Select Case

When you are testing the same value over and over, Select Case says it once and reads far better.

Sub RegionZone()
    Dim region As String
    region = Cells(6, 3).Value

    Select Case region
        Case "North", "South"
            Debug.Print region, "Zone 1"
        Case "East"
            Debug.Print region, "Zone 2"
        Case Else
            Debug.Print region, "Zone 3"
    End Select
End Sub

It handles ranges and comparisons too, which is where it really beats a stack of ElseIfs.

Sub ValueBand()
    Dim v As Double
    v = Cells(2, 6).Value * Cells(2, 7).Value

    Select Case v
        Case Is >= 150000
            Debug.Print v, "Large"
        Case 50000 To 149999
            Debug.Print v, "Medium"
        Case Is > 0
            Debug.Print v, "Small"
        Case Else
            Debug.Print v, "Nothing"
    End Select
End Sub

If or Select Case? Testing one value against several possibilities - Select Case. Testing different things in each branch, like units > 5 And region = "North" - If and ElseIf. Use whichever makes the intent obvious to somebody reading it cold.

Try these yourself

Write an If that prints "Laptop order" only when column E on row 2 is Laptop.
Rewrite RegionZone as an If / ElseIf chain. Which version reads better?
Print "Check this" when units are over 10 AND the product is not Mouse.
Build a Select Case on units: 1 To 4 is Small, 5 To 10 is Medium, anything higher is Bulk.
Explain why branch order matters in an If / ElseIf chain, using the Grading example.

← PreviousDay 3 — Cells, Range and Offset Next →Day 5 — For loops - doing it to every row