Home / VBA / VBA Select Case Statement in Excel

VBA Select Case Statement in Excel

Last Updated: March 30, 2024
puneet-gogia-excel-champs

- Written by Puneet

Contents hide
Key Points
What is Select Case in VBA
Syntax for Select Case VBA
Important Points
More Examples to use Select Case
SELECT CASE Vs. IF THEN ELSE Statement
Related Tutorials

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.

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.

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)
 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:

select-case-syntax
  • 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.

Simple Example to understand a Select Case Statement in VBA

The syntax of the 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:

select-case-statement-example
  • 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 the case1 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

Important Points

  • 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 is 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 are 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.

100 Excel Macro Examples

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.

select-case-with-numbers
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 or not 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”. 

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 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 show a message box with the message “Out of Range” if cases 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 the condition to test and then a colon, and then the statement to run in that case is true.

using-colon-operator
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.

select-case-with-multiple-values
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.

5. Using Select Case in a Custom Function

You can also use the SELECT CASE while writing code for a VBA Function.

select-case-in-custom-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
select-case-from-nested-conditions

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 cell B2 if it is Commerce or Science.

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.

Choice between Select Case and If statement 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.

What is VBA

Related Tutorials