Lesson 11: Mastering the Select Case Statement in Excel VBA

The Select Case statement is one of the most powerful and efficient decision-making structures in Excel VBA. It provides a cleaner alternative to complex If...Then...Else statements when you need to evaluate a single expression against multiple possible values.

11.1 Why Use Select Case?

While If...Then...Else statements work well for simple conditions, they can become:

• Difficult to read with multiple conditions
• Hard to maintain and debug
• Inefficient for evaluating a single variable against many values

The Select Case structure solves these problems by providing a clear, organized way to handle multiple conditions.

Basic Select Case Syntax

The structure of Select Case...End Select is as follows:

Select Case testExpression
    Case value1
        statements
    Case value2
        statements
    Case value3
        statements
    Case Else
        statements
End Select
    

Key features of Select Case:

• Evaluates a single expression once at the top
• Compares against each Case value in order
• Executes only the first matching Case block
• Case Else handles any values not explicitly covered
• End Select marks the end of the structure

Example 11.1: Grade Evaluation

This example demonstrates how to use Select Case to assign remarks based on letter grades. This is ideal when you have discrete values to compare against.

Private Sub CommandButton1_Click()
    Dim grade As String
    grade = InputBox("Enter the grade (A, A-, B, C, E or F)")
    
    Select Case grade
        Case "A"
            MsgBox "High Distinction"
        Case "A-"
            MsgBox "Distinction"
        Case "B"
            MsgBox "Credit"
        Case "C"
            MsgBox "Pass"
        Case Else
            MsgBox "Fail"
    End Select
End Sub
    

Key Points:

• Perfect for exact matches against string values
• Case-sensitive by default ("A" ≠ "a")
• Order of cases doesn't matter for exact matches
• Case Else handles any grades not listed explicitly

Example 11.2: Mark to Grade Conversion

This example shows how to use range expressions in Case statements to convert numerical marks to letter grades.

Private Sub CommandButton1_Click()
    Dim mark As Single
    Dim grade As String
    
    mark = InputBox("Enter the mark (0-100)")
    
    Select Case mark
        Case 0 To 29
            grade = "F"
        Case 30 To 49
            grade = "E"
        Case 50 To 59
            grade = "D"
        Case 60 To 69
            grade = "C"
        Case 70 To 79
            grade = "B"
        Case 80 To 100
            grade = "A"
        Case Else
            grade = "Error! Mark must be between 0 and 100"
    End Select
    
    MsgBox "Grade: " & grade
End Sub
    

Key Points:

• Uses the To keyword to specify ranges
• Ranges can be numbers, characters, or dates
• Case Else handles invalid inputs (marks outside 0-100)
• More readable than equivalent nested If statements

Example 11.3: Creating a Custom Grade Function

This example converts the previous code into a reusable function that can be called from any Excel worksheet cell.

Function grade(mark As Integer) As String
    Select Case mark
        Case 0 To 29
            grade = "F"
        Case 30 To 49
            grade = "E"
        Case 50 To 59
            grade = "D"
        Case 60 To 69
            grade = "C"
        Case 70 To 79
            grade = "B"
        Case 80 To 100
            grade = "A"
        Case Else
            grade = "Error!"
    End Select
End Function
    

Key Points:

• Can be used directly in Excel formulas like =grade(B2)
• Automatically recalculates when input cells change
• More efficient than VLOOKUP for simple conversions
• Easier to modify than complex nested IF formulas

11.2 Advanced Select Case Techniques

Multiple Conditions in a Single Case

You can test for multiple values in a single Case statement using commas:

Select Case dayOfWeek
    Case 1, 7
        MsgBox "Weekend"
    Case 2 To 6
        MsgBox "Weekday"
    Case Else
        MsgBox "Invalid day"
End Select
    

Comparison Operators

Use the Is keyword with comparison operators:

Select Case age
    Case Is < 13
        MsgBox "Child"
    Case 13 To 19
        MsgBox "Teenager"
    Case Is >= 20
        MsgBox "Adult"
End Select
    

String Pattern Matching

Use Like operator with wildcards for pattern matching:

Select Case fileName
    Case "*.xls*"
        MsgBox "Excel file"
    Case "*.doc*"
        MsgBox "Word document"
    Case "*.ppt*"
        MsgBox "PowerPoint file"
End Select
    

11.3 When to Use Select Case vs. If...Then...Else

Scenario	Best Choice	Reason
Single variable with multiple possible values	Select Case	More readable and efficient
Complex conditions with multiple variables	If...Then...Else	Select Case only tests one expression
Range-based conditions	Select Case	Simpler syntax with To keyword
Boolean (True/False) conditions	If...Then...Else	More straightforward for simple tests

Common Mistakes to Avoid

1. Forgetting End Select: Every Select Case must have a matching End Select.
2. Overlapping Cases: VBA uses the first matching Case, so order matters when ranges overlap.
3. Case Sensitivity: String comparisons are case-sensitive by default (use UCase or LCase if needed).
4. Missing Case Else: Always include a Case Else to handle unexpected values gracefully.
5. Complex Expressions: The test expression should be simple - move complex logic outside the Select Case.

Performance Considerations

While Select Case is generally efficient, keep these points in mind:

• The test expression is evaluated only once at the beginning
• Cases are evaluated in order until a match is found
• Place the most likely cases first for better performance
• For very large numbers of cases, consider alternative approaches like dictionaries

Practical Applications

Select Case is useful in many real-world scenarios:

• Data validation and cleaning
• Workflow routing (different actions for different statuses)
• Report generation with different formats
• User interface navigation
• Error handling and status messages

Summary: Key Takeaways

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial