What is Excel VBA Switch Function?
In Excel VBA, the Switch Function allows you to declare conditional statements in one line as opposed to the Select/Switch case, which is similar to the Switch function; the critical difference is that in the Switch function, all the conditions are evaluated, and then “binned.” It means that before the resultant statement can be executed, all the conditions in the Switch function are checked.
You can learn how to use the VBA Switch function below effectively. Consider the following example:
Create a subroutine to check the city names with their respective country and return the country name. It can be done using the VBA Switch function instead of the VBA Select Case since it will take more lines of code unnecessarily. The name is taken as input from the user, as shown.
After clicking on the “OK” button, you will get a message box showing the name of the country whose city you wrote as an input.
Table of contents
Key Takeaways
- VBA Switch function is very similar to the Select Case. In the SWITCH function, all the conditions are written in one line.
- All the conditions are evaluated first, then the resulting code is executed for any matching statement. It is used when the output requires a single value.
- When none of the conditions are true, the Switch function returns a Null value as the VBA Switch Default value.
- It is used for very simple case-switching problems.
Syntax
The syntax to use the VBA Switch function is as seen below.
Switch(expr-1,value-1,[expr-2,value-2,....[exper-n,value-n]])Where:
- expr- The conditional expressions to be checked.
- n- Number of conditions in the Switch function.
- All the expressions to check the condition are separated with a comma.
Learn more on how to use the VBA Switch function below.
How to Use VBA Switch Function in Excel VBA?
Learn how to effectively use the VBA Switch function in Excel VBA by following a step-by-step tutorial below.
- In the Workbook, select the “Developer” part in the Excel toolbar.
Here, select the “Visual Basic” icon on the far top left-hand side of the Excel Workbook. It opens the VBA editor. In the VBA toolbar, select “Insert” and click on “Module”.
With that, you have successfully started to code in Excel VBA. Given below is a table of students. You want to give out remarks based on their performance, and you need to get it done as soon as possible with minimum effort. It is where you can use the VBA Switch function to build a system that remarks on whether the student has passed or failed.
In this example, there are two ways to implement this. One is with a function call in the Excel sheet, and the other one is by using a subroutine. Both will be seen in this example.
- Define the function which is used to return comments depending on the student’s score.
This function takes the marks as an integer value and returns a String value. - Provide value for the function by using the VBA Switch function and ending the function.
Here, based on the range of marks, a variety of remarks are assigned.
• If marks>=80, return “Excellent”
• If marks>=40, return “Can Improve”
• If marks<40, return “Fail.” - Initialize a subroutine to give whether a student has passed or failed in the exam. Initialize the marks as an integer.
- Take a value from the table and assign it to the marks variable. Here, the value of cell “B6” is shown.
- Call the function to give remarks that were defined earlier and assigned to a variable Result.
- Print the result in a message box. The vbInformation is a constant used in Excel VBA to depict an icon in the Message Box.
Code:
Sub PassOrFail()
Dim marks As Integer
marks = Range(“B6”).Value
Result = RemGive(marks)
MsgBox “The result is: ” & Result, vbInformation
End Sub
Function RemGive(marks As Integer) As String
RemGive = Switch(marks >= 80, “Excellent”, marks >= 40, “Can Improve”, marks < 40, “Fail”)
End Function - Run the above subroutine to view the results. You can also press the “F5” button to do so.
If you run the function on the worksheet, you get.
Select the cell you want.
As you can see, the value is printed in the cell where you called the function. This is the main difference between a function and a subroutine. A function in Excel VBA always returns some value.
Examples
You can view some more interesting examples of how to use the VBA Switch function below.
Example #1
In this example, you have a list of students along with their grades, you want to write a function that can be called in the worksheet and used accordingly to the table. It can also be done using the VBA Switch function.
The remarks are as shown:
- If the grade is A, then type “Outstanding.”
- If the grade is B, then type “Can do better.”
- If the grade is C, then type “Need improvement.”
- If the grade is D, type “Need to improve immediately.”
- If the grade is E, type “Prone to academic suspension.”
- If the grade is F, type “Academic Probation.”
- Step 1: Define a function to return the remarks when the grade is given.
Here, the function accepts the grade as a string, returns a remark depending on the grade, and returns a String value, that is, the result.
- Step 2: Define a variant data type in VBA that is used to deal with error handling.
- Step 3: Perform Error handling if none of the conditions in the Switch function are true. It is one of the ways to deal with the VBA Switch function default value.
- Step 4: Give value to the function by starting the VBA Function Switch to check values.
- Step 5: Declare the remarks in the switch function based on the necessary grade after a comma.
- Step 6: Similarly, follow this and write remarks for all the grades. Exit the function if there are any errors encountered.
- Step 7: Define the validity block. Here, this code block is executed to deal with invalid values.
Code:
Function GiveRemark(Grade As String) As String
Dim validity As Variant
On Error GoTo validity
GiveRemark = Switch(Grade = “A”, “Outstanding”, Grade = “B”, “Can do better”, _
Grade = “C”, “Need improvement”, Grade = “D”, “Need to improve immediately”, _
Grade = “E”, “Prone to academic suspension”, Grade = “F”, “Academic Probation”)
Exit Function
validity:
GiveRemark = “Please enter a valid grade”
End Function
- Step 8: Call the function as shown in the Excel Worksheet.
After selecting the required cell for the remark, press Enter.
Now, you can simply drag the entire function throughout the table.
With this, you can see the results.
With this, you’ve learned how to deal with the VBA Switch function default value, error handling, and more context on how to use the VBA Switch function.
Example #2
In this example, based on comparing two numbers, you will find the factorial of the greater number. If both the numbers are equal, you’ll simply return the number. For such a simple case, the VBA Switch function can be used to solve this problem. The result should be printed in a message box.
- Step 1: Declare a function to return the factorial of a given number.
Here, all the values from 1-n will be multiplied and their product will be the factorial.
For example, 3! = 3*2*1 = 6. From this, you can say that the factorial of 3 is 6.
- Step 2: Start with an If-Else VBA block to check if a number is 0.
If it is 0, then the factorial of 0 is 1. Hence, the value of 1 is assigned to the factorial value.
- Step 3: In the Else block, if the number is not 0, then call a recursive function, multiply the current number with the previous number, and end the function. Here, since you’re recursively calling the function, there’s no need for a looping structure.
- Step 4: Declare the subroutine to find the factorial value for the greatest between two numbers.
- Step 5: Define three integer variables. Two variables are used as input from the user, and the third one is used to get the result of the factorial value. Get the numbers you want compared.
- Step 6: Assign the result variable with the VBA Switch function where the conditions n1>n2, n1<n2, and n1=n2 are being checked.
You need to find the factorial of the greater number so,
- If n1>n2, then find the factorial of n1.
- If n1<n2, find the factorial of n2.
- If n1=n2, simply return the same value.
- Step 7: Print the result in a message box function.
Code:
Sub VBA_Fact()
Dim n1, n2, Result As Integer
n1 = Int(InputBox(“Enter 1st number”))
n2 = Int(InputBox(“Enter 2nd number”))
Result = Switch(n1 > n2, Factorial(n1), n1 < n2, Factorial(n2), n1 = n2, n2)
MsgBox (Result)
End Sub
Function Factorial(n)
If n = 0 Then
Factorial = 1
Else
Factorial = n * Factorial(n – 1)
End If
End Function
- Step 8: Run the above subroutine and view the output.
Since 4 is greater than 3, the factorial of 4 is calculated, which is 24.
Important Things to Note
- Unlike Select Case, the Switch Function is a function which returns a value.
- Ensure correct variable declaration when handling and comparing values in the VBA Switch function.
- Perform Error handling to deal with the Switch Function default value, since simply returning a value may result in a type mismatch error in VBA, stopping the entire code.
- You can combine operators and conditions in a VBA Switch function. All the conditions and their results are separated by commas.
- Make sure that all the conditions are unique.
Frequently Asked Questions (FAQs)
No, you cannot nest switch functions in Excel VBA as it does not support it.
While comparing String values in Excel VBA, yes, the Switch function is case-sensitive.
Yes, you can create user-defined functions to run as macros in your Excel and task the conditions accordingly.
Consider the example below:Function RemGive(marks As Integer) As String
RemGive = Switch(marks >= 80, "Excellent", marks >= 40, "Can Improve", marks < 40, "Fail")
End Function
When the function is called, you can see it running when it is called in Excel.
There is no inbuilt way to handle errors in the Switch function. Instead, to handle it, you can use the VBA GoTo function to execute a line of code in case, none of the Switch conditions are true. You must declare the variable as a Variant data type.
For example:
Dim validity As Variant
On Error GoTo validity
GiveRemark = Switch(Grade = "A", "Very Good", Grade = "B", "Good”)
Exit Function
validity:
GiveRemark = "Please enter a valid grade"
End Function
In this example, if there’s an error due to an invalid value, the “validity” code block will be executed.
Download Template
This article must be helpful to understand the VBA Switch Function, with its features and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Switch Function. Here we learn how to use SWITCH function in Excel VBA code, along with its syntax & step-by-step examples. You can learn more from the following articles –
Leave a Reply