VBA Select Case

The Select Case VBA statement compares an expression with multiple Case statements containing conditions. If a match is found in any of the Case statements, the condition is said to be true. Further, if a condition is true, its corresponding code is executed and no more Case statements are tested. However, if a match is not found in any of the Case statements, the conditions are said to be false. In this case, the code of the Case Else statement is executed.

For example, an employee is asked to rate the work-life balance of his organization on a scale of 10. Further, his input will be compared with the following three conditions (or possible outcomes):

• If the input is between 1 and 3 (both inclusive), display the message “the work-life balance is poor.”
• If the input is between 4 and 6 (both inclusive), display the message “the work-life balance is reasonable.”
• If the input is between 7 and 10 (both inclusive), display the message “the work-life balance is healthy.”

The message of only that condition will display, which matches the input entered by the employee. Note that the expression can either be entered directly in a Select Case structure or collected from the user as an input.

The purpose of using the Select Case statement in VBA is to execute different codes based on the fulfillment or non-fulfillment of different conditions. The Select Case statement is used to evaluate (or test) three or more conditions. It is a substitute for the If Then Else statement of VBA and the nested IF statement of Excel.

The Select Case statement in VBA is similar to the Switch Case statement used in programming languages like Java, C#, PHP, etc. The syntax of the Select Case statement of Excel VBA is given as follows:

Select   Expression for testing
  
 List of Expression Statements (Case 1, Case 2, Case 3 and so on...)

Case Else (Else Statements)

End Select
End Sub

The Select Case statement of Excel VBA accepts the following arguments:

• Expression for testing: This is a single expression that is to be compared with the different Cases. It can either be a numeric or textual expression. So, it can evaluate to a character, integer, Boolean, object, string, etc.
• List of expressions: This is the list of expressions (called Case statements) against which the “expression for testing” is compared. Excel VBA looks for an exact match within these Case statements. These Case statements consist of one or more possible outcomes (values or conditions) that may or may not match the “expression for testing.” If there are multiple expressions (or values) within a single Case, they must be separated by commas. The following keywords can be used in Case statements:
  • The To keyword should be used when a range of values needs to be specified in a Case statement. The value preceding the To keyword should be less than or equal to the value succeeding this keyword.
  • The Is keyword should be used when a logical operator (=, <>, <, >, <= or >=) needs to be entered in a Case statement. This keyword should be inserted before the logical operator.
• Statements: This is one or more codes succeeding the “list of expressions.” Only that code is executed for which the “expression for testing” matches the “list of expressions.”
• Case Else statement: This consists of one or more codes (or statements), which are executed when all the Case statements (or list of expressions) are false.
• End Select: It closes the Select Case structure. Every Select Case statement must necessarily have an End Select statement.

The “expression for testing,” “list of expressions,” and End Select clauses are required in a Select Case construction. However, the “statements” and Case Else statements are optional in the given syntax.

Note 1: In a Select Case statement in VBA, each Case statement is compared with the “expression for testing.” Once a condition is met (or the Case statement is matched), no further Case statements are tested. However, if a condition is false (or the Case statement does not match), the next Case statement is tested.

The testing of Case statements (or conditions) continues till one of the specified conditions is met or till the Case Else or the End Select statement. If none of the tested conditions is true and there is no Case Else statement, control passes to the End Select statement.

Note 2: If the “expression for testing” matches more than one Case statement, only the code of the first such match is executed.

The Select Case statement is a feature of Excel VBA. One can access VBA from the “visual basic” option of the Developer tab of Excel. In this tab, the user-defined functions (UDF) and their codes can be created.

The Select Case statement of VBA should be used in place of the nested IF statement of Excel. This is because the former is easier to understand and execute than the latter.

The steps to use the Select Case statement of VBA are listed as follows:

1. Create a command button and place it in the Excel worksheet. When the command button is clicked, a macro runs. The running of the macro performs an action.
2. Right-click the command button and choose the option “view code.” The Visual Basic Editor opens.
3. Enter the code between the CommandButton function and End Sub.
4. Debug and compile the code to identify the syntax errors.
5. Change the input values to observe the different outputs.

Note 1: The user-defined functions (UDF) are customized functions that cater to the specific needs of the user.

Note 2: Every time the input is changed (in point e), the “expression for testing” changes. As a result, the conditions that match this expression change. Hence, different codes are executed each time the input values change.

Example #1–“Expression for Testing” is Entered Directly

In the following code, the “expression for testing” is A=20 and the Case statements are 10, 20, 30, and 40. There is also a Case Else statement in this code.

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 Cases match!"

End Select
End Sub

On running the preceding code, the following output is obtained.

Explanation: The expression A=20 is compared with the four Case statements 10, 20, 30, and 40. A match is found in the second Case statement (Case 20). Consequently, the message of the second Case statement is displayed, which is “the second Case is matched.”

Had the expression not matched with any of the Case statements, the message of the Case Else statement would have been displayed.

Note: The MsgBox function of VBA displays a dialog box containing a customized message.

Example #2–“Expression for Testing” is Compared with a Range of Values Using the “To” Keyword

In the following code, the “expression for testing” is collected from the user with the help of the InputBox function of VBA. There are four Case statements, namely Case 1 To 36, Case 37 To 55, Case 56 To 80, and Case 81 To 100. There is also a Case Else statement in the code.

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

On running the preceding code, the user is asked for input. Assume that the user enters 90. So, the following output is obtained.

Explanation: The Case statements of the preceding code contain a range of values. Notice that in the code, each number preceding the To keyword is smaller than the number succeeding this keyword.

The “expression for testing” is entered as 90 by the user. This expression matches the fourth Case statement (Case 81 to 100). So, the code in this Case statement is executed. Hence, the message “A grade” is returned by VBA. This output is displayed in a dialog box due to the usage of the MsgBox function in the code.

