VBA - IIF Statement with CDate(variant)

Question

I just started learning VBA three weeks ago, so feel free to critique my code.

I would like to use IIF statement to do what is inside the If statement:

Dim rng_ModPlanStart as range
Dim QueryDate as Variant

Set rng_ModPlanStart = ThisWorkbook.ActiveSheet.Range("AH2:AH" & LastCell)
rng_ModPlanStart(1).Offset(-1, 0).Value = "Module Planned Start"

    For Each cl In rng_ModPlanStart
        QueryDate = Application.VLookup(ActiveSheet.Range("E" & cl.Row), Sheets("Chamber Schedule").Range("B:U"), 20, False)
        If Not ((IsError(QueryDate))) Then
        cl.Value = DateDiff("d", CDate(QueryDate), Date)
        End If
    Next cl

But I get an error when trying to use the IIF like

IIF(IsError(QueryDate), "", DateDiff("d", CDate(QueryDate), Date))

because VBA thinks QueryDate is not a date... which it should be by the CDate function right? What am I missing?

Answer 1

You don't want to use IIf for this (or for almost anything IMHO). The problem is that IIf isn't a "statement" - it's a function. That means that all of the parameters are evaluated before they are passed to the IIf function. So, it evaluates (in order):

IsError(QueryDate)
""
DateDiff("d", CDate(QueryDate), Date)   'This is still evaluated if IsError returns True.

That means you'll get a runtime error, because you'll be calling DateDiff regardless of whether QueryDate is an error.

IIf isn't like a ternary expression in other languages, so you can't use it for guard clauses.

Answer 2

Like VBA's logical operators, IIF does not have the short-circuiting semantic (unlike C and family). Therefore the error case is not handled the same way as with an If Then else statement.

IIF(IsError(QueryDate), "", DateDiff("d", CDate(QueryDate), Date))

Here even if IsError returns true, the second case which includes CDate(QueryDate) will be evaluated, resulting in a run-time error.