VBA Switch Case

What is Excel VBA Switch Case?

In Excel VBA, the Switch Case statement is used to execute different blocks of code based on the value of an expression. It is like the Select Case statement, but it allows for a cleaner and more concise syntax when checking multiple values against a single expression.

Consider the following example:

VBA Switch Case - Definition Example - 1

Here, a subroutine is created to check whether the given number is in the first half (1-5) or the latter half (6-10). In this case, a VBA Switch Case Else is also declared to handle values if none of the conditions are satisfied. When you run this code, the output will be printed in the “Immediate” tab.

VBA Switch Case - Definition Example - 2

Let us look at the entire article on how to use the Switch…Case function by reading below.

What is Excel VBA Switch Case?

Select Case is used in VBA for multiple conditional checks against a single expression. It matches the expression against specified values and executes the code block for the first matching value.
The optional Case Else is used for handling values not matched by previous cases.
The expression is evaluated only once, providing a concise syntax for multiple checks on the same expression.
It enhances code readability when dealing with multiple values for a single expression.
Unlike some other languages, there is no fall-through behavior; once a match is found, subsequent cases are skipped.

How to Use VBA Switch Case Statement?

Once you open the Excel Workbook, select “Developer” in the Excel toolbar.

After that, select “Visual Basic.” This will open the VBA (Visual Basic for Applications) Editor in another window.

In the VBA toolbar, select “Insert” and click on “Module”.

Now you can code in VBA modules.
Name the subroutine to check whether the given number of the day is a weekday or a weekend.
Declare an Integer and get the input from the user.
Initialize a Switch/Select case for the Integer variable.
Declare a nested VBA Switch Case for day number 1,7 (weekends).
In the inner Switch case, declare a MsgBox to print “Saturday” (day 7) or “Sunday” (day 1).

vbInformation is a constant in the MsgBox function which will add an icon to the Message Box popup. After that, end the inner Switch case.
Declare another case for the outer Switch case function, for numbers 2 to 6.
Initialize a VBA Switch Case Else to handle the default if none of the cases are true.

Then, end the Switch case function.

Code:

Sub DaysCheck()
Dim day As Integer
day = InputBox(“Enter a number (from 1 to 7)”, “WeekDay”)
Select Case day
Case 1, 7
Select Case day
Case 1
MsgBox “It is a Sunday!”, vbInformation
Case Else
MsgBox “It is a Saturday!”, vbInformation
End Select
Case 2 To 6
MsgBox “It is a Weekday!”, vbInformation
Case Else
MsgBox “Please enter a valid day number!”, vbExclamation
End Select
End Sub
Run the VBA subroutine by clicking the green arrow button on the VBA Toolbar. You can also run the code by pressing “F5”.

If you enter a number greater than 7.

Now that you know how to use the VBA Switch Case enum, you can view some examples of how to use the VBA Switch case below.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

View the different cases/scenarios in which the VBA Switch Case can be used.

Example #1

You have to create an application with the input as the department name for new employees which will give a popup on whom to contact for the onboarding process depending on the department. For this, you can use the VBA Switch Case string solution. With this, you can solve the problem.

Every solution starts with creating the subroutine to name the managers to be contacted for the onboarding process.

Step 1: Start with naming the subroutine.

Step 2: Get the department name as Input from the user by initializing a string variable in VBA.

Step 3: Initialize a Switch Case function for the department name.

Step 4: Check with VBA Switch Case String whether the department string matches with the Cases provided. This is an example of the “HR” department.

Step 5: Similarly declare more cases for departments such as Marketing, IT, Sales and Finance.

For all the cases, name the managers of the specific department.

Step 6: Declare a VBA Switch Case Else before ending the Switch Case to handle default values (if none of the cases are matched).

Then, end the VBA Switch case.

Code:

Sub ReferToManagers()
Dim dept As String
dept = InputBox(“Enter department”, “Departments”)
Select Case dept
Case “HR”
MsgBox “Please contact Ms. Sarah for the onboarding process”, vbInformation
Case “Finance”
MsgBox “Please contact Mr. Brooks for the onboarding process”, vbInformation
Case “Sales”
MsgBox “Please contact Mr. Pearson for the onboarding process”, vbInformation
Case “Marketing”
MsgBox “Please contact Mrs. Attenborough for the onboarding process”, vbInformation
Case “IT”
MsgBox “Please contact Mr. Tennyson for the onboarding process”, vbInformation
Case Else
MsgBox “This department is not valid!”, vbExclamation
End Select
End Sub

Step 7: Run the above subroutine. An input box will pop up as a result.

VBA Switch Case in Excel - Example 1 - Step 7a

VBA Switch Case in Excel - Example 1 - Step 7b

From this, you can find out that Mrs. Attenborough is responsible for the onboarding process for the new employees in the Marketing department.

Example #2

Here, given some Airport codes, you want to print the name of the Airport. This can be done by using the VBA Switch Case function. Given are the values.

The Airport name should be printed in the adjacent column. If there are any invalid airport names, they must be addressed as “Invalid.”

Step 1: Define a function to return the name of the airport. This returns the result as a string

Functions are used since they can be called in Excel without the need to run the code, unlike a subroutine.

Step 2: Define a string variable to store the names of the airports.

Step 3: Start with the VBA Switch case on the Airport code names.

Step 4: Define the Airport codes as shown below.

You can use colons (:) to make the Switch Cases look more neater.