Both numbers (preceding and succeeding the To keyword) of the range are inclusive. So, had the user entered 81, it would have again matched with the fourth Case statement. However, had a number greater than 100 been entered, the output would have been “out of range.” This message is defined in the Case Else statement of the code.

Note: The InputBox function collects the input from the user through a dialog box. The message shown in this dialog box is the same as that entered in the InputBox function of the code.

Example #3–“Expression for Testing” is Compared with Numbers Using the “Is” Keyword

In the following code, the “expression for testing” is collected from the user. There are two Case statements, namely Case Is < 200 and Case Is >= 200. There is no Case Else statement in the code.

Sub CheckNumber()
  Dim NumInput As Integer
  NumInput = InputBox("Please enter a number.")
  
Select Case NumInput
  
Case Is < 200
 MsgBox "You entered a number less than 200"
    
Case Is >= 200
 MsgBox "You entered a number greater than or equal to 200"

End Select
End Sub

On running the preceding code, a dialog box containing the message “please enter a number” is displayed. Assume that the user enters 200. So, the following output is displayed.

Explanation: Observe that the two logical operators (< and >=) in the code are preceded by the “Is” keyword. Had this keyword not been supplied, VBA would have automatically inserted it before the logical operators.

The “expression for testing” is entered by the user as 200. This expression is compared with the two Case statements of the preceding code. A match is found in the second Case statement. This is because 200 is not greater than but is equal to 200.

Notice that there is no Case Else statement in the preceding code. This is because whatever number the user enters, it falls within either of the two Case statements. So, one of the two Case statements will always be true. Hence, the Case Else statement is not required as it runs only when all Case statements evaluate to false.

Example #4–“Expression for Testing” is Entered in a Cell and Evaluated by the Command Button

In the following code, the “expression for testing” is taken from cell A1 of the Excel worksheet. There are three Case statements and one Case Else statement.

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

Further, a command button has been created in the worksheet with the help of ActiveX controls. The input entered in cell A1, the command button, and the output obtained in cell B1 are shown in the following image.

Explanation: The preceding code is executed by clicking the command button of the Excel worksheet. Notice that multiple expressions of each Case statement are separated by commas (like “Red”, “Green”, “Yellow”).

The “expression for testing” is entered as “pink” in cell A1. This expression matches none of the three Case statements specified in the code. Consequently, the code of the Case Else statement is executed. So, the output in cell B1 is 4.

Had the user entered a color (in cell A1) specified in any of the three Case statements, the corresponding code would have run. For instance, had the user entered “White” in cell A1 (without the double quotes and with “W” capital), the output in cell B1 would have been 2.

Hence, a change in the input of cell A1 causes the output of cell B1 to change.

Example #5–“Expression for Testing” is Divided by 2 with the MOD Operator of VBA

In the following code, the “expression for testing” is collected from the user. There are two Case statements, namely Case True and Case False. The Case Else statement is not there in the code.

Sub CheckOddEven()
CheckValue = InputBox("Enter a 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

On running the preceding code, a dialog box with the message “enter the number” is displayed. Assume that the user enters 10. The output obtained is shown in the following image.

Explanation: The “expression for testing” entered by the user is 10. This expression is divided by 2 with the help of the MOD operator. If the remainder is equal to zero, the number is divisible by 2. Such a number is even. If the remainder is not equal to zero, the number is not divisible by 2 and is odd.

Since 10 is divisible by 2, the code of the Case True statement is executed. Hence, the output is the message “the number is even.” Notice that a Case Else statement is not required in the preceding code. This is because either of the two Case statements will be true at all times.

Note: The MOD operator of VBA divides one number by the other and returns the remainder as the output.

Example #6–Nested Select Case Statements Where “Expression for Testing” is the Current Date

In the following code, the “expression for testing” is the current date. There are two Select Case statements , one Case Statement (Case 1), and two Case Else statements.

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

On running the preceding code, the output obtained is shown in the following image.

Explanation: The inner Select Case statement (Case 1, 7) is the Case statement of the outer Select Case statement . The outer Select Case statement checks whether the current date is a weekday, Saturday or Sunday.

When the NOW VBA function is enclosed within the VBA WEEKDAY function, the latter returns a day of the week that corresponds with the current date. If the output of the WEEKDAY function is 1, the message to be displayed is “today is Sunday.” If the output of the WEEKDAY function is 7, the message to be displayed is “today is Saturday.” This is because, in the preceding code, VBA considers 1 and 7 as Sunday and Saturday respectively.

The message “today is a weekday” will be returned when the output of the WEEKDAY function is other than 1 and 7. So, the second Case Else statement is executed when Case 1 (today is Sunday) and the first Case Else statements (today is Saturday) are false.

Since this article has been created on a weekday, the output of the Select Case statement is “today is a weekday.”

Note 1: The NOW function of VBA returns the current date and time. This function does not take any arguments. The WEEKDAY function of VBA returns the day of the week corresponding to the specified date.

Note 2: Every nested Select Case statement should necessarily contain a matching End Select statement.

The important points related to the Select Case statement of VBA are listed as follows:

• The Is keyword used in the Case statement and Case Else statement is different from the Is operator of VBA.
• The codes of the Select Case statement of VBA can run successfully if macros have been enabled.
• The “expression for testing” entered in the dialog box should be in accordance with the message of the InputBox function of VBA.

Note: To know more about enabling macros, click the given hyperlink.

This has been a guide to the Excel VBA Select Case statement. Here we discuss how to use the Excel VBA Select Case statements with certain keywords (like Is, To) along with examples and a downloadable Excel template. Take a look at the following Excel VBA articles–

• VBA LCase
• Excel VBA InputBox
• FIND VBA Function
• What is VBA Range?