SELECT CASE Statement in Excel VBA

Key Points

• With SELECT CASE, you can test multiple conditions, especially when you are working on 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.
• 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.

01. What is Select Case in VBA

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.

02. Syntax for Select Case VBA

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)
	Statement2 [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

The 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 tested.
• After that, in the SECOND part, you need to specify the case to test (that means the condition to test).
• Now in the THIRD part, you need to write another case 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 for when neither of the above cases is TRUE.

03. Example to Understand a Select Case Statement in VBA

The syntax of SELECT CASE is self-explanatory and quite easy to follow while writing code. But let’s take an example and understand it completely. Below you have a simple code to test the value from 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 cell A1 and code to show a message box with the message “It’s a Yes” if the condition mentioned in case one is true.
• THIRD, you again have the keyword case and the condition (“No”) to check from 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

Note that text comparison in Select Case is case-sensitive by default, so “yes” or “YES” in cell A1 goes to Case Else. To ignore the case, test LCase(Range(“A1”).Value) and write the cases in lowercase, like Case “yes”. Also, VBA runs only the first case that matches and then jumps to End Select, even if a later case matches too.

04. 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 the >= and < operators to test the conditions. To use an operator in a case, you need to start the case with the Is keyword.

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 cell A1 is greater than or equal to 45 and returns a message box with the message “Pass”.
• And the second case in the statement checks if the value from cell A1 is lower than 45 and returns a message box with the message “Fail”. An empty cell counts as 0 here, so it also shows “Fail”.

05. 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 of 0 to 44 to test with the value from cell A1 and returns “Fail” if the value falls under this range.
• The third statement is Case Else, which shows a message box with the message “Out of Range” if cases one and two are false.
• Watch out for decimals: a value like 44.5 is not in 0 To 44 or in 45 To 100, so it goes to Case Else. If your values can have decimals, use Case Is < 45 for the second case instead.

06. Using the Colon Operator

While writing code for the SELECT CASE, you can use the colon to write the entire case in one line. In the below code, the line starts with the case, then the condition to test, then a colon, and then the statement to run if 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

07. 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 cell A1 if it’s 2, 4, or 6 and will return “No”.

08. Using Select Case in a Custom Function

You can also use the SELECT CASE while writing 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 40 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

The above function has five cases to check the score of a student from a cell. The first case uses Is < 40, and the other four use a range of numbers, and each case returns the grade defined in it. Use it in a cell like =udfGrade(B2). Make sure the cases cover every number: a score of 40 must fall in one of them, otherwise the function returns a blank.

09. 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 that tests multiple conditions with one select case inside another. 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 if the value in cell A2 is “Boy” or “Girl”.

And then, based on the result, one of the two inner statements runs and checks if the value in cell B2 is Commerce or Science.

10. SELECT CASE Vs. IF THEN ELSE Statement

In VBA, both the Select Case statement and If statement are used for decision making in the code.

• The If statement is used when simple conditions need to be tested. It checks whether a condition is true or false and then executes a block of code accordingly.
• The Select Case statement, on the other hand, is more suitable for complex conditions, especially when there are multiple conditions to be tested.

It allows the program to test for various possibilities and then execute a block of code corresponding to the first true condition. The choice between the Select Case and If statements depends on the complexity of the conditions to be tested in the code. The If statement is usually sufficient for simple conditions, while the Select Case statement is often more appropriate for more complex or multiple conditions.

11. Select Case with a Range of Text

The To keyword works with text as well. In the following code, names that start with A to M go to the first group, and the rest go to the second group. The comparison is alphabetical, so “A” To “M” would include “M” but not “MARY”, which comes after “M”. That’s why the code uses “MZZZ” as the upper limit, and UCase makes the test ignore the case.

Sub SelectCaseText()
    Select Case UCase(Range("A1").Value)
        Case "A" To "MZZZ"
            MsgBox "Group 1"
        Case Else
            MsgBox "Group 2"
    End Select
End Sub

12. Select Case True to Test Different Conditions

Normally, all the cases test the same expression. But when you want each case to test a different condition (like two different cells, or the Like operator), use Select Case True. VBA then runs the first case whose condition is True:

Sub SelectCaseTrue()
    Select Case True
        Case Range("A1").Value >= 45 And Range("B1").Value = "Yes"
            MsgBox "Pass with project"
        Case Range("A1").Value >= 45
            MsgBox "Pass"
        Case Range("C1").Value Like "EX*"
            MsgBox "Exempted"
        Case Else
            MsgBox "Fail"
    End Select
End Sub

Frequently Asked Questions