Step 5: Similarly, declare cases for the other airport codes.

Step 6: Define a VBA Switch Case Else to handle the default values in case all the cases fail.

End the Switch case after that.

Step 7: Return the result of the Switch Case to the function so that the function returns a value when called.

Code:

Function FindMyAirport(Name As String)
Dim result As String
Select Case Name
Case “MSP”: result = “Minneapolis-St Paul International Airport”
Case “LAX”: result = “Los Angeles International Airport”
Case “JFK”: result = “John F. Kennedy Airport”
Case “LHR”: result = “London Heathrow Airport”
Case “SFO”: result = “San Fransisco International Airport”
Case Else: result = “Please Enter a valid Airport Code!”
End Select
FindMyAirport = result
End Function

Step 8: Go to the worksheet and type the function name with “=” as shown below.

Step 9: In the brackets, select the cell number you want to.

Now you can get the answer as seen below.

VBA Switch Case in Excel - Example 2 - Step 9b

Step 10: For the others, simply drag the cursor.

Now you can view all the outputs below.

VBA Switch Case in Excel - Example 2 - Step 10b

Example #3

Consider a set of students with their marks given. You’ll need to print their grades which are:

If marks > 95, the Grade is O.
If marks > 90 but less than 95, the Grade is A+.
If marks > 80 but less than 90, the Grade is A.
If marks > 70 but less than 80, the Grade is B+.
If marks > 60 but less than 70, the Grade is B.
If marks > 50 but less than 60, the Grade is C+.
If marks < 40, the Grade is “F”.

Given are the marks and student numbers below.

You also have to either grade them as ‘pass’ or ‘fail.’

If marks > 40, pass, and highlight them in green, else red.

Now you can see how these can be done using the VBA Switch Case.

Step 1: Start with defining a function to grade the students. It accepts an integer (student marks) as an argument.

Step 2: Declare a string variable to store the grades.

You need to define a string variable to return the result from the called function.

Step 3: Start the select case with the marks.

Step 4: Define the conditions for the grades given above in the Switch case as shown.

Step 5: Similarly, define all the grades as required.

Step 6: Declare a VBA Switch Case Else to handle in case none of the options work.

Then end the Switch case

Step 7: Make the result return the grade value by equating the grade value with the function near the end.

Step 8: To check whether a student passed or failed, define a subroutine.

Step 9: Find the size of the table using the xlUp function to count the number of non-empty rows.

Step 10: Define an integer to store all the marks. Then, initialize a FOR-loop to run through the table and assign the values in the “Marks” column to be assigned to the integer.

Step 11: Fill in the grades column (column 3) using the Cells function and calling the GradeMarks function declared earlier.

Step 12: Start a VBA Switch Case with the marks. Define the Fail conditions first (if the marks are below 40). Change the font color to red.

Step 13: Define a VBA Switch Case Else for the Pass grade. Change the font color to green.

End the switch case and continue the FOR-loop.

Code:

Function GradeMarks(marks As Integer)
Dim Grade As String
Select Case marks
Case Is > 95: Grade = “O”
Case Is >= 90: Grade = “A+”
Case Is >= 80: Grade = “A”
Case Is >= 70: Grade = “B+”
Case Is >= 60: Grade = “B”
Case Is >= 50: Grade = “C+”
Case Is > 40: Grade = “C”
Case Else: Grade = “F”
End Select
GradeMarks = Grade
End Function

Sub PassOrFail()
Dim lastRow As Long
lastRow = Range(“A” & Rows.Count).End(xlUp).Row
Dim m As Integer
For i = 2 To lastRow
m = Cells(i, 2).Value
Cells(i, 3).Value = GradeMarks(m)
Select Case m
Case Is < 40
Cells(i, 4).Value = “F”
Cells(i, 4).Font.ColorIndex = 3
Case Else
Cells(i, 4).Value = “P”
Cells(i, 4).Font.ColorIndex = 4
End Select
Next i
End Sub

Step 14: Run the subroutine and then go to the worksheet to view the results.

Excel VBA Switch Case - Example 3 - Step 14

Important Things to Note

Utilize Select Case when you need to check a single expression against multiple values.
There is no need for VBA Switch Case break statements as in some other languages. The code automatically exits the Select Case construct after executing the matched case.
Stick to Select Case when it’s the most appropriate choice. Avoid mixing it with other constructs like If and Else…If without a clear reason.
Ensure the cases are distinct; avoid redundancy in case values that can lead to unexpected results.

Frequently Asked Questions (FAQs)

1. What is the difference between switch and IF in VBA?

2. Is Select Case the same as switch case in VBA?

Yes, in VBA, Select Case is functionally equivalent to the Switch Case construct. They are interchangeable terms for the same conditional branching structure in VBA.

3. What are the disadvantages of switch case in VBA?

• The Switch Case construct in VBA is less expressive compared to some other programming languages.
• Unlike some other languages, VBA’s Switch Case doesn’t support fall-through behavior. Once a matching case is found and executed, it doesn’t continue to evaluate subsequent cases.
• VBA’s Switch Case does not directly support matching ranges of values.
• While suitable for simple scenarios, the Switch Case construct may not be as versatile as the If statement, especially when dealing with more complex and nested conditions.

4. What is the difference between Case and SWITCH in VBA?

Download Template

This article must be helpful to understand the VBA Switch Case, with it features and examples. You can download the template here to use it instantly.

VBA Switch Case Download

VBA Switch Case