The Switch function is Microsoft Excel’s in-built function. It is used in Excel VBA, too. The working procedure of this function is quite interesting. It works based on a given value. It searches for this value from a given list of values and then shows the corresponding object of the given value. This article is all about the VBA Switch function and its usage with a detailed explanation.
Introduction to Switch Function
Function Objective:
The Switch function compares one referred value with a list of values and returns the corresponding object of the found value.
Syntax:
Switch(expr-1,value-1,[expr-2,value-2,....[exper-n,value-n]])
Argument:
Argument | Required/Optional | Explanation |
---|---|---|
expr | Required | This expression will match the reference value. |
value | Required | This is the corresponding object that will be delivered. |
Returns:
The return will depend on the value argument, maybe a number, text, or anything.
Available In:
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
VBA Switch Function in Excel: 6 Examples
We will discuss the use of the VBA Switch function with the examples below.
1. Input Value in the Formula with the Switch Function
In this example, we will input the values in the main formula and perform the switch operation.
Step 1:
- First, go to the Developer tab.
- Then choose the Record Macro option.
- Now, put a VBA_Switch_1 in the Macro name box.
- Finally, press OK.
Step 2:
- Press the Macros option.
- Go to the VBA_Switch_1 macro.
- Then, click on Step Into.
Step 3:
- Now, go to the Module option from the Insert tab.
Step 4:
- Write the code below in the command window.
Sub VBA_Switch_1()
Dim Player, Game As String
Player = "Phelps"
Game = Switch(Player = "Phelps", "Swmming", Player = "Nadal", "Tennis", Player = "Messi", "Football")
MsgBox "Name of Sport is : " & Game
End Sub
Step 5:
- Now, click on the marked tab to run the code.
Finally, we will get the expected return.
2. Input User Defined Value with VBA Switch
In this segment, we will take input from the user.
Step 1:
- Form a new Macro named VBA_Switch_2 following the steps shown in the previous method.
- Then, press OK.
Step 2:
- Now, Step Into the VBA_Switch_2 by following the steps of the previous method. Or we can press Alt+F8 to avail this option.
Step 3:
- Type the following code in the command window.
Sub VBA_Switch_2()
Dim Player, Game As String
Player = InputBox("Name of Player")
Game = Switch(Player = "Phelps", "Swimming", Player = "Nadal", "Tennis", Player = "Messi", "Football")
MsgBox "Name of Sport is : " & Game
End Sub
Step 4:
- Now, run the code by applying the previous steps. Or we can press F5 to run the code.
- A new input field will appear. Write “Nadal” on the input box.
- Then press OK.
Finally, we will get the desired output.
3. Use Cell Reference as Input in Switch Function
In this segment, we will use another data set. That is given below:
We will use the cell reference in this example.
Step 1:
- Initiate a new Macro named VBA_Switch_3.
- Now, press OK.
Step 2:
- Get into the macro VBA_Switch_3 by pressing the Alt+F8.
Step 3:
- Write the following code on the command window.
Sub VBA_Switch_3()
Dim Time As Integer
Dim Status As String
Time = Range("B5").Offset(0, 1).Value
Status = Switch(Time <= 70, "Short", Time <= 100, "Perfact", Time > 100, "Lengthy")
MsgBox "Running Time of Episode : " & Status
End Sub
Step 4:
- Run the code by pressing the F5 button.
4. Insert Invalid Value When Applying a Switch Function
We will observe what happens when we enter an invalid value.
Step 1:
- Form a new macro following method shown previously. VBA_Switch_4 is the new macro.
Step 2:
- Press Alt+F8 and step into the macro.
Step 3:
- Command window will open and copy-paste the following code.
Sub VBA_Switch_4()
Dim N As Integer
Dim M As String
Dim validity As Variant
N = InputBox("Input a Number")
On Error GoTo validity
M = Switch(N = 1, "One", N = 2, "Two", N = 3, "Three", N = 4, "Four")
MsgBox "This is: " & M
Exit Sub
validity:
MsgBox "You have entered invalid number"
End Sub
Step 4:
- Now, press F5 to run the code.
- An input window will open. Put “1″ in the input box.
Step 5:
- Then, press OK.
As our input is valid a result is shown.
Step 6:
- Again, run the code by pressing the F5 button.
- Put “10″ in the input box.
Step 7:
- Finally, press OK.
No result is showing. The number is identified as invalid because this number is not mentioned in our code. According to users’ needs, they can customize this validity range.
5. Build Custom Function Using VBA Switch
We will build a custom function of Excel using the VBA Switch. We will apply this to the newly formed column named “Status” of the data set.
Step 1:
- Form another new macro VBA_Switch_5.
- Then press OK.
Step 2:
- Step into the VBA_Switch_5 macro by pressing Alt+F8.
Step 3:
- Copy-paste the code below in the command window.
Function Status(Time As Integer) As String
Status = Switch(Time <= 70, "Short", Time <= 100, "Perfact", Time > 100, "Lengthy")
End Function
Step 4:
- Then, save the code and go to the Excel window.
- Now, type the below formula on cell D5.
=Status(C5)
Step 5:
- Then, press Enter.
We can see that the corresponding result of Cell C5 is showing.
Step 6:
- Now, pull the Fill Handle icon to get results for the rest of the cells.
6. Apply Another Function with VBA Switch
In this segment, we will add a function with the Switch function.
Step 1:
- Create a new macro named VBA_Switch_6.
- Then press OK.
Step 2:
- Enter the macro by pressing Alt+F8.
Step 3:
- Write the following code on the command window.
Sub VBA_Switch_6()
Dim n1, n2, Result As Integer
n1 = Int(InputBox("Enter 1st number"))
n2 = Int(InputBox("Enter 2nd number"))
Result = Switch(n1 > n2, adder(n1), n1 < n2, adder(n2), n1 = n2, n2)
MsgBox (Result)
End Sub
Function adder(a1)
For i = 1 To a1
Sum = Sum + i
Next i
adder = Sum
End Function
Step 4:
- Click F5 to run the code.
- Now, put 10 on both input fields.
Step 5:
- Then press OK on the second input field.
According to the function if both the inputs are the same the show that input as the output
Step 6:
- Now, put 10 as the 1st input and 20 as the second input.
Step 7:
- Then press OK on the second input field.
Things to Remember
-
- You might be confused with the Select Case with this Switch function.
- If none of the expressions are satisfied, the error will show.
- This Switch is a proper replacement for the Nested IF function.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we discussed the VBA Switch function with different examples. I hope this will satisfy your needs. If you have any queries or suggestions, comment in the comment box below.
<< Go Back to Excel SWITCH Function | Excel Functions | Learn Excel