Contents
In VBA (Visual Basic for Applications), the Switch function evaluates a list of expressions and returns the corresponding value for the first expression in the list that is True. It’s a useful function for handling multiple conditions without the need for complex nested If statements.
Basic syntax
Switch(expr-1, value-1, expr-2, value-2, ..., expr-n, value-n)
Here, expr-1, expr-2,…, expr-n are the expressions that you want to test, and value-1, value-2,…, value-n are the values that will be returned if the corresponding expression is evaluated to True.
The Switch function will evaluate each expression in the order they are provided. As soon as it finds an expression that is True, it will return the associated value and exit. If none of the expressions evaluates to True, the Switch function will return a Null value.
Example
Sub ExampleSwitchFunction()
Dim score As Integer
Dim grade As String
' Sample score
score = 85
' Use Switch to determine the grade based on the score
grade = Switch( _
score >= 90, "A", _
score >= 80, "B", _
score >= 70, "C", _
score >= 60, "D", _
True, "F" _
)
' Output the grade
MsgBox "Your grade is: " & grade
End Sub
In this example, the Switch function checks the score against different ranges and assigns a grade based on where the score falls. Notice that the last condition (True, “F”) acts as a default that will always be true if reached, thereby returning the grade “F” if none of the other conditions are met.
Remember, because the Switch function will exit as soon as it finds a true expression, the order in which you provide conditions is essential. If you place a more general condition before a specific one, it may prevent the more specific condition from ever being evaluated.
Unlock Your Potential
Excel
Access
Power BI
