Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 37 ยท Control Flow and Procedures

Select Case in VBA

Replace long ElseIf ladders with Select Case: match values, lists, ranges, Is comparisons and text, use Case Else, and test conditions with Select Case True.

When one value decides between many outcomes, a long ElseIf ladder becomes noisy. Select Case evaluates an expression once and compares it against a list of Case clauses. It reads like a table and is easy to extend.

Basic form

VB
Sub DayType()
    Dim d As Long
    d = Weekday(Date, vbMonday)        ' 1 = Monday ... 7 = Sunday
    Select Case d
        Case 1
            Debug.Print "Start of the week"
        Case 5
            Debug.Print "Friday"
        Case 6, 7
            Debug.Print "Weekend"
        Case Else
            Debug.Print "Midweek"
    End Select
End Sub

The expression after Select Case is evaluated once. VBA checks each Case from the top and runs the first matching block, then jumps to End Select. There is no fall-through and no Break needed. Case Else is optional but wise: it catches the unexpected.

Ways to match

Pattern Meaning
Case 3 equals 3
Case 1, 2, 3 any of the listed values
Case 10 To 20 within the inclusive range
Case Is >= 100 comparison with the tested value
Case "red" text equality (case sensitive by default)
Case "a" To "m" text range

You can mix them in one clause: Case 1 To 3, 7, Is > 100.

VB
Function Shipping(ByVal weightKg As Double) As Currency
    Select Case weightKg
        Case Is <= 0
            Shipping = 0
        Case 0 To 1
            Shipping = 4.5
        Case Is <= 5
            Shipping = 8
        Case Is <= 20
            Shipping = 15
        Case Else
            Shipping = 30
    End Select
End Function

Because the first match wins, the order of ranges matters. Notice Case 0 To 1 includes 1, and Case Is <= 5 therefore only handles values above 1 that were not matched earlier.

Text and case sensitivity

VB
Select Case LCase(Trim(answer))
    Case "y", "yes", "ok"
        Debug.Print "confirmed"
    Case "n", "no"
        Debug.Print "declined"
    Case Else
        Debug.Print "unclear"
End Select

Normalising with LCase and Trim first is simpler than adding every spelling. Option Compare Text at the top of the module also makes text cases case-insensitive.

Select Case True: conditions in each Case

Different tests on different variables do not fit the standard form, but you can select True and put Boolean expressions in the cases:

VB
Select Case True
    Case score >= 90 And attendance > 0.9
        grade = "A+"
    Case score >= 90
        grade = "A"
    Case isLate
        grade = "Late"
    Case Else
        grade = "-"
End Select

The first Case whose expression is True wins. This is the closest VBA gets to a clean multi-way condition and it avoids a long ElseIf chain.

Nesting and exiting

Select Case blocks can be nested. Use Exit Sub or Exit Function inside a case to leave early; there is no Exit Select. If several cases share code, list their values together in one Case or move the code into a procedure.

Choose and Switch: expression versions

For a value-to-value lookup you can use functions instead:

VB
Debug.Print Choose(2, "one", "two", "three")                 ' two (1-based index)
Debug.Print Switch(x < 0, "negative", x = 0, "zero", x > 0, "positive")

Switch returns the value after the first True test (Null if none). Like IIf, every argument is evaluated, so avoid risky expressions.

Stand-in: the Select Case logic in JavaScript

The JavaScript stand-in (not VBA) models the shipping function and Select Case True, so you can test boundary values (0, 1, 5, 20).

JS Stand-in: ranges and first-match-wins

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

No output.

In JavaScript, case falls through without break; VBA never does, which is why return is used above to mimic the single winner.

When to use which

  • Two outcomes: If...Else.
  • One value, several discrete or range outcomes: Select Case.
  • Different variables in each test: ElseIf, or Select Case True.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens after a Case block runs?

    VBA never falls through; only the first matching Case runs.
  2. 2. Which Case matches 1, 2 and 3?

    Use Case 1 To 3 (or Case 1, 2, 3).
  3. 3. What does Case Is >= 100 compare?

    Is stands for the tested expression, used with a comparison operator.
  4. 4. How do you test different variables in each Case?

    Select Case True picks the first Case whose expression evaluates to True.

Summary

  • Select Case tests one expression against ordered Case clauses; the first match wins, no fall-through.
  • Match values, lists, To ranges and Is comparisons; add Case Else.
  • Normalise text before matching; use Select Case True for mixed conditions.
  • Choose and Switch offer expression-style lookups.

Next: For...Next Loops