VBA SELECT CASE is a statement for testing multiple conditions. In this Statement, you can First Define a Condition and Then Specify a Code to Run if that Condition is True, Then Specify a Second Condition and a Code to run if That Condition is True. So, Multiple Conditions and Statements can be Entered.
The VBA Select Case Statement is also known as Switch Case in Many other Languages Such as:
Java
, C +
, C#
, and JavaScript
Note that, if a Match is not Found in any of the Case Statements, the Conditions will Surely be False. In This kind Case, the Code of the Case Else Statement is Executed.
The Syntax For Select Case
Select Case Statement Follows the Following Syntax.
Select Case expression Case expression 1 statement 1 statement 2 .... statement1 n Case expression 2 statement 1 statement 2 .... Case expression n statement 1 statement 2 .... Case Else else statement 1 else statement 2 .... End Select
- In the FIRST part, it’s Important you Specify the “Test Expression” on Which all the Conditions get Tested.
- After That, in the SECOND Part, you Need to Specify the Case to Test (That Means Condition to Test).
- Now in the THIRD part, you Need to Write Another case Where you Need to test if the Condition in the Second Part is not TRUE.
- In the end, in the FOURTH part, you Need to Specify an Outcome that Should Come When Both the Above Cases are not TRUE.
See More: VBA MsgBox
Below are Some Examples that Explain the Execution of the Select Case Statement.
Example 1. We Will Find a Condition Where i = True with the Help of the Select Case Statement.
Sub SelectCase_Example_1() Dim i As Integer i = InputBox("Enter any number to print in word") Select Case i Case 1 MsgBox "One" Case 2 MsgBox "Two" Case 3 MsgBox "Three" Case Else MsgBox "None" End Select End Sub
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 i.
Case 1: If the User Enter the Values of i and Press the OK Button.
You can see the Values of i is “Two”. After Clicking on the OK Button, it will Show the Following Output.
Case 2: If the Values of i is out of case number and Click on the OK Button.
Click on the OK Button Again and will Show the Following Result Below.
Example 2: We Want to Check the Entered Number by the User is Less Than Greater Than or Equal to 50.
Sub SelectCase_Example_2() Dim iNumber As Integer iNumber = InputBox("Please Enter Any Number Between 1 to 50") Select Case iNumber Case Is < 20 MsgBox "The number is less than 20" Case Is = 20 MsgBox "The number is Equal to 20" Case Is > 20 MsgBox "The number is greater than 20" End Select End Sub
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 50.
Click on the OK Button, and it Gives the Following Output.
Example 3: In This Example, we Check the Multiple Conditions Within a Single Case with the Help of the Select Case Statement.
Sub SelectCase_Example_3() Dim iNumber As Integer iNumber = InputBox("Please Enter Any Number Between 1 to 10") Select Case iNumber Case 1, 3, 5, 7, 9 MsgBox "The number is Odd" Case 2, 4, 6, 8, 10 MsgBox "The number is Even" Case Else MsgBox "The number is out of range" End Select End Sub
This Code Will Check the Weather a Number is Even or odd When a User Enters any Number From 1 to 10.
If you Take a Look you will see That 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.
Now Click on the OK Button it Gives the Following Output.