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
Enter your email address below to download the sample workbook.
You can download a sample workbook with lots of examples of using SELECT CASE.
Nice illustration
Thanks
An excellent tutorial Phil !
For those doing serious VBA programming, I would recommend SELECT CASE as generally a much better structure to use than IF . . . THEN, especially for more complex decision trees.
It makes the code far easier to read, understand and troubleshoot.
Thanks Harold.
I saw this newsletter header and thought “I already know this, but I’ll skim it anyway.” And then you illuminate how little I knew about select case statements. Thank you for the post!
No worries Ryan, glad you found it useful.
One other test that I often use in a userform when I need to find out what option button was selected.
Private Sub cbOK_Click() Select Case True Case obDeleteRows DeleteEmptyRows Case obDeleteColumns DeleteEmptyColumns End Select End SubThanks Sunny
Thank you very much Phil for this awesome tutorial.
You’re welcome 🙂