Alok Paul
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 this value from a given list of values 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
6 Examples of Using VBA Switch Function in Excel
We will discuss the use of the VBA Switch function with the below examples.
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 below code on 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.
Read More: How to Use VBA Val Function in Excel (7 Examples)
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 on 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 wanted output.
Related Content: How to Use Fix Function in Excel VBA (4 Examples)
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.
Read More: How to Use VBA Int Function in Excel ( 3 Examples)
Similar Readings:
- How to Use VBA InstRev Function (7 Suitable Examples)
- Use the Left Function in VBA in Excel (2 Examples)
- How to Call a Sub in VBA in Excel (4 Examples)
- Use VBA DIR Function in Excel (7 Examples)
- How to Use VBA UCASE Function in Excel (4 Examples)
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 is opened and copy-pastes 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.
Read More: VBA If – Then – Else Statement in Excel (4 Examples)
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 below code on 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.
Read More: VBA Format Function in Excel (8 Uses with Examples)
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.
Read More: How to Use VBA Randomize Function in Excel (5 Examples)
Things to Remember
-
- You may have confusion 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.
Conclusion
In this article, we discussed the VBA Switch function with different examples. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
