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`.

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

IIf(expression, truepart, falsepart)

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

Here’s an example of how to use the `IIf` function in 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`.

Here’s an example of the `If…Then…Else` approach for the same scenario:

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