How to Use VBA Switch Function (6 Suitable Examples)

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.

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

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

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

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 wanted output.

Input User Defined Value with VBA Switch

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:

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

Read More: How to Use VBA Int Function in Excel ( 3 Examples)


Similar Readings:


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

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.

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

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

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

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

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo