VBA Switch Case

Excel VBA Switch Case

In VBA Switch Case, when we need to logically check or analyze a condition and execute the different lines of codes based on the same, we use IF-Else conditional statement. Similarly, there is one more statement Switch Case which is more widely known as Select Case statement which can check or analyze the multiple logical conditions and execute the code lines based on those. When you have three or more logical conditions to check, it is recommended that you use Select Case or Switch Case instead of a conventional IF-Else statement. Because it makes the VBA Code faster and more understandable instead of nested IF-Else statements in such cases.

Switch Case / Select Case

Switch Case or Select Case in VBA works on similar lines as of IF-Else statements. It checks the multiple logical/conditional expressions against different values (cases). As soon as any of the case is matching the condition (becomes true for the given condition), the system executes that case and rest other cases are ignored to execute. In case, there are two cases that are true for the given condition, only the first one will be executed with the law of precedence.

Syntax for Switch Case/Select Case is as below:

Select Case <Expression to check>
Case value_1
Code to Execute when Expression = value_1
Case value_2
Code to Execute when Expression = value_2
Case value_3
Code to Execute when Expression = value_3
.
.
.
Case value_n
Code to Execute when Expression = value_n
Case Else
Code to Execute when no case value meets the expression/logical test.
End Select

Where,

Expression: This is an expression for which we wanted to check or analyze using different case values.

value_1, value_2, value_3, … are the logical conditions that are needed to be checked for the given expression.

How to Use the Switch Case Statement in Excel VBA?

We will learn how to use the Switch Case statement in Excel by using the VBA Code.

VBA Switch Case – Example #1

We will check whether the given number is less than 100 or more than 100. For this, follow the steps below:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Define a new subprocedure within the inserted module that can hold your macro.

Code:

Sub switch_case_example1()

End Sub

Step 3: Define a new variable named usrInpt which can hold the user value. Make use of the VBA InputBox function to create an input box that takes user values through variable usrInpt.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")

End Sub

Step 4: Use the Select Case statement and supply the value provided by the user through variable usrInpt. This is the expression we need to check with logical conditions.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt

End Sub

Step 5: Enter the first logical test to be checked under the Case statement as follows.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt
Case Is < 100

End Sub

Step 6: Use MsgBox function to add an output message if Case Is < 100 is true.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt
Case Is < 100
MsgBox "The provided number is less than 100"

End Sub

Step 7: Now, we need to provide an executable statement when the value for usrInpt is greater than 100. Add a Case and MsgBox to achieve this.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt
Case Is < 100
MsgBox "The provided number is less than 100"
Case Is > 100
MsgBox "The provided number is greater than 100"

End Sub

Step 8: What if the value provided by the user is exactly the 100? We have no case added for that. Let’s add the one that gives the user a message that the value he entered is 100.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt
Case Is < 100
MsgBox "The provided number is less than 100"
Case Is > 100
MsgBox "The provided number is greater than 100"
Case Is = 100
MsgBox "The provided number is 100"

End Sub

Step 9: We need to end the Select Case statement. Use End Select to end the loop created.

Code:

Sub switch_case_example1()

Dim usrInpt As Integer
usrInpt = InputBox("Please enter your value")
Select Case usrInpt
Case Is < 100
MsgBox "The provided number is less than 100"
Case Is > 100
MsgBox "The provided number is greater than 100"
Case Is = 100
MsgBox "The provided number is 100"
End Select

End Sub

Step 10: Now, run this code by hitting the Run button or F5. A new popup box will appear asking for the value to be checked. I will enter a value as 110 and hit the OK button on the input box. Since the number I enter is greater than 100, as soon as I click on OK, a new message will pop-up on my screen saying, “The provided number is greater than 100”.

VBA Switch Case – Example #2

Suppose we want to capture the grade of students based on their marks. See the steps below on how we can achieve this.

Step 1: Define a new sub-procedure named switch_case_example2.

Code:

Sub switch_case_example2()

End Sub

Step 2: Define two new variables – marks as integer and grades as a string using Dim statement in the sub-procedure created.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String

End Sub

Step 3: Use the VBA InputBox function through which the user can input the value for the variable marks.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")

End Sub

Step 4: Use the Select Case statement and supply the variable marks as an expression to be checked.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks

End Sub

Since we want to assign the grades to students based on the marks they input, we need to define the grades first.

Step 5: Use the case statement to check whether the marks are less than 35. If so, assign value as “F” to variable grades.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"

End Sub

Step 6: If the marks are between 35 to 45, assign value as “D” to variable grades.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"

End Sub

Step 7: If the marks inputted are between 46 to 55, value “C” should get assigned to variable grades.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"

End Sub

Step 8: When the marks are in range 56 to 65, the value assigned to variable grades should be “B”.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"
Case 56 To 65
grades = "B"

End Sub

Step 9: For marks between 66 to 75, the grade should be “A”.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"
Case 56 To 65
grades = "B"
Case 66 To 75
grades = "A"

End Sub

Step 10: If the marks inputted are more than 75, grades variable should be assigned a value as “A+”

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"
Case 56 To 65
grades = "B"
Case 66 To 75
grades = "A"
Case Is > 75
grades = "A+"

End Sub

Step 11: Use End Select the close the Select Case loop.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"
Case 56 To 65
grades = "B"
Case 66 To 75
grades = "A"
Case Is > 75
grades = "A+"
End Select

End Sub

Step 12: Now, we need to check what is the grade associated with the marks a student has inputted. In order to achieve that, use MsgBox function in such a way that it denotes the achieved grades inside a message box.

Code:

Sub switch_case_example2()

Dim marks As Integer
Dim grades As String
marks = InputBox("Please enter the marks")
Select Case marks
Case Is < 35
grades = "F"
Case 35 To 45
grades = "D"
Case 46 To 55
grades = "C"
Case 56 To 65
grades = "B"
Case 66 To 75
grades = "A"
Case Is > 75
grades = "A+"
End Select
MsgBox "Grade achieved is: " & grades

End Sub

Step 13: Run this code by hitting the F5 or Run button and you can see a VBA input box asking for a mark value. I will enter the marks as 72 and hit the OK button present on the input box. As soon as I hit the OK button inside the input box, I will get a message box indicating the grades associated with the marks entered.

This is how we can use the Switch/Select Case statement inside VBA.

Things to Remember

• Switch case is actually a synonym for Select Case statement in VBA. It has been called so because with the help of this statement we can switch between the different outputs at the same time.
• This statement can be used as an alternative to the IF-Else loop whenever we have three or more conditions to check.
• It is OK if you don’t use ELSE Case in Select Case statement. However, in that case, you need to make sure that at least one condition is true throughout the loop.

Recommended Articles

This is a guide to VBA Switch Case. Here we discuss How to Use the Switch Case Statement in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Collection (Examples)
2. VBA IF Statements |Excel Templates
3. How to Use Excel VBA Sort Function?
4. VBA While Loop (Examples with Excel Template)