VBA Select Case

What Is The Select Case Statement Of VBA?

VBA SELECT CASE statement allows us to apply multiple conditions to arrive at a single result. Now, we need to first apply the condition and then apply the action to be completed if the applied condition is TRUE.

In Excel, we will do a lot of logical tests to do calculations and logical function IF is the most used in Excel formulas. But with VBA we can use the IF statement as well as the SELECT CASE statement as an alternative to the logical IF condition.

Syntax Of Select Case Statement Of VBA

The syntax of the VBA Select Case Statement is a bit lengthy, but we will explain it in a simple manner to make it easy for you to understand.

1. Now, the first part of the syntax is to specify the value to be tested in subsequent case statements.
2. Next, apply the first logical test to be evaluated. If this case statement is TRUE, then we can write the action to be performed in the next line.
3. The third part is to apply the second logical test to be evaluated if the first case statement is FALSE. If this case statement is TRUE, then we can write the action to be performed in the next line.
4. Finally, the last part is the CASE ELSE statement i.e., if all the applied case statements are FALSE then we can write the code to perform the alternative action.

At last, we need to write the code “End Select” Statement, and this is mandatory.

How To Use The Select Case Statement Of Excel VBA?

Now, we will show a simple example of using the Select Case statement in VBA. For example, we have a certain value in cell A1.

Next, we will do a select case statement to check what value we have in cell A1 and show the following results based on the value in cell A1.

• Case 1: If the value in cell A1 is YES, then we will show the message box value as “The value in cell A1 is YES”.
• Case 2: If the value in cell A1 is No, then we will show the message box value as “The value in cell A1 is No”.
• Case Else: If we have any other value, then we will show the message box value as “The value in cell A1 is Neither YES or No”.

Step 1: To start with, the subroutine procedure by giving a name to the macro.

Step 2: Next, define a variable to get the value of cell A1.

Step 3: Then, assign the value of cell A1 by using the Range object for the defined variable.

Step 4: Now, open the select case statement.

Step 5: After the select case statement, enter the variable name value as YES.

Step 6: Similarly, after the first case test, we need to write what needs to be done if the applied case test is TRUE. We will show the message “The value in cell A1 is YES” through the message box.

Step 7: Next, apply the second case statement to check if the variable’s value is NO. And show the message as “The value in cell A1 is NO”

Step 8: If we do not have either YES or NO, then we will apply the CASE ELSE statement to show “The value in cell A1 is neither YES nor NO”.

Step 9: Next, close the Select Case statement by putting the code “End Select”.

Sub Case_Example1()
Dim Cell_Value As String
Cell_Value = Range(“A1”).Value
Select Case Cell_Value
Case “Yes”
MsgBox “The value in cell A1 is YES”
Case “No”
MsgBox “The value in cell A1 is NO”
Case Else
MsgBox “The value in cell A1 is neither YES or NO”
End Select
End Sub

Let’s run this code and see what we get in the message box.

Because we have a YES value in cell A1, we have got the message saying, “The value in cell A1 is YES”.

Now, change the cell A1 value to NO and see what we get.

Now, the message is “The value in cell A1 is NO”.

Then, change the value in cell A1 to other than YES or NO.

Because we have a value other than YES or NO in cell A1, we got the message box saying, “The value in cell A1 is neither YES nor NO”.

Examples

Example #1 – Check User Entry In Input Box

Assume, we are collecting employee survey scores out of 10, and we need to showcase the level of satisfaction based on the scores given by the user in the input box.

• If the score is >8, then the satisfaction is “Very Good”.
• If the score is >6, then the satisfaction is “Good”.
• If the score is >4, then the satisfaction is “Average”.
• If the score is <=3, then the satisfaction is “Low”.

Step 1: First, start the subroutine procedure by naming the macro as “Employee_Score”.

Step 2: Next, define a variable to store the value given in the input box.

Step 3: Then, design an input box for this variable.

Step 4: Now, open the select case statement to check the value of the variable “Score”.

Step 5: Next, apply the first case test to check the value is >=8.

Step 6: If the case is TRUE, then we will show the satisfaction message “Very Good”.

Step 7: Similarly, apply the remaining case tests and the completed code looks like the following.

Code:

Sub Employee_Score()
Dim Score As Integer
Score = InputBox(“What is your score satisfaction score?”)
Select Case Score
Case Is >= 8
MsgBox “Very Good”
Case Is >= 6
MsgBox “Good
Case Is >= 4
MsgBox “Average”
Case Else
MsgBox “Low”
End Select
End Sub

Next, run this code.

We will get the following input box to enter the satisfaction rating.

Now, we have entered a score 7.

