VBA Select Case: Complete Guide with Examples (2025)

Updated: January 2025•8 min read

Quick Answer: Select Case tests one expression against multiple values. Syntax: Select Case variable, then Case value for each condition, ending with End Select. Cleaner than multiple If-ElseIf statements.

What is Select Case?

Select Case is a VBA control structure that evaluates a single expression and executes different code blocks based on its value. It's an elegant alternative to multiple If-ElseIf statements when testing one variable against many possible values.

Simple Example:

Sub GradingSystem()
    Dim score As Integer
    Dim grade As String
    
    score = Range("A1").Value
    
    Select Case score
        Case 90 To 100
            grade = "A"
        Case 80 To 89
            grade = "B"
        Case 70 To 79
            grade = "C"
        Case 60 To 69
            grade = "D"
        Case Else
            grade = "F"
    End Select
    
    MsgBox "Grade: " & grade
End Sub

✓ Use Select Case When:

  • •Testing one variable against multiple values
  • •You have 3+ conditions to check
  • •Checking ranges of values
  • •Code readability is important

✓ Use If-Then When:

  • •Testing different variables
  • •Complex AND/OR logic needed
  • •Only 1-2 conditions to check
  • •Conditions are complex expressions

Select Case Syntax

Select Case testExpression
    Case value1
        ' Code for value1
    Case value2, value3
        ' Code for value2 or value3
    Case value4 To value5
        ' Code for range
    Case Is > value6
        ' Code for comparison
    Case Else
        ' Default code
End Select

Single Value Matching

Select Case dayNumber
    Case 1
        dayName = "Monday"
    Case 2
        dayName = "Tuesday"
    Case 3
        dayName = "Wednesday"
End Select

Multiple Values (Comma-Separated)

Select Case month
    Case 12, 1, 2
        season = "Winter"
    Case 3, 4, 5
        season = "Spring"
    Case 6, 7, 8
        season = "Summer"
    Case 9, 10, 11
        season = "Fall"
End Select

Range Matching (To Keyword)

Select Case age
    Case 0 To 12
        category = "Child"
    Case 13 To 19
        category = "Teenager"
    Case 20 To 64
        category = "Adult"
    Case Is >= 65
        category = "Senior"
End Select

Comparison Operators (Is Keyword)

Select Case score
    Case Is >= 90
        grade = "A"
    Case Is >= 80
        grade = "B"
    Case Is >= 70
        grade = "C"
    Case Is >= 60
        grade = "D"
    Case Else
        grade = "F"
End Select

String Matching

Select Case UCase(userRole)  ' Convert to uppercase
    Case "ADMIN"
        access = "Full Access"
    Case "MANAGER"
        access = "Edit Access"
    Case "USER"
        access = "Read Only"
    Case Else
        access = "No Access"
End Select

Practical Examples

Example 1: Price Discount Calculator

Sub CalculateDiscount()
    Dim orderTotal As Double
    Dim discountRate As Double
    
    orderTotal = Range("A1").Value
    
    Select Case orderTotal
        Case 0 To 99.99
            discountRate = 0
        Case 100 To 499.99
            discountRate = 0.05  ' 5% discount
        Case 500 To 999.99
            discountRate = 0.1   ' 10% discount
        Case Is >= 1000
            discountRate = 0.15  ' 15% discount
        Case Else
            discountRate = 0
    End Select
    
    Range("B1").Value = orderTotal * discountRate
End Sub

Example 2: Month Name Converter

Sub GetMonthName()
    Dim monthNum As Integer
    Dim monthName As String
    
    monthNum = InputBox("Enter month number (1-12):")
    
    Select Case monthNum
        Case 1: monthName = "January"
        Case 2: monthName = "February"
        Case 3: monthName = "March"
        Case 4: monthName = "April"
        Case 5: monthName = "May"
        Case 6: monthName = "June"
        Case 7: monthName = "July"
        Case 8: monthName = "August"
        Case 9: monthName = "September"
        Case 10: monthName = "October"
        Case 11: monthName = "November"
        Case 12: monthName = "December"
        Case Else
            monthName = "Invalid month"
    End Select
    
    MsgBox monthName
End Sub

Example 3: Status Color Formatter

Sub FormatByStatus()
    Dim status As String
    Dim cell As Range
    
    For Each cell In Range("A2:A100")
        status = UCase(cell.Value)
        
        Select Case status
            Case "COMPLETE", "DONE", "FINISHED"
                cell.Interior.Color = RGB(144, 238, 144)  ' Light green
            Case "IN PROGRESS", "WORKING"
                cell.Interior.Color = RGB(255, 255, 153)  ' Light yellow
            Case "PENDING", "WAITING"
                cell.Interior.Color = RGB(255, 218, 185)  ' Light orange
            Case "CANCELLED", "FAILED"
                cell.Interior.Color = RGB(255, 182, 193)  ' Light red
            Case Else
                cell.Interior.ColorIndex = xlNone
        End Select
    Next cell
End Sub

Example 4: File Extension Handler

Sub ProcessFile()
    Dim fileName As String
    Dim fileExt As String
    Dim action As String
    
    fileName = Range("A1").Value
    fileExt = LCase(Right(fileName, 4))
    
    Select Case fileExt
        Case ".xlsx", ".xlsm", ".xls"
            action = "Open in Excel"
        Case ".docx", ".doc"
            action = "Open in Word"
        Case ".pdf"
            action = "Open in PDF Reader"
        Case ".jpg", ".png", ".gif"
            action = "Open in Image Viewer"
        Case Else
            action = "Unknown file type"
    End Select
    
    MsgBox action
End Sub

Frequently Asked Questions

What is Select Case in VBA?

Select Case is a VBA control structure that tests a single expression against multiple values. It's cleaner than multiple If-ElseIf statements when checking one variable against many possible values. Syntax: Select Case testExpression, Case value, Case Else, End Select.

When should I use Select Case instead of If Then?

Use Select Case when testing one variable against multiple specific values (3+ conditions). Use If-Then-Else for complex conditions with AND/OR logic, or when testing different variables. Select Case is more readable for simple value matching.

How do I check multiple values in one Case?

Use commas to separate multiple values: Case 1, 2, 3. Use To for ranges: Case 1 To 10. Use Is for comparisons: Case Is > 100. You can combine them: Case 1, 5, 10 To 20, Is > 50.

What is Case Else in VBA?

Case Else is the default case that runs when no other Case matches. It's optional but recommended as a catch-all. Similar to Else in If statements. Always place it as the last Case before End Select.

Can I use Select Case with strings?

Yes, Select Case works with strings. Example: Select Case userName, Case "Admin", Case "Guest". String matching is case-sensitive unless you use LCase() or UCase() to normalize.

How do I use ranges in Select Case?

Use the To keyword: Case 1 To 10 matches values from 1 to 10 inclusive. Use Is for comparisons: Case Is >= 100 matches 100 and above. Combine them: Case 1 To 50, Is > 100.

Can I nest Select Case statements?

Yes, you can nest Select Case inside another Select Case or inside If statements. However, deeply nested structures can be hard to read. Consider refactoring into separate functions if nesting gets complex.

What happens if multiple Cases match?

Only the first matching Case executes, then VBA exits the Select Case block. Unlike some languages, VBA doesn't fall through to subsequent Cases. Order your Cases from most specific to most general.

Need Help Writing VBA Code?

Our AI-powered VBA Code Generator creates working VBA code from plain English descriptions. No coding knowledge required!

✓ No credit card required ✓ 5 free generations ✓ Instant results