如何检查Excel中的日期单元格是否为空？

If feels like this should be really easy but I dont get it to work without retrieving the value of the cell again.

如果感觉这应该是非常容易的，但是如果不再检索单元格的值，我就不能让它工作。

To start with, I have 2 date cells:

首先，我有2个日期单元格：

Dim agreedDate As Date
Dim completedDate As Date

THIS WORKS .. (but looks messy)

这个工作..（但看起来很乱）

agreedDate = Worksheets("Data").Cells(Counter, 7).Value
completedDate = Worksheets("Data").Cells(Counter, 9).Value

If (IsEmpty(Worksheets("Data").Cells(Counter, 7).Value) = True) Or (IsEmpty(Worksheets("Data").Cells(Counter, 9).Value) = True) Then

[.. do stuff]
End If

THIS DOES NOT WORK - WHY NOT?!

这不起作用 - 为什么不呢？！

agreedDate = Worksheets("Data").Cells(Counter, 7).Value
completedDate = Worksheets("Data").Cells(Counter, 9).Value

If (IsEmpty(agreedDate) = True) Or IsEmpty(completedDate) = True) Then

[.. do stuff]
End If

Is there a way to write the if statement in a clean and easy way?

有没有办法以干净简单的方式编写if语句？

3 个解决方案

#1

Since only variables of type Variant can be Empty, you need a different test for Date types.

由于只有Variant类型的变量可以为Empty，因此需要对Date类型进行不同的测试。

Check for zero:

检查零：

If agreedDate = 0 Or completedDate = 0 Then

But a safer path would be to change the variables to type Variant and then do this test:

但更安全的方法是将变量更改为Variant类型，然后执行此测试：

If IsDate(agreedDate) = False Or IsDate(completedDate) = False Then

#2

The IsEmpty function determines indicated whether a variable has been initialized. If a cell is truly blank then it is considered uninitialized from the IsEmpty standpoint. However, declaring a variable in VBA gives it a default value. In this case the date type variables are essentially 0 or 30-Dec-1899 00:00:00 as demonstrated by the following short snippet.

IsEmpty函数确定指示变量是否已初始化。如果一个单元格真的是空白，则从IsEmpty的角度来看它被认为是未初始化的。但是，在VBA中声明变量会为其提供默认值。在这种情况下，日期类型变量基本上是0或30-Dec-1899 00:00:00，如以下简短片段所示。

Sub date_var_test()
    Dim dt As Date
    Debug.Print Format(dt, "dd-mmm-yyyy hh:mm:ss")
End Sub

If you want to 'tidy up' your code, you might also wish to allow the true boolean return of the IsEmpty function to resolve the boolean condition rather than comparing it to True.

如果你想“整理”你的代码，你可能还希望允许IsEmpty函数的真正布尔返回来解析布尔条件而不是将它与True进行比较。

If IsEmpty(Worksheets("Data").Cells(Counter, 7)) Or IsEmpty(Worksheets("Data").Cells(Counter, 9)) Then
    [.. do stuff]
End If

Given that False is (for all intents and purposes) zero then this will work for your date type variables.

鉴于False（对于所有意图和目的）为零，那么这将适用于您的日期类型变量。

If Not (agreedDate or completedDate) Then
    [.. do stuff]
End If

#3

As Excel Hero pointed out, a date variable cannot be empty. In fact, a date variable is a number, so you should be able to do something like this. Also, notice that the code below uses "Value2".

正如Excel Hero指出的那样，日期变量不能为空。实际上，日期变量是一个数字，所以你应该可以做这样的事情。另请注意，下面的代码使用“Value2”。

Sub test()
    Dim d As Date
    d = Range("A1").Value2
    If d = 0 Then
      MsgBox "ok"
    Else
      MsgBox "not ok"
    End If
End Sub

#1