How to Use VBA Switch Function (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Input Value in the Formula with the Switch Function

Step 2:

  • Press the Macros option.
  • Go to the VBA_Switch_1 macro.
  • Then, click on Step Into.

Input Value in the Formula with the Switch Function

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

Input Value in the Formula with the Switch Function

Step 5:

  • Now, click on the marked tab to run the code.

Input Value in the Formula with the Switch Function

Finally, we will get the expected return.

Input Value in the Formula with the Switch Function


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

Input User Defined Value with VBA Switch

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.

Input User Defined Value with VBA Switch

Finally, we will get the desired output.

Input User Defined Value with VBA Switch


3. Use Cell Reference as Input in Switch Function

In this segment, we will use another data set. That is given below:

Cell Reference as Input in Switch Function

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

Cell Reference as Input in Switch Function

Step 4:

  • Run the code by pressing the F5 button.

Cell Reference as Input in Switch Function


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

Insert Invalid Value when Applying a Switch Function

Step 4:

  • Now, press F5 to run the code.
  • An input window will open. Put “1″ in the input box.

Insert Invalid Value when Applying a Switch Function

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.

Insert Invalid Value when Applying a Switch Function

Step 7:

  • Finally, press OK.

Insert Invalid Value when Applying a Switch Function

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

Build Custom Function Using VBA Switch

Step 4:

  • Then, save the code and go to the Excel window.
  • Now, type the below formula on cell D5.
=Status(C5)

Build Custom Function Using VBA Switch

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.

Build Custom Function Using VBA Switch


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

Another Function with VBA Switch

Step 4:

  • Click F5 to run the code.
  • Now, put 10 on both input fields.

 

Another Function with VBA Switch

Step 5:

  • Then press OK on the second input field.

Another Function with VBA Switch

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.

 

Another Function with VBA Switch

Step 7:

  • Then press OK on the second input field.

 

Another Function with VBA Switch


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo