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.
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”
MsgBox “Yes Cell A1 value is less than Cell A2”
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.
Table of contents
- 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.
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.
Select Case Range(“A2”).Value
Case Is > Range(“B2”).Value
Range(“C2”).Value = “Grade A”
Range(“C2”).Value = “Grade B”
Step 8: Press F5 to execute the code or click on the “Execute” button.
We get the following result in cell C2.
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.”
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.
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”
Range(“C2”).Value = “C”
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.
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 4th and 5th logical tests.
Frequently Asked Questions (FAQs)
The VBA Case Statement is case-sensitive. For example, look at the following code.
Dim MyName As String
MyName = “PETER”
Select Case MyName
MsgBox "Correct Matching of Words"
MsgBox "Not Correct Matching of Words"
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.
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.
Yes, we can use the Case statement without the Else. However, if we don’t use Else, we must include an extra Case statement.
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.
Guide to VBA Case Statement. Here we explain how to use case statement in excel VBA with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply