How to use IIf function in VBA?

The IIf function in VBA (Visual Basic for Applications) is a shorthand function for doing a quick inline conditional evaluation, and it works similarly to the ternary operator found in some other programming languages. The IIf function evaluates a condition and returns one of two values, depending on whether the condition evaluates to True or False.

Basic syntax

VBA


IIf(expression, truepart, falsepart)

Parameters

expression: The condition you want to evaluate.
truepart: The value returned if expression is True.
falsepart: The value returned if expression is False.

Example

VBA


Sub ExampleIIf()
    Dim age As Integer
    Dim canVote As String
    
    age = 18 ' Change this value to test the result
    
    ' Checks if the age is greater or equal to 18 and assigns the result to canVote
    canVote = IIf(age >= 18, "Yes", "No")
    
    MsgBox "Can vote: " & canVote
End Sub

In this example, we use IIf to determine if a person with a certain age can vote. If age is greater than or equal to 18, canVote is set to “Yes”; otherwise, it is set to “No”. It’s important to note that IIf always evaluates both the truepart and the falsepart arguments, even if the condition is False. This can lead to unexpected side effects if you’re using functions in the truepart/falsepart arguments that modify state or have side effects. If you need to prevent such potential side effects, you should use a regular If…Then…Else block instead of IIf.

Example of the If…Then…Else approach for the same scenario

VBA


Sub ExampleIfThenElse()
    Dim age As Integer
    Dim canVote As String
    
    age = 18 ' Change this value to test the result
    
    ' Using regular If block to determine if the person can vote
    If age >= 18 Then
        canVote = "Yes"
    Else
        canVote = "No"
    End If
    
    MsgBox "Can vote: " & canVote
End Sub

This will perform the same task as the IIf example, but it doesn’t suffer from the potential side effect issue, as only the relevant branch is evaluated based on the condition.

facebooklinkedin