VBA Case Statement

What is Excel VBA Case Statement?

The VBA Case statement is used to derive results based on logical tests. The VBA Case Statement is used to compare the result of an expression with multiple Case statements. If a match is found, the condition holds true. The Case statement returns either TRUE or FALSE as a result of the logical test.

For example, we have two numbers in the worksheet. We must test if the value in cell A1 is greater than that in cell A2. We can apply the VBA Case statement to test the result.

We can use the following Case statement code to check if the value in cell A1 is greater than that in cell A2 value.

Sub Case_Intro()
Dim Value1 As Integer
Dim Value2 As Integer
Value1 = Range(“A1”).Value
Value2 = Range(“A2”).Value
Select Case Value1
Case Is > Value2
MsgBox “Yes Cell A1 value is greater than Cell A2”
Case Else
MsgBox “Yes Cell A1 value is less than Cell A2”
End Select
End Sub

The above case statement will return the following result in the message box.

Since the value in cell A1 (56) is less than in cell A2 (100), we get the FALSE output, i.e., Yes Cell A1 value is less than Cell A2.

What is Excel VBA Case Statement?

VBA’s Case Statement is used to apply conditions and arrive at results based on the result after any condition matches the given value/expression.
The Case Statement can be an alternative to the IF statement in VBA.
We can use the “To” word instead of comparison operators to test a range of numbers.
When applying multiple values to the test, we can enter them in a single line separated by a comma (,).
The Case Statement is case-sensitive.

Syntax of the VBA Case Statement

Following is the syntax of the Case statement in VBA.

Test Expression: This is the expression/value we will test against the different cases in the subsequent steps.

Value1, Value2, Value3…. This is the condition on which the Test Expression is evaluated. When it is true, the corresponding code is executed.

After all the case statements, we must use the “End Select” key word to end the case statement.

Special Note: When we construct multiple VBA Case statements to test various conditions, as soon as a condition is met, VBA will exit the Select Case statement. For example, assume we have six tests. While doing the logical test in excel, VBA will exit the Select Case statement after executing the code under the third case if the condition is satisfied in the third case. The remaining tests after the third condition will not be tested.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How to Use Excel VBA Case Statement?

Here’s a step-by-step approach to implementing the Select Case statement in VBA. First, follow the steps listed below.

For example, consider the following data in Excel.

The following is the scenario we need to test.

If the Exam1 value (cell A2) is greater than the Exam2 value (cell B2), the result in cell C2 should be “Grade A”; otherwise, it should be “Grade B.”

Step 1: Start the sub-procedure by naming the macro in the Visual Basic Editor window.

Step 2: Open the Select Case statement.

Step 3: Here, we should test the value in cell A2; Hence, provide the value in cell A2 using the RANGE object.

Step 4: After the value to be tested is given in the Select Case statement, we should access VBA Case statement and add the conditions. So, apply them as > Cell B2 value.

It will test if the Range A2 value is greater than that of Range B2.

Step 5: If it is greater, we must get “Grade A” in cell C2, so enter this code.

Step 6: If the logical test is false, i.e., the Range A2 value is not greater than the B2 value, we must get “Grade B” in cell C2. Since there is only one logical test to check, we can enter the Case Else statement as follows.

Step 7: Close the Case statement using the “End Select” statement-the final looks like this.

Sub Result_Case()

Select Case Range(“A2”).Value
Case Is > Range(“B2”).Value
Range(“C2”).Value = “Grade A”
Case Else
Range(“C2”).Value = “Grade B”
End Select

End Sub

Step 8: Press F5 to execute the code or click on the “Execute” button.

We get the following result in cell C2.

VBA Case Statement - Use - Step 8 - Output

The result in cell C2 is “Grade B” because the condition tested, that is, the Range A2 value is greater than the Range B2 value, is false; hence, the Case Else statement is executed, and the result is “Grade B.”

Examples

Now let us look at some real-world examples of applying the Case statement in VBA.

Example #1 – Find the Examination Result

The Case statement is often applied to find exam results. For example, look at the following data in Excel.

Peter is a student who scored 86 on an examination. Now we need to find the result based on the following conditions.

If the score is greater than 95, the result will be “A+.”
If the score is greater than 85, the result will be “A.”
If the score is greater than 60, the result will be “B.”
If the score is less than or equal to 60, the result will be “C.”

We need to test 3 different conditions using the Case statement.

1: Open the sub-procedure by naming the macro.

2: Open the Select Case statement.

3: We must enter the cell to be evaluated against multiple conditions. In this case, we must examine the score cell B2; hence, we provide the cell address B2 using the Range object.

