VBA SELECT CASE

VBA SELECT CASE allows you to test an expression against multiple conditions, and then carry out a series of commands when a matching condition is found.

It's like using lots of IF statements, but easier and clearer to understand.

Here's the basic structure of SELECT CASE

Select Case Expression

   Case Condition_1
      Code Block

   Case Condition_2
      Code Block

   ..
   ..

   Case Condition_n
      Code Block

   Case Else
      Code Block

End Select

Each Case Condition is a test to match against the Expression

At the bottom is a Case Else which is a catch-all if none of the other Case tests have been met. The Case Else is optional.

Each Code Block consist of one or more lines of VBA that are run if the Condition matches the Expression.

SELECT CASE in Action

In this example I'm testing the value of a variable called MyNum. I've initially set this to 5.

As I step through the code (by pressing F8) you can see that each of the Case statements is tested before reaching the 5 towards the bottom. When this match is found, the code under that is run which just writes some text to the Immediate Window in the VB editor.

The value of MyNum is then changed to 6 and I step through the code again. This time I don't have any Case matches so the code uses the Case Else and prints the message that the number is "Greater than Five".

If I left out the Case Else, then no tests match the Expression and nothing is printed.

In this example I'm testing a hard coded value, but this Expression can be the result of a function, input from a MsgBox, a value in a cell, some mathematical expression or just a variable you've assigned a value to.

SELECT CASE With Strings

You can test strings like this:

Sub select_case_string()

    Dim MyStr As String
    
    MyStr = "Three"
    
    Select Case MyStr

        Case "One"
            Debug.Print 1

        Case "Two"
            Debug.Print 2

        Case "Three"
            Debug.Print 3
        
        Case "Four"
            Debug.Print 4
        
        Case "Five"
            Debug.Print 5
    
    End Select

End Sub

SELECT CASE With Multiple Tests

You're not limited to testing a single value against your expression. You can test multiple values by separating them with commas:

Sub select_case_numbers()

    Dim MyNum As Long
    
    MyNum = 2
    
    Select Case MyNum

        Case 1, 3, 5
            Debug.Print "Odd"

        Case 2, 4
            Debug.Print "Even"

    End Select

End Sub

Sub select_case_strings()

    Dim MyStr As String
    
    MyStr = "Three"
    
    Select Case MyStr

        Case "One", "Three", "Five"
            Debug.Print "Odd"

        Case "Two", "Four"
            Debug.Print "Even"
    
    End Select

End Sub

Test a Range of Numbers

You can test if a number is in a range using To:

Sub select_case_to_num()

    Dim MyNum As Long
    
    MyNum = 50
    
    Select Case MyNum

        Case 51 To 100 ' Must be low number to high number
            Debug.Print "> 50 and <= 100"

        Case 0 To 50
            Debug.Print ">= 0 and <= 50"
            
    End Select

End Sub

Be careful with the Case statements as the ranges are inclusive of the starting and ending numbers, and make sure your ranges don't overlap.

Also, you must specify the lowest number first. Writing Case 100 To 51 won't work properly.

Test a Range of Characters

As with numbers, you can test if a character (or string) is in a given 'range' of characters using To:

Sub select_case_to_char()

    Dim MyStr As String
    
    MyStr = "f"
    
    Select Case MyStr

        Case "a" To "m"
            Debug.Print "a to m"

        Case "n" To "z"
            Debug.Print "n to z"
            
    End Select

End Sub

Comparison Tests : IS

You can use the comparison operators =, <>, <, <=, >, >= to test values:

You need to use the Is keyword before the comparison operator. If you don't type it, it's entered in for you automatically.

Sub select_case_is()

    Dim MyNum As Long
    
    MyNum = 75
    
    Select Case MyNum

        Case Is > 100
            Debug.Print "Greater than 100"

        Case Is > 75
            Debug.Print "Greater than 75"

        Case Is > 50
            Debug.Print "Greater than 50"
            
        Case Is > 25
            Debug.Print "Greater than 25"
        
        Case Is > 0
            Debug.Print "Greater than 0"
            
        Case Else
            Debug.Print "Less than or equal to 0"
    
    End Select

End Sub

Mixing Case Tests

It is possible to mix the different types of Case tests, like so:

Sub select_case_mixed_test()

    Dim MyNum As Long
    
    MyNum = -9
    
    Select Case MyNum

        Case Is > 100
            Debug.Print "Greater than 100"

        Case 50 To 100
            Debug.Print ">= 50 and <= 100"

        Case 1 To 49
            Debug.Print ">= 1 and <= 49"
                  
        Case 0
            Debug.Print "Zero"
                        
        Case Else
            Debug.Print "Less than 0"
    
    End Select

End Sub

As long as the data type for each Case test matches the data type being tested, you are ok with this.

Using Variant Data Type

If you aren't sure what data type you are going to be testing then you need to use a Variant variable.

This can happen for example, if you read the contents of a cell, and aren't sure if you are getting text or a number.

Sub select_case_variant()

    Dim MyVar As Variant
    
    MyVar = "One"
    
    Select Case MyVar

        Case "One"
            Debug.Print "One"
        
        Case 1
            Debug.Print "1"

    End Select

End Sub

If I declared MyVar as Long, I'd get an error when the code gets to the first test Case "One". It will only generate an error when the code runs, not before, so bugs can sneak into your code this way if you're not careful.

Download Sample Workbook