VBA Switch

Excel VBA Switch Statement

Switch in VBA is similar to select case function. The only difference between those is that we have to write much lesser codes in a switch in comparison to select case. We can write a switch statement inside a subprocedure or we can write it as a user-defined function in VBA.

Switch in VBA evaluates an expression and gives the output based on the conditions for that expression. In select case statements, we defined cases that if we have a case the result should be b and so on. This required to write a lot of codes for select case statement but in Switch, we can merge all the conditions and expressions in one statement rather than to have multiple case statements.

Syntax of Switch Statement

Switch calculates the first expression and if the value is true it returns the value for the expression and if the value for expression 1 is not true it goes on for same calculation for expression 2 and if the result is true then value 2 is displayed but if the expression is returned is false, switch moves on to another expression.

So what happens if none of the expression is true and all of them is returned as False, we get run time error in such case unless we have a pre-emptive error handling for such a scenario.

How to Use Switch Statement in Excel VBA?

Below are the different examples to use VBA Switch Statement in Excel.

VBA Switch – Example #1

Let us start with the basic example to get an idea of the VBA switch statement. We will take input from the user and based on that input we will define the output. We will take input from number 1 to 5 and based on the input we will have predefined results but if the user enters any number other than our range we will have to handle that error.

Follow the below steps to use Switch Statement in Excel VBA:

Step 1: From the visual basic tab enter the VB editor from the developer’s tab.

Step 2: In the VB Editor window, insert a new module from the Insert tab.

Step 3: Declare a new subprocedure in the new module just created.

Code:

Sub Sample()

End Sub

Step 4: Declare two variables A and B, one as integer and another as a string.

Code:

Sub Sample()

Dim A As Integer
Dim B As String

End Sub

Step 5: In variable A, store the value is given by the user using the input box function.

Code:

Sub Sample()

Dim A As Integer
Dim B As String
A = InputBox("Enter a Value", "value should be between 1 to 5")

End Sub

Step 6: Now in Variable B we will use a VBA switch statement to have evaluated the result as per input provided by the user.

Code:

Sub Sample()

Dim A As Integer
Dim B As String
A = InputBox("Enter a Value", "value should be between 1 to 5")
B = Switch(A = 1, "One", A = 2, "Two", A = 3, "Three", A = 4, "Four", A = 5, "Five")

End Sub

Step 5: Display the value stored in B using the MSGBOX function.

Code:

Sub Sample()

Dim A As Integer
Dim B As String
A = InputBox("Enter a Value", "value should be between 1 to 5")
B = Switch(A = 1, "One", A = 2, "Two", A = 3, "Three", A = 4, "Four", A = 5, "Five")
MsgBox B

End Sub

Step 6: Run the above code by pressing F5 key and it asks us to input an integer. Provide a number 3 and press ok to see the result.

Step 7: Now let us rerun the code and enter the number 6 and see the result we get.

We get the run time error as in our switch statement there isn’t an expression to define 6. So in such scenarios, we need to have some pre-emptive error handling.

Step 8: To overcome this run time error, declare another variable Var as Variant.

Code:

Sub Sample()

Dim A As Integer
Dim B As String
Dim var As Variant
A = InputBox("Enter a Value", "value should be between 1 to 5")
B = Switch(A = 1, "One", A = 2, "Two", A = 3, "Three", A = 4, "Four", A = 5, "Five")
MsgBox B

End Sub

Step 9: Before our switch statement use the on error goto statement so that if the code finds an error it knows where to go.

Code:

Sub Sample()

Dim A As Integer
Dim B As String
Dim var As Variant
A = InputBox("Enter a Value", "value should be between 1 to 5")
On Error GoTo var
B = Switch(A = 1, "One", A = 2, "Two", A = 3, "Three", A = 4, "Four", A = 5, "Five")
MsgBox B

End Sub

Step 10: Now define what will happen if the code encounters an error,

Code:

Sub Sample()

Dim A As Integer
Dim B As String
Dim var As Variant
A = InputBox("Enter a Value", "value should be between 1 to 5")
On Error GoTo var
B = Switch(A = 1, "One", A = 2, "Two", A = 3, "Three", A = 4, "Four", A = 5, "Five")
MsgBox B
var:
MsgBox "You have entered invalid number"
Resume Next

End Sub

Step 11: Now let us run the code again by pressing F5 or clicking on Play button and input value 6 and then Press ok to see the final result in case switch statement encounters an error.

VBA Switch – Example #2

For this example, I have data in sheet 1 which contains the name of some movies and their time duration in minutes. Based on their length I want to define is the movie short long or super long. Have a look at the data below.

Follow the below steps to use Switch Statement in Excel VBA:

Step 1: Let us start in the same module and start by defining another subprocedure as follows.

Sub Sample1()

End Sub

Step 2: Declare Two Variables A and B, one as Integer another as String as follows,

Sub Sample1()

Dim A As Integer
Dim B As String

End Sub

Step 3: Now in variable A let us store the value of film length for any of the movies.

Sub Sample1()

Dim A As Integer
Dim B As String
A = Range("A3").Offset(0, 1).Value

End Sub

Step 4: Now in Variable B, let us use a VBA switch statement to define what the scenario would be for each film length.

Sub Sample1()

Dim A As Integer
Dim B As String
A = Range("A3").Offset(0, 1).Value
B = Switch(A <= 70, "Too Short", A <= 100, "Short", A <= 120, "Long", A <= 150, "Too Long")

End Sub

Step 5: Now what if the value is greater than 180 should we have another error handler here or we can simply write another expression as True, Value. As all the other values will be returned as the false code will execute this expression.

Sub Sample1()

Dim A As Integer
Dim B As String
A = Range("A3").Offset(0, 1).Value
B = Switch(A <= 70, "Too Short", A <= 100, "Short", A <= 120, "Long", A <= 150, "Too Long",
True, "Boring")

End Sub

Step 6: Display the value of B using the msgbox function.

Sub Sample1()

Dim A As Integer
Dim B As String
A = Range("A3").Offset(0, 1).Value
B = Switch(A <= 70, "Too Short", A <= 100, "Short", A <= 120, "Long", A <= 150, "Too Long",
True, "Boring")
MsgBox B

End Sub

Step 7: Run the code by pressing the F5 key or by clicking on the Play button and see the output.

A3 cell has movie 2 whose length was 100 so the output as short.

VBA Switch – Example #3

Let use switch statement to make a user-defined function for the above example so that we can use it in our worksheet whenever required.

Follow the below steps to use Switch Statement in Excel VBA:

Step 1: In the same module declare a function called as filmlength.

Function FilmLength(Leng As Integer) As String

End Function

Step 2: Now use a VBA Switch statement to define the parameters based on the length as follows,

Function FilmLength(Leng As Integer) As String

FilmLength = Switch(Leng <= 70, "Too Short", Leng <= 100, "Short", Leng <= 120, "Long",
Leng <= 150, "Too Long", True, "Boring")

End Function

Step 3: Now let us go in the worksheet where we had our data, type =FilmLength(

Step 4: We can see our function there give an argument as values in cell B2.

Step 5: After applying the formula press enter.

Step 6: Now drag the function to cell C10 and see the result.

Things to Remember

• Switch function evaluates the expression in it. If the first expression is evaluated true then it does not evaluate the other expressions.
• If none of the expression is true it returns a run time error.
• We need to have an error handler in switch statement or expression so that if every expression is returned false we do not encounter a run time error.
• A switch is similar to select case statement.

Recommended Articles

This is a guide to VBA Switch. Here discuss how to use switch statement in excel VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA InStr
2. VBA CDEC
3. VBA Random Number
4. VBA IsError