4: The first condition is to check if the provided cell value is > 95. So, use the “Case Is” statement and apply the logical test as shown below.

5: Once the logical test is provided, we decide what should be done if it is TRUE. In this case, we need to get the value “A+” in the result cell C2.

6: If the applied logical test is not TRUE, we must use the next one, i.e., check if the value is >85.

7: If this is TRUE, we need the result A in cell C2.

8: Likewise, apply all the remaining tests. The complete code is below.

Sub Exam_Result()
Select Case Range(“B2”).Value
Case Is > 95
Range(“C2”).Value = “A+”
Case Is > 85
Range(“C2”).Value = “A”
Case Is > 60
Range(“C2”).Value = “B”
Case Else
Range(“C2”).Value = “C”
End Select
End Sub

9: Execute the code. We get the following result in cell C2.

The Case statement evaluates all the applied logical tests and returns the result A.

Example #2 – Using the “To” Keyword

Using Range of Numbers Instead of Comparison Operators

We can also use a range of numbers instead of comparison operators. For example, in the previous example, instead of applying > 95, we can use the Range of numbers “96 to 100,” and instead of >85, we can use “86 to 95.”

For better understanding consider the following code.

Example #3 – Case Statement with Multiple Values to be Tested

We can also use the VBA case statement with multiple conditions in the logical test. For example, look at the following data.

We have a monthly list in cells A2 to A13. Then, column B should mention which quarter it belongs to based on the following conditions.

Jan, Feb, Mar = Q1

Apr, May, Jun = Q2

Jul, Aug, Sep = Q3

Oct, Nov, Dec = Q4

1: We begin by writing the code for our requirements. The month should be either Jan, Feb, or Mar to specify the quarter as Q1. The following code will show us how to apply multiple conditions in the Case statement.

Note: Since we should apply the same logic for many cell, we use the For…Next loop to loop through all the cells and arrive at the quarter names.

Code Explanation:

Part #1: In this part, we have declared two variables. One is to find the last used row of the worksheet, and the other is to loop through all the cells.

Part #2: In this part, we find the last used cell in the worksheet.

Part #3: Here, we loop through all the cells. We apply the Case statement inside the loop to conduct logical tests for all the looping cells.

Let’s execute the code and see what we get in return.

2: Upon executing the code, we get the above result.

If you notice, we have a blank result for the month of “Mar.” To find the cause of this issue, let us examine the code once.

While applying the logical Case statement, we have given the month name in small letters, whereas in the data table, the month name is a mix of capital and small letters.

While applying the logical Case statement, we have given the month name in small letters, whereas in the data table, the month name is a mix of capital and small letters. Moreover, the first letter is a capital letter in the data table. Hence, the Case statement treats this differently than the word “mar,” thereby showing that it is case-sensitive by default.

3: To make the Case statement case-insensitive, we can add the Compare statement at the beginning of the module.

Add the statement Option Compare Text at the top of the module as shown below.

Now, the Case statement becomes case insensitive.

It will return a result without considering the case in the condition specified.

To make the Case statement again case- sensitive, add the statement Option Compare Binary at the top of the module.

Important Things to Note

VBA’s Case statement is case-sensitive by default. Hence, we must provide values keeping in mind the case when testing for conditions.
We can turn off the case-sensitive nature of the Case statement by adding the Option Compare Text at the top of the module.
The Case Statement should start with “Select Case” and end with “End Select”; otherwise, we will get a “Compile Error.”

In Case Statement, if there are five logical tests, it tests one case at a time sequentially; if the third logical test is TRUE, it will not test the 4^th and 5^th logical tests.

Frequently Asked Questions (FAQs)

1. Why is Excel VBA Case Statement Not Working?

The VBA Case Statement is case-sensitive. For example, look at the following code.

Sub FAQ()
Dim MyName As String
MyName = “PETER”
Select Case MyName
Case "Peter"
MsgBox "Correct Matching of Words"
Case Else
MsgBox "Not Correct Matching of Words"
End Select
End Sub

The variable has the word “PETER.” However, in the Case statement, we mentioned “Peter .”If the variable name and condition’s name match even in the case, it will return the first result; else, it will return the second result.

Here, the variable and the condition’s case are different; hence, the Case statement did not evaluate the first condition as true. Therefore, it returned the result in the Else statement.

2. What is the use of Excel VBA Case Statement?

VBA Case statement is helpful for logical tests and computation based on the logical test’s results. The Case statement can be used as an alternative to the IF statement in VBA.

3. Can we use Case Statement in VBA without Else?

Yes, we can use the Case statement without the Else. However, if we don’t use Else, we must include an extra Case statement.

Download Template

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

VBA-Case-Statement Download

VBA Case Statement