Next, click on OK, and we will get the following status.

Clearly, the result is Good because the score is between 6 and 8.

Example #2 – Range Of Numbers In Case Statement

In example #1, we have entered the excel logical operators > (greater than) to check the numbers range. However, we can also use the range of numbers to test the case.

Instead of applying the logical test >8, we can also mention the range of numbers as 8 to 10.

Code:

Sub Employee_Score()
Dim Score As Integer
Score = InputBox(“What is your score satisfaction score?”)
Select Case Score
Case 8 to 10
MsgBox “Very Good”
Case Is 6 to 7
MsgBox “Good”
Case Is 4 to 5
MsgBox “Average”
Case Else
MsgBox “Low”
End Select
End Sub

Instead of applying the logical operators, we have used the range of numbers by defining the lowest and highest range of numbers.

Example #3 – Use Colon Operator

In the previous 2 examples, we have written the result code after the case statement. However, we can write the result code in the same line as the case statement by entering the colon (:).

First, we will apply the case with the condition and then a colon, and then the statement or result code to be executed if the condition is TRUE.

The following code demonstrates the same.

Code:

Sub Employee_Score1()
Dim Score As Integer
Score = InputBox(“What is your score satisfaction score?”)
Select Case Score
Case Is >= 8: MsgBox “Very Good”
Case Is >= 6: MsgBox “Good”
Case Is >= 4: MsgBox “Average”
Case Else: MsgBox “Low”
End Select
End Sub

As we can see, we have the same result code as the case statement by just entering the colon after the condition is applied.

Example #4 – Select Case With Multiple Condition Values

Meanwhile, we can also write the select case statement with multiple condition values. For example, look at the following line of code.

Select Case Score
Case Is >= 8: MsgBox “Very Good”
Case Is >= 6: MsgBox “Good”
Case Is >= 4: MsgBox “Average”
Case Else: MsgBox “Low”
End Select

The first case statement logic is >=8, i.e., anything greater than or equal to 8 will be tested out. The second case is >=6 i.e., numbers between 6 to 7 will be considered in this logical test in excel.

However, instead of writing the logic like this, we can write the logic in the following way as well.

Sub Employee_Score1 ()
Dim Score As Integer
Score = InputBox(“What is your score satisfaction score?”)
Select Case Score
Case 8,9,10: MsgBox “Very Good”
Case 6,7: MsgBox “Good”
Case 4,5: MsgBox “Average”
Case Else: MsgBox “Low”
End Select
End Sub

The first case will test numbers 8,9,10 and the second case will test numbers 6 and 7 and so on.

Example #5 – Find Employee Grade

We have the following employee appraisal scores in Excel.

We have employee name and their rating. Now, based on the rating, we need to arrive at the grade, and the following are the grade conditions.

• If the rating is >9, then the Grade is A+.
• If the rating is >=7, then the Grade is A.
• If the rating is >=5, then the Grade is B.
• If the rating is anything less than 5, then the Grade is C.

The following code is for your reference.

Code Explanation

Part 1 – We have defined 3 variables, Rating, k, and LR. For the “Rating” variable we will assign the value of the rating cells, for the “k” variable we will use it as a loop variable, and for “LR” we will find the last used to decide on the end of the loop.

Part 2 – Here, we are finding the last used row to decide on the exit of the loop. For example, in our data, we have 7 used rows, so the loop should end after running 7 times.

Part 3 – In this part, we are starting our loop from the 2^nd position because our 1^st row has headers, so excluded this from the loop. Starting from the 2^nd row, the loop should run until the last used row number is returned by the variable LR.

When the loop begins, we are fetching the value from the rating column for the respective cell of the loop number. For example, when the loop begins for the first time, we will get the rating value from cell B2 and when the loop comes back for the 2^nd time the rating cell will be B3, and so on.

Part 4 – Based on the rating value we are applying the case statement and deciding on the Grade in the Grade column.

Now, when we run this code, we will get the following Grade result in column C.

Likewise, we can use VBA Select Case to obtain results.

Important Things To Note

• When you have multiple conditions to test, the Select Case statement is much better than the IF THEN ELSE statement.
• When we use the logical operators =, >, >=, <, <= we need to use the keyword “IS”.
• Select Case statement executed line by line, so if the first condition is TRUE, then it will not execute other remaining case statements.
• Use a comma to test more than one conditional value.
• End Select is mandatory to close out the Select Case statement.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Select Case, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Select Case. Here we explain how to use Select Case statement in Excel VBA with examples and downloadable template. You may learn more from the following articles –

• VBA Case Statement
• VBA Selection Range
• VBA Object Required