Excel VBA Select Case Statement
Select Case is a substitute of writing down multiple if statements in VBA, when we have many conditions in a code we might have to use multiple If statements and which can be tedious as it becomes more complex as more of the If statements are provided, in select case statement we define the criteria as different cases and results as per them.
SELECT CASE helps in the decision-making process. In this, only one expression is used to assess the different possible cases. Multiple conditions are analyzed easily to execute the code developed. This case statement is used as a substitute statement to ELSE IF to assess expressions. It is classified as a logical function that inbuilt into Excel. To use this function, the code is entered through visual basic editor presented under the developer tab.
Various groups of statements are used in the Select Case. The following syntax should be followed to use this effectively. It is similar to the switch statement presented in other programming languages such as Java, C#, and PHP.
Select [Case] Expression for testing [Case] List of Expression Statements (Case 1, Case 2, Case 3 and so on...) Case Else (Else Statements) End Select
The explanation for the terms associated with the select case is provided as follows
An expression for testing: It is required to assess the different types of data such as integer, string, boolean, object, and character.
List of Expressions: Expressions are created with the case to find the exact match of the input entered. If more than two expressions, these are separated using the comma operator. ‘Is’ is a keyword utilized in comparing the two expressions using the logical operators in excel such as =, <, >, <=, and >=.
- End Select: It closes the constructor select case definition
- Statements: Statements are developed using the case to run the expressions that are evaluated to analyze if there is any matching statement
- Else Statements: It is to test the else statement when the testing expression does not match with any case statements.
How to use the VBA Select Case Statement?
The select case feature provided by the VBA does not run in the normal worksheets. We need to use the Visual Basic option under the Developer tab. User-defined functions and coding created in the developer mode to run the various small applications in the business field.
It is used in situations when there are nested if statements in excel. It is the best option to deal with a variety of case statements. To use this option effectively, the following tasks should be performed
- First of all, needs to create a macro by placing command button control in the excel sheet.
- After that right-click on the command button and select view code option
- Place the code between the command button function and end sub
- Debug the code to identify any syntax errors.
- Compile the code to find compilation errors to successfully execute the program
- Change the input values to observe different results based on the matching criteria
Example #1 – Simple Select Case Statement
This example is to understand the simple select case to find the matched value.
Private Sub Selcaseexmample () Dim A As Integer A = 20 Select Case A Case 10 MsgBox "First Case is matched!" Case 20 MsgBox "The Second Case is matched!" Case 30 MsgBox "Third Case is matched in Select Case!" Case 40 MsgBox "Fourth Case is matched in Select Case!" Case Else MsgBox "None of the Case is matched!" End Select End Sub
Four case statement and case else statement is used to compare the test expression with different cases. In the present example, the second case is matched as variable A matches with 20.
Example #2 – ‘To’ Keyword to Test the Grades
This example explains the use of the ‘To’ keyword with the select case.
Private Sub Selcasetoexample () Dim studentmarks As Integer studentmarks = InputBox("Enter marks between 1 to 100?") Select Case studentmarks Case 1 To 36 MsgBox "Fail!" Case 37 To 55 MsgBox "C Grade" Case 56 To 80 MsgBox "B Grade" Case 81 To 100 MsgBox "A Grade" Case Else MsgBox "Out of range" End Select End Sub
‘To’ keyword is helpful in defining the set of test expressions in a range. It is helpful in finding the grade obtained by a student by comparing the different cases. After running the program, we have to enter the value to get the result.
The output is displayed with a message box as shown in the above screenshot.
Example #3 – Using ‘Is’ Keyword with Select Case
This example demonstrates the use of ‘Is’ keyword with the select case.
Sub CheckNumber() Dim NumInput As Integer NumInput = InputBox("Please enter a number") Select Case NumInput Case Is < 200 MsgBox "You entered a number greater than 200" Case Is >= 200 MsgBox "You entered a number greater than or equal to 200" End Select End Sub
‘Is’ keyword helps to find the matched values with only case statements. It compares test expression with given input to produce the output.
The output is obtained as shown in the figure if the entered value is more than 200
Example #4 – With Command Button to Change the Values
The select case also used with the Command button by creating a macro.
Sub color() Dim color As String color = Range("A1").Value Select Case color Case "Red", "Green", "Yellow" Range("B1").Value = 1 Case "White", "Black", "Brown" Range("B1").Value = 2 Case "Blue", "Sky Blue" Range("B1").Value = 3 Case Else Range("B1").Value = 4 End Select End Sub
As shown in the program, the input is taken from the cells of the worksheet. The program is executed after clicking on the command button used from ActiveX controls. In this, a comma separator is used to combine the test expressions.
When the value in the Cell A1 changed, it results in a change in the B1 cell also by running the excel macro through the command button. You can refer to the screenshot given below:-
Example #5 – Check a Number Odd or Even
This example to help to check a number is even or odd.
Sub CheckOddEven() CheckValue = InputBox("Enter the Number") Select Case (CheckValue Mod 2) = 0 Case True MsgBox "The number is even" Case False MsgBox "The number is odd" End Select End Sub
The coding is developed as shown in the screenshot and output is displayed as follows when even number is entered.
Example #6 – Nested Select Case Statements
Nesting is a useful feature of the select case and the way how it is done is shown.
Sub TestWeekday() Select Case Weekday(Now) Case 1, 7 Select Case Weekday(Now) Case 1 MsgBox "Today is Sunday" Case Else MsgBox "Today is Saturday" End Select Case Else MsgBox "Today is a Weekday" End Select End Sub
Here the select case is defined inside another select case to test a day, weekday, or weekend. Except case 1 and 7 remaining all are weekdays (Note: case 1 is Sunday and case 7 is Saturday)
Things to Remember
- The ‘Is’ keyword using in the statement of [case] and [case else] are not equal.
- ActiveX control should be used in executing program through a command button taking input and displaying output in the excel sheet range of cells.
- It is hard to run the programs in VBA if macros are disabled and always enable Macros to get best results.
- VBA is a case sensitive and input should be entered accurately to get better results.
This has been a guide to VBA Select Case Statement. Here we discuss how to use VBA select case statements with various keywords like Is, To along with examples, and downloadable Excel template. Below you can find some useful excel VBA articles –