Contents
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
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
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
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.
Unlock Your Potential
Excel
Access
Power BI
