VBA Select Case

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 is inbuilt into Excel. To use this function, the code is entered through the visual basic editor presented under the developer tab.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Select Case (wallstreetmojo.com)

Explanation

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 excelLogical Operators In ExcelIn Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more 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 tabThe Visual Basic Option Under The Developer TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more. User-defined functions and coding are 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 excelNested If Statements In ExcelIn Excel, multiple IF conditions are IF statements that are contained within another IF statement. They are used to test multiple conditions at the same time and return distinct values. Additional IF statements can be included in the 'value if true' and 'value if false' arguments of a standard IF formula.read more. 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, it needs to create a macro by placing the command button control in the excel sheet.
  • After that, right-click on the command button and select the view code option.
  • Place the code between the command button function and the 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
You can download this VBA Select Case Excel Template here – VBA Select Case Excel Template

Example #1 – Simple Select Case Statement

This example is to understand the simple select case to find the matched value.

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 Case is matched!"

End Select
End Sub

Result:

VBA Select Case

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.

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

Result:

VBA Select Case 1

The ‘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 the ‘Is’ keyword with the select case.

Code:

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.

vba select case example 3

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 is also used with the Command button by creating a macro.

Code:

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.

vba select case example 4

When the value in the Cell A1 changed, it results in a change in the B1 cell also by running the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more 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.

Code:

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 the number is entered.

example 5

Example #6 – Nested Select Case Statements

Nesting is a useful feature of the select case, and the way how it is done is shown.

Code:

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 for case 1 and 7 remaining all are weekdays (Note: case 1 is Sunday and case 7 is Saturday)

example 6

Things to Remember

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>