VBA doesn't have a ternary conditional expression (e.g. C-style
?:), which leads to a long-winded conditional statement like this when you just want assign a value to the same variable based on a condition:
If <condition> Then x = 1 Else x = 2 End If
In C-style languages, you would just write
x = <condition> ? 1 : 2. You can mimic the
?: ternary operator using VBA's IIf(expr, truepart, falsepart) function,
x = IIf(<condition>, 1, 2), which reduces the verbosity of VBA code.
There are some limitations, though. Unlike the C-style
?: operator which only evaluates either the truepart or falsepart depending on the condition, IIf() is a function call so all its arguments are evaluated and this evaluation strategy can lead to side-effects or cause an unexpected error message.
x = IIf(condition, function1, function2) would cause both function1 and function2 to be called, so this statement would be slower than the equivalent
If condition Then function1 Else function2 End If, where either
function2 are called.
Unexpected error message: You may encounter an unexpected error message if you rely on the value of a variable in truepart or falsepart. For example, the following Excel VBA script searches for a value in a worksheet range B1:B8 and prints its address if it is found, otherwise it prints "Not found".
Sub TestFind() Dim wks As Worksheet Set wks = Sheet1 Dim rng As Range Set rng = wks.[B1:B8].Find(What:="z") Debug.Print IIf(Not rng Is Nothing, "Found " & rng.Address, "Not found") End Sub
If z is in the range, the script would execute without any errors. On the other hand, if it is not, you may see this unhelpful error:
Run-time error '91': Object variable or With block variable not set. The reason is that rng is Nothing, so evaluating the second argument,
"Found " & rng.Address, causes an error.
To sum up, you can use IIf() as a exact substitute for
?: if both truepart and falsepart can always be evaluated (e.g. literals, constants or variables).