The Select Case statement is an alternative way to write If/ElseIf statements.

You will find a Select Case statement equivalent in most popular programming languages. In many languages, the equivalent statement is the Switch statement. For example, the languages Java, C#, C++ and Javascript all have a switch statement.

Case Statement Quick Guide

Case Options	Description	Examples
Is	Use with operators =, >,<,<>,>=,<=	Case Is = 5 Case Is = "Apple" Case Is > 5 Case Is <= 10
To	Use for a range of numbers	Case 5 To 10 Case 85 To 99 Case "A" To "D"
Comma	Use the comma to include multiple conditions for one case	Case 1, 3, 9, 11 Case 1, Is > 20, 10 To 15
No operator	The same as "Is ="	Case 5 Case "Apple"

Select Case Format

The format of the VBA Select Case statement is a follows:

Select Case [variable]
    Case [condition 1]
        [Statement 1]
    Case [condition 2]
        [Statement 2]
    Case [condition n]
        [Statement n]
    Case Else
        [Statement else]
End Select

 

The following is a simple example of using the Select Case Statement:

' https://excelmacromastery.com/
Public Sub Select_Case_Example()

    ' Read value from cell A1 on sheet1
    Dim airportCode As String
    airportCode = Sheet1.Range("A1").Value
    
    ' Print the name of the airport to the Immediate Window(Ctrl + G)
    Select Case airportCode
        Case "LHR"
            Debug.Print "London Heathrow"
        Case "JFK"
            Debug.Print "John F Kennedy"
        Case "SIN"
            Debug.Print "Singapore"
    End Select

End Sub

 

The code below is the equivalent If statement:

' https://excelmacromastery.com/
Public Sub If_Example()

    ' Read value from cell A1 on sheet1
    Dim airportCode As String
    airportCode = Sheet1.Range("A1").Value
    
    ' Print the name of the airport to the Immediate Window(Ctrl + G)
    If airportCode = "LHR" Then
            Debug.Print "London Heathrow"
    ElseIf airportCode = "JFK" Then
            Debug.Print "John F Kennedy"
    ElseIf airportCode = "SIN" Then
            Debug.Print "Singapore"
    End If

End Sub

We use the Select Case statement because it provides us with more flexibility than an If statement. We will see more above this below.

Using the Colon Operator

We can use the colon symbol “:” to make our Case statements look neater. The colon symbol allows us to place two lines VBA lines of code on one line. They are still treated as two lines by VBA but the code looks neater:

Select Case airportCode
    Case "LHR": Debug.Print "London Heathrow"
    Case "JFK": Debug.Print "John F Kennedy"
    Case "SIN": Debug.Print "Singapore"
End Select

 

Code Between Case Statements

In the previous examples we had just one line of code for each Case Condition. You can have as many lines of code as you want. However it is good practice to keep it to one line if possible. The purpose of the Select Case statement is to make the code readable.

In the following example, we have multiple lines of code for the Case “London”:

' https://excelmacromastery.com/
Sub MultiLine()

    Dim city As String
    city = Sheet1.Range("A1").Value

    Select Case city
        Case "London"
            ' would be better in another sub
            Count = Count + 1
            x = 6
            a = 5
        Case Else
            Debug.Print "other city"
    End Select
    
End Sub

 

If we have multiple lines, we could place them in a sub and then call this sub:

Select Case city
    Case "London"
        Call UpdateValues
    Case Else
        Debug.Print "other city"
End Select

 

Case Else

The Case Else statement is used with Select Case. It is the equivalent of the Else statement used with If. In simple terms, it means “if no other options were selected then select this one”.

 

Case Else is often used to check that a valid value was supplied. In the example below, it is used to check if the Airport Code was valid:

' https://excelmacromastery.com/
Public Sub Select_Case_Else()

    ' Read value from cell A1 on sheet1
    Dim airportCode As String
    airportCode = Sheet1.Range("A1").Value
    
    ' Print the name of the airport to the Immediate Window (Ctrl + G)
    Select Case airportCode
        Case "LHR"
            Debug.Print "London Heathrow"
        Case "JFK"
            Debug.Print "John F Kennedy"
        Case "SIN"
            Debug.Print "Singapore"
        Case Else
            MsgBox "The airport code is not valid.", vbInformation
    End Select

End Sub

 

Using Select Case with Numbers

We can use the To keyword to specify a range of numbers:

' https://excelmacromastery.com/
Select Case marks
    Case 85 To 100
        Debug.Print "High Distinction"
    Case 75 To 84
        Debug.Print "Distinction"
    Case 55 To 74
        Debug.Print "Credit"
    Case 40 To 54
        Debug.Print "Pass"
    Case Else
         Debug.Print "Fail"
End Select

 

Select Case Is

We can use the Is keyword if we want to use the operators like =, >,< etc.

In the below example, I have rewritten the previous Select Case statement using Is instead of To:

' https://excelmacromastery.com/
Select Case marks

    Case Is >= 85
        Debug.Print "High Distinction"
    Case Is >= 75
        Debug.Print "Distinction"
    Case Is >= 55
        Debug.Print "Credit"
    Case Is >= 40
        Debug.Print "Pass"
    Case Else
        ' For all other marks
        Debug.Print "Fail"
        
End Select

 

We don’t need to use the Is keyword when using equals. The two lines below are equivalent:

Case "LHR"
Case Is = "LHR"

 

So are these two:

Case 10
Case Is = 10

 

Select Case Multiple Values

We can have multiple case conditions on one line. We simply use the comma to separate them:

Case Is > 85, 70 To 75, 83

 

Case 2, 4, 6, 8
    Debug.Print "Even numbers"
Case 1, 3, 5, 7
    Debug.Print "Odd numbers"

 

The following is an example of using multiple strings:

' https://excelmacromastery.com/
Public Sub Select_Case_Multi()

    Dim city As String
    ' Change value to test
    city = "Dublin"
    
    ' Print the name of the airport based on the code
    Select Case city
        Case "Paris", "London", "Dublin"
            Debug.Print "Europe"
        Case "Singapore", "Hanoi"
            Debug.Print "Asia"
        Case Else
            MsgBox "The city is not valid.", vbInformation
    End Select

End Sub

 

Comparing Upper and Lower Case

We can use Option Compare at the top of the VBA module. This affects how strings are compared within that module.

We can use Binary or Text with Option Compare.

Option Compare Binary

Option Compare Text

 

1. Binary means that VBA checks the case of the letters – case sensitive.
2. Text means that VBA ignores the case of the letters – not case sensitive.

 

If we set “Option Compare Binary” then the following If and Case statements will evaluate to false.

If we set “Option Compare Text” they will evaluate to true:

city = "Dublin"

' true for "Option Compare Text"
' false for "Option Compare binary"
If city = "DUBLIN" Then
End If

Select Case city
    ' true for "Option Compare Text"
    ' false for "Option Compare binary"
    Case "DUBLIN"
End Select

 

You can try the following example. Change between Binary and Text and check the results:

' Change between "Binary" and "Text" and compare results
' https://excelmacromastery.com/
Option Compare Binary

Private Sub Select_Case_Multi()

    Dim city As String
    city = "dublin"
    
    ' Print the name of the airport based on the code to the
    ' Immediate Window (Ctrl + G).
    Select Case city
        Case "DUBLIN"
            Debug.Print "Europe"
        Case Else
            Debug.Print "The city is not valid."
    End Select

End Sub

 

Related Articles

VBA If Statement

VBA MessageBox

 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)