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.

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.



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


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *