- With SELECT CASE, you can test multiple conditions, especially when you are working more than two.
- The code you write with SELECT CASE is way more structured than standard conditional code.
- It is easier to modify it when you need to adjust one or more of the values in the code.
What is VBA Select Case
VBA SELECT CASE is a statement to test multiple conditions. In this statement, you can specify one condition and then specify a code to execute if that condition is true and then specify a second condition and a code to run if that condition is true. In this way, you can specify multiple conditions and multiple statements.
The syntax for select case
Below is the syntax that you need to follow to write a select case statement.
Select Case Test Expression Case (Condition to Test) Statement1 [Line of Code to Run if CASE 1 is TRUE] Case (Condition to Test) Statement1 [Line of Code to Run if CASE 2 is TRUE] Case Else Statement [Line of Code to Run if no CASE is TRUE] End Select
In SELECT CASE syntax starts with the keyword “Select” and ends with “End Select”. To understand its syntax, we need to split it into four parts:
- In the FIRST part, you need to specify the “Test Expression” on which all the conditions get tests.
- After that, in the SECOND part, you need to specify the case to test (that means condition to test).
- Now in the THIRD part, you need to write another case where you need to test if the condition in the second part is not TRUE.
- In the end, in the FOURTH part, you need to specify an outcome that should come when both the above cases are not TRUE.
A simple example to understand select case statement
The syntax of the SELECT CASE is self-explanatory and quite easy to follow while writing code.
But let take an example and understand it completely. Below you have a simple code to test the value from the cell A1.
Sub SelectCaseExample1() Select Case Range("A1").Value Case "Yes" MsgBox "It's a Yes." Case "No" MsgBox "It's a No" Case Else MsgBox "Can't Say" End Select End Sub
Now let’s understand this code with each line:
- FIRST, you have the SELECT CASE keyword and the cell A1 to use as an expression for testing the condition.
- SECOND, you have the keyword CASE and the condition (“YES”) to check from the cell A1 and code to show a message box with the message “It’s a Yes” if the condition mentioned in case1 is true.
- THIRD, you again have the keyword case and the condition (“No”) to check from the cell A1 and code to show a message box with the message “It’s a No” if the condition mentioned in case two is true.
- FOURTH, you have the keyword CASE ELSE and a line of code to show a message box with a message “Can’t Say” if none of the cases are true.
- FIFTH, you have the END SELECT keyword to end the SELECT CASE statement.
Related: VBA MESSAGE BOX
- It can test multiple conditions and execute a line of code based on the result.
- You can use comparison operators (=, >, <, <>, >=, <=) while testing for a condition by using the Is keyword.
- You can use a comma to test more than one condition within a single case.
- You can also use a range of numbers while testing a condition in a single case.
- When one condition met, VBA stops to test the rest of the cases from the statement.
- You can also add a case else section at the end to execute a line of code when none of the conditions met.
- You can skip using the CASE END statement.
More Examples to use Select Case
It’s time to use the SELECT CASE in different ways, and below are some of the useful and essential examples that you can learn.
1. Select Case with Numbers
While writing a VBA code for the SELECT CASE statement, you can use operators to compare values. Now in the below code, you have >,<, and = operators to test the conditions.
Sub SelectCaseExample1() Select Case Range("A1").Value Case Is >= 45 MsgBox "Pass" Case Is < 45 MsgBox "Fail" End Select End Sub
- The first case in this statement checks if the value from the cell A1 is greater than or equal to 45 or not and returns a message box with the message “Pass”.
- And the second case in the statement checks if the value from the cell A1 is lower than 45 and returns a message box with a message “Fail”.
2. Select Case with a Range of Numbers
You can also use a range of numbers to test a condition. In the below code, you have a condition with a range of numbers in each case to test.
Sub SelectCaseExample2() Select Case Range(“A1”).Value Case 45 To 100 MsgBox “Pass” Case 0 To 44 MsgBox “Fail” Case Else MsgBox “Out of Range” End Select End Sub
- The first case in the statement checks the number from cell A1 using the range of numbers from 45 to 100 and returns the “Pass” if the value falls under this range.
- The second statement has a range 0 to 44 to test with the value from the cell A1 and returns “Fail” if the value falls under this range.
- The third statement that is case else show a message box with the message “Out of Range” if case one and two are false.
3. Using the Colon Operator
While writing code for the SELECT CASE, you can use colon operators to write the entire case in one line.
In the below code, the line starts with the case and then condition to test and then a colon, and then the statement to run in that case is true.
Sub SelectCaseExample3() Select Case Range(“A1”).Value Case 45 To 100: MsgBox “Pass” Case 0 To 44: MsgBox “Fail” Case Else: MsgBox “Out of Range” End Select End Sub
4. Use Select Case with Multiple Conditions
Just like defining a range for a case, you can also specify multiple values to test for the condition.
In the below code, each case has three numbers to test.
Sub SelectCaseExample() Select Case Range("A1").Value Case 1, 3, 5 MsgBox "Yes" Case 2, 4, 6 MsgBox "No" Case Else MsgBox "Out of range" End Select End Sub
- The first case of this code will test the value from the cell if it is 1, 3, or 5 and will return “Yes” in a message box.
- And the second case of this code will test the value from the cell A1 if it’s 2, 4, or 6.
5. Using Select Case in a Custom Function
You can also use the SELECT CASE while writing a code for a VBA Function.
Function udfGrade(student_marks As Integer) Dim myGrade As String Select Case student_marks Case Is < 40: myGrade = “Bad” Case 41 To 50: myGrade = “Average” Case 51 To 60: myGrade = “Good” Case 61 To 80: myGrade = “Very Good” Case 81 To 100: myGrade = “Excellent” End Select udfGrade = myGrade End Function
In the above function, it has five cases to check for the score of the students from a cell. All 5 cases use a range of numbers and return the value defined in the statement.
6. Using Select Case from Nested Conditions
You can also create a nested condition statement. What I’m trying to say is you can write a code to test multiple conditions with the select case.
Imagine if you want to write a code that can test conditions based on the values from two different cells.
Sub SelectCaseStatement() Select Case Range("A2") Case "Boy" Select Case Range("B2") Case "Commerce": MsgBox "Boy (Commerce)" Case "Science": MsgBox "Boy (Science)" End Select Case "Girl" Select Case Range("B2") Case "Commerce": MsgBox "Girl (Commerce)" Case "Science": MsgBox "Girl (Science)" End Select End Select End Sub
In the above code, you have three select case statements. There is one main statement that checks for the value from the cell A2 if it is “Boy” or “Girl”.
And then, based on the result, two more statements run and check for the value from the cell B2 if it is Commerce or Science.
SELECT CASE Vs. IF THEN ELSE Statement
- When you want to test multiple conditions, it’s easy to write a code using the SELECT CASE instead of IF-THEN.
- Even it is easy to edit and make changes in a SELECT CASE statement comparing to IF-THEN.
VBA is one of the Advanced Excel Skills