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.
Contents
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
- Binary means that VBA checks the case of the letters – case sensitive.
- 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
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.)
Thanks for the awesome article .. this site is by far the best for VBA.. thanks for all you hard work… I am following your Youtube channel also… Keep up the fantastic work.
Thanks. Glad you like it.
Your articles are amazing.
I am enjoying learning VBA.
Thanks:)
A really useful variation is:
Select Case True
Case (a < b)
Debug.Print "a b)
Debug.Print “a > b”
Case Else
Debug.Print “a = b”
End Select
I think it’s clearer than:
Select Case a
Case Is < b
Debug.Print "a b
Debug.Print “a > b”
Case Else
Debug.Print “a = b”
End Select
but it also allows checking disparate conditions, such as:
Select Case True
Case (a = b)
Debug.Print “a = b”
Case (c = d)
Debug.Print “c = d”
Case (e = f)
Debug.Print “e = f”
Case Else
Debug.Print “No pair is equal”
End Select
If the logic had to be reproduced with ifs, it would be:
If (a = b) Then
Debug.Print “a = b”
Else
If (c = d) Then
Debug.Print “c = d”
Else
If (e = f) Then
Debug.Print “e = f”
Else
Debug.Print “No pair is equal”
End If
End If
End If
Nice code Peter.
I want to know how to handle vlookup errors with case, I evaluate multiple STRING cases but when vlookup returns an error I cannot make case act, I’m using a VARIANT type for the evaluated variable
I love this website and all your websites, videos. I earn a lot. Thank you
Hi Paul, Can you please post example of using Select Case wtih Instr.
Which of the two is faster? (To use in a large set of data)