VBA - IIF声明与CDate（变体）

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

我刚刚开始在三周前学习VBA，所以请随意批评我的代码。

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

我想使用IIF语句来做If语句中的内容：

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时遇到错误

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?

因为VBA认为QueryDate不是一个日期......它应该是CDate函数吗？我错过了什么？

2 个解决方案

#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):

你不想为此使用IIf（或几乎任何恕我直言）。问题是IIf不是一个“声明” - 它是一个功能。这意味着所有参数在传递给IIf函数之前都要进行评估。所以，它评估（按顺序）：

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.

这意味着您将收到运行时错误，因为无论QueryDate是否为错误，您都将调用DateDiff。

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

IIf不像其他语言中的三元表达式，因此您不能将它用于保护子句。

#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.

与VBA的逻辑运算符一样，IIF没有短路语义（与C和系列不同）。因此，错误情况的处理方式与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, resulting in a run-time error.

这里即使IsError返回true，也将评估包含CDate（QueryDate）的第二种情况，从而导致运行时错误。

#1

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