VBA IIf() as a ternary conditional expression (sometimes)

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.

Side-effects: 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 function1 or 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).