VBA Select Case

VBA Select Case statement is used instead of multiple Nested If statements. The VBA Select Case makes the VBA program easy to understand and faster in execution time.

The VBA Select Case statement is also called the Switch Case in many other languages such as Java, C++, C#, and JavaScript. It checks a variable for different values. If any case is true, then it will execute only that case and avoided the rest of other cases.

The Select Case statement is an alternative of the If Else If statement. It is another way to select a value from a list of values.

Syntax

VBA Select Case statement follows the following syntax:

Explanation

  • test_expression: It is a string or numeric value that comparing to the list of the conditions.
  • condition_1 to condition_n: These are the conditions that evaluated in the order listed. If a condition is true, it will execute the corresponding code and not execute the further conditions of the code.
  • Result_1 to result_n: The code that is executed when a condition is true.
  • Case Else: If no condition is met to be accurate, then the else statement will be executed in the code.

Examples

Here are some examples that help to understand the execution of the Select Case statement.

Example 1: We will find a condition where x = y with the help of the Select Case statement.

VBA Select Case

If the condition is true, then the "Case True" block of the code will be executed. Otherwise, the "Case False" block of the code will be executed.

In the above example, we used the InputBox function to get values from the user.

Now execute the code and enter the values of X and Y.

Case 1: If the user enters both the different values of X and Y.

VBA Select Case

Now press the OK button and enter the value for Y.

VBA Select Case

You can see both the values of X and Y are different. After clicking on the OK button, it gives the following output.

VBA Select Case

Case 2: If the values of X and Y are equal.

Enter a value for X.

VBA Select Case

Now click on the OK button, and enter the same value for Y.

VBA Select Case

Again click on the OK button, and it gives the following output.

VBA Select Case

Example 2: We want to check the entered number by the user is less than greater than or equal to 50.

VBA Select Case

Here we used the IS keyword with a Case statement to compare values.

Now execute the code and give any value in between 1 to 100.

VBA Select Case

Click on the OK button, and it gives the following output.

VBA Select Case

Example 3: In this example, we check the multiple conditions within a single case with the help of the Select Case statement.

VBA Select Case

This code will check the weather a number is even or odd when a user enters any number from 1 to 10.

We used (" ") to compare multiple conditions within a single Case statement.

Execute the code by using the Run button and enter any number start 1 to 10.

VBA Select Case

Now click on the OK button it gives the following output.

VBA Select Case