VBA Statement - IIF with CDate (Option)

Question

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

I would like to use the IIF statement to execute 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 IIF, for example

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

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

Answer 1

You do not want to use IIf for this (or almost anything IMHO). The problem is that IIf not an “operator” - it is a function. This means that all parameters are evaluated before they are passed to IIf . So he evaluates (in order):

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

This means that you will get a runtime error, because you will call DateDiff regardless of whether QueryDate error.

IIf does not look like ternary expression in other languages, so you cannot use it for protection sentences.

Answer 2

Like the logical operators of VBA, IIF has no short-circuited semantics (unlike C and the family). Therefore, the error case is not handled in the same way as with the If Then else .

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

Here, even if IsError returns true, the second case, which includes CDate(QueryDate) , will be evaluated , which will lead to a runtime error.