The IIf() Function vs. The IIf() Statement

The IIf() function and the IIf() statement are fundamentally different animals.

Unfortunately, they are nearly impossible to tell apart.  And yet, it is important to be able to do so.  Why?  

Because this IIf() function is a runtime error waiting to happen:

IIf(Attempts = 0, 0, Successes / Attempts)

While this IIf() statement will safely return a value even if Attempts = 0:

IIf(Attempts = 0, 0, Successes / Attempts)

So, can you spot the difference between the two?

Telling Them Apart

If you can't spot the difference between the two samples above, that's because there is no difference.

The IIf() function looks identical to the IIf() statement.

The difference is in how–or more precisely, where–they get evaluated.

• The IIf function is a member of the VBA.Interaction standard library.
• The IIf statement gets evaluated by the Jet/ACE database engine's Expression Service (acees.dll).

I wrote about the differences between code and expressions here:

Expressions vs. Code

When is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.

No Longer SetMike Wolfe

The Fundamental Difference Between the IIf() Function and the IIf() Statement

Three words: short-circuit evaluation.

• The IIf statement has it.  
• The IIf function does not.

What is Short-Circuit Evaluation?

In short-circuit evaluation, the code evaluates only those expressions necessary to determine the result.

The IIf statement and IIf function both take three arguments:

• expr: a boolean condition
• truepart: the result to return if expr is True
• falsepart: the result to return if expr is False

With the IIf statement, only two of those arguments are ever evaluated: the expr and–depending on the result of the expr–either the truepart OR the falsepart.

With the IIf function, all three arguments MUST be evaluated before they even get passed to the function.

Why It Matters

Let's go back to the original example code:

IIf(Attempts = 0, 0, Successes / Attempts)

What happens if the value of Attempts is zero?

IIf statement

• The expr evaluates to True.
• The truepart evaluates to 0.
• The statement returns 0.

IIf function

• The expr evaluates to True.
• The truepart evaluates to 0.
• The falsepart evaluates to #ERROR#: Division by zero

The function version returns an error because it was forced to evaluate both the truepart and the falsepart expressions.

Where is IIf Treated as a Statement?

IIf is evaluated as a statement in the following places:

• Queries
• Form/Report/Control Properties (e.g., a TextBox ControlSource)
• The Access Application.Eval() function

For more details–including how to definitively test whether IIf is being treated as a statement or a function–read my article on the differences between expressions and code: Expressions vs. Code.

External References

IIf function (Visual Basic for Applications)

Office VBA reference topic

Microsoft Docso365devx

Image by 3282700 from Pixabay