Another similar construct that allows you to check for multiple conditions is the SELECT CASE statement. Select Case is useful when you have three or more conditions that you want to check. You can also use this with two conditions (but I feel If Then Else is easier to use in those cases). A simple example where the Select Case statement is useful when you want to get the grade of a student based on the marks he/she has scored (covered as an example later in this tutorial). Note: All the example codes covered in this tutorial are meant to be placed in a module in VBA.
Select Case Syntax
Below is the syntax of Select Case in Excel VBA:
Test_Expression: This is the expression whose value we analyze by using different cases (explained better with the examples below). Condition_1, Condition_2,…: These are the conditions on which the text expression is tested. If it meets the condition, then the code block for the given condition is executed.
For every Select Case statement that you use, you need to use the End Select statement.
Select Case Examples
Now to better understand how to use Select Case statement in VBA, let’s go through a few examples. Note that most of the examples in this tutorial are meant to explain the concept. These may or may not be the best way to get the work done. Let’s start with a simple example of see how Select Case allows us to check for conditions.
Example 1 – Check the Numbers
In the below example, the code asks the user to enter any number between 1 and 5, and then shows a message box with the number the user entered. Note that this code is far from useful and is not even foolproof. For example, if you enter 6 or any string, it would do nothing. But as I mentioned, my intent here is to showcase how Select Case works.
Example 2 – Using Select Case with IS Condition
You can use an IS condition with the Select Case construct to check for the value of numbers. The below code checks whether the input number is greater than 100 or not.
Example 3 – Using Case Else to Catch All
In the above example, I used two conditions (less than 100 or greater than or equal to 100). Instead of the second case with a condition, you can also use Case Else. Case Else acts as a catch-all and anything which doesn’t fall into any of the previous cases is treated by the Case Else. Below is an example code where I have used Case Else:
Example 4 – Using a Range of Numbers
In Select Case, you can also check for a range of numbers. The below code asks for an input and shows a message box based on the value.
Example 5 – Get the Grade based on the Marks Scored
So far we have seen basic examples (which are not really useful in the practical world). Here is an example which is closer to a real-world example where you can use Select Case in Excel VBA. The following code will give you the grade a student gets based on the marks in an exam. The above code asks the user for the marks and based on it, shows a message box with the final grade. In the above code, I have specified all the conditions – for marks 0 – 100. Another way to use Select Case is to use a Case Else at the end. This is useful when you have accounted for all the conditions and then specify what to do when none of the conditions is met. The below code is a variation of the Grade code with a minor change. In the end, it has a Case else statement, which will be executed when none of the above conditions are true.
Example 6 – Creating a Custom Function (UDF) using Select Case
In the above example, the code asked the user for the marks input. You can also create a custom function (User Defined Function) that can be used just like any regular worksheet function, and which will return the grade of the students. Below is the code that will create the custom formula: Once you have this code in the module, you can use the function GetGrade in the worksheet as shown below.
Example 7 – Check ODD / EVEN with Select Case
Below is an example code where I check whether the number in cell A1 is odd or even.
Example 8 – Checking for Weekday/Weekend (Multiple Conditions)
You can also use Select Case to check for multiple values in the same case. For example, the below code uses the current date to show whether today is a weekday or weekend (where weekend days are Saturday and Sunday) In the above code, we check for two conditions (1 and 7) in the same case. Note: Weekday function returns 1 for Sunday and 7 for Saturday.
Example 9 – Nested Select Case Statements
You can also nest one Select Case statement within other. Below is a code that checks whether a day is a weekday or a weekend, and if it’s a weekend, then it will display whether it’s a Saturday or a Sunday. In the above code, I have nested the Select Case to check whether the weekend is a Saturday or a Sunday. Note: The example shown above is to explain the concept. This is not the best or the most practical way to find out weekday/weekend.
Example 10 – Checking Text String with Select Case
You can check specific strings using Select Case and then execute code based on it. In the example code below, it asks the user to enter their department name and shows the name of the person they should connect with for onboarding. Hope all the examples above were helpful in understanding the concept and application of Select Case in Excel VBA.
Excel VBA Loops – For Next, Do While, Do Until, For Each. For Next Loop in Excel VBA. How to Record a Macro in Excel.
