How to use Switch function in VBA?

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.

The syntax for the `Switch` function is as follows:

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.

Here is an example of how to use the `Switch` function in VBA:

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.

facebooklinkedin