EDUCBA

Home VBA VBA Resources VBA Program Flow Functions VBA Switch
 

VBA Switch

Madhuri Thakur
Article byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of Switch Statement

Syntax of Switch

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.

You can download this VBA Switch Excel Template here – VBA Switch Excel Template

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.

VBA Switch Example 1-1

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

VBA Switch Example 1-2

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

Code:

Sub Sample()

End Sub

VBA Switch Example 1-3

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

VBA Switch Example 1-4

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

VBA Switch Example 1-5

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

VBA Switch Example 1-6

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

VBA Switch Example 1-7

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.

VBA Switch Example 1-8

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

VBA Switch Example 1-10

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

Declare Variable Example 1-11

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

On Error Go to Example 1-12

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

VBA Switch Example 1-13

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 1-14

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.

Movies Name Example 2-1

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

VBA Switch Example 2-2

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

Declare variables Example 2-3

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

VBA Switch Example 2-4

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

VBA Switch Example 2-5

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

Error Handler Example 2-6

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

VBA Switch Example 2-7

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

VBA Switch Example 2-8

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

filmlength Function Example 3-1

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

VBA Switch Example 3-2

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

VBA Switch Example 3-3

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

Apply the Formula Example 3-4

Step 5: After applying the formula press enter.

VBA Switch Example 3-5

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

Drag the function Example 3-6

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