Excel VBA Lesson 13: Mastering the Select Case Statement in Excel VBA

13.1 What is the Select Case Statement?

The Select Case control structure is a powerful decision-making tool in VBA that provides a cleaner alternative to complex If...Then...ElseIf statements. While both structures evaluate conditions, they differ in their approach:

• If...Then...ElseIf evaluates multiple expressions with different conditions
• Select Case evaluates a single expression against multiple possible values

Select Case is particularly useful when:

• You have more than three or four conditions to evaluate
• You're testing the same variable against multiple values
• You want to improve code readability and maintainability

13.2 Select Case Syntax

The basic syntax of the Select Case statement is:

Select Case test_expression
  Case condition_1
    ' Code to execute if condition_1 is true
  Case condition_2
    ' Code to execute if condition_2 is true
  Case condition_3
    ' Code to execute if condition_3 is true
  Case Else
    ' Code to execute if no conditions are met
End Select
    

13.3 Practical Example: Student Grading System

Let's expand on the student grading example with more robust code and explanations:

Private Sub CommandButton1_Click()
  ' Declare variables with proper data types
  Dim mark As Single
  Dim grade As String
  
  ' Get the mark from cell A1
  mark = Cells(1, 1).Value
  
  ' Format the cells for better presentation
  With Range("A1:B1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
  End With
  
  ' Evaluate the mark using Select Case
  Select Case mark
    Case 0 To 19.99
      grade = "F"
      Cells(1, 2).Interior.Color = RGB(255, 200, 200) ' Light red
    Case 20 To 29.99
      grade = "E"
      Cells(1, 2).Interior.Color = RGB(255, 225, 200) ' Light orange
    Case 30 To 39.99
      grade = "D"
      Cells(1, 2).Interior.Color = RGB(255, 255, 200) ' Light yellow
    Case 40 To 59.99
      grade = "C"
      Cells(1, 2).Interior.Color = RGB(200, 255, 200) ' Light green
    Case 60 To 79.99
      grade = "B"
      Cells(1, 2).Interior.Color = RGB(200, 200, 255) ' Light blue
    Case 80 To 100
      grade = "A"
      Cells(1, 2).Interior.Color = RGB(200, 255, 255) ' Light cyan
    Case Else
      grade = "Invalid!"
      Cells(1, 2).Interior.Color = RGB(200, 200, 200) ' Light gray
  End Select
  
  ' Output the grade
  Cells(1, 2) = grade
End Sub
    

13.4 Advanced Select Case Techniques

1. Multiple Conditions in a Single Case

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

Select Case dayNumber
  Case 1, 7
    dayType = "Weekend"
  Case 2 To 6
    dayType = "Weekday"
  Case Else
    dayType = "Invalid day"
End Select
    

2. Using Comparison Operators

You can use comparison operators with the Is keyword:

Select Case temperature
  Case Is < 0
    state = "Freezing"
  Case Is < 10
    state = "Cold"
  Case Is < 20
    state = "Cool"
  Case Is < 30
    state = "Warm"
  Case Else
    state = "Hot"
End Select
    

3. Combining String Patterns

Select Case works well with string patterns:

Select Case UCase(productCode)
  Case "A" To "C"
    category = "Electronics"
  Case "D" To "F"
    category = "Furniture"
  Case "G", "H", "J"
    category = "Clothing"
  Case Else
    category = "Other"
End Select
    

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

Best Practices for Select Case

1. Always include a Case Else to handle unexpected values
2. Keep Case statements simple - move complex logic to separate functions
3. Order Case statements from most specific to most general
4. Use comments to explain non-obvious conditions
5. Consider using enumerations for better readability with named constants

Common Mistakes to Avoid

• Overlapping ranges: VBA uses the first matching Case, so order matters
• Missing Case Else: Can lead to unhandled scenarios
• Complex expressions: Keep Case conditions simple for readability
• Forgetting End Select: Always properly close the structure

Real-World Application: Shipping Cost Calculator

Here's a practical example of calculating shipping costs based on weight:

Function CalculateShipping(weight As Double, destination As String) As Currency
  Dim baseRate As Currency
  Dim multiplier As Double
  
  ' Determine base rate by destination
  Select Case UCase(destination)
    Case "LOCAL"
      baseRate = 5.00
    Case "DOMESTIC"
      baseRate = 10.00
    Case "INTERNATIONAL"
      baseRate = 25.00
    Case Else
      CalculateShipping = -1 ' Error code for invalid destination
      Exit Function
  End Select
  
  ' Determine weight multiplier
  Select Case weight
    Case 0 To 0.5
      multiplier = 1.0
    Case 0.51 To 2
      multiplier = 1.5
    Case 2.01 To 5
      multiplier = 2.0
    Case Is > 5
      multiplier = 3.0
    Case Else
      CalculateShipping = -1 ' Error code for invalid weight
      Exit Function
  End Select
  
  CalculateShipping = baseRate * multiplier
End Function
    

Summary

✅ In This Lesson, You Learned:

🔗 Related Resources

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