Excel VBA日期格式

Question

我有一个电子表格，其中包含许多日期。这些日期通常以mm/dd/yyyy或mm/dd/yyyy hh:mm的格式显示。

问题是日期并不总是正确输入的，我想在代码中添加检查以确保它们是日期。

我的原始想法是使用IsDate进行检查或者使用CDate，但这似乎没有起作用：它仍然返回字符串而不是日期。

后来我设置了一个小实验，证明了这些函数不像我期望的那样工作。 方法如下：

1. 在A1单元格中输入公式=DATE(2013,10,28)
2. B1单元格中输入公式=A1*1，应该等于一个数字（41575）

3. 运行以下脚本

   Sub test()
   
   MsgBox ("Start:" & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = Format(ActiveCell.Value, "mm/dd/yyyy")
   MsgBox ("After format: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   ActiveCell.Value = CDate(ActiveCell.Value)
   MsgBox ("After Cdate: " & TypeName(ActiveCell.Value) & " " & IsDate(ActiveCell.Value))
   
   End Sub
   

   当脚本开始时，单元格的类型为日期类型，IsDate返回true。运行Format后，它变成了字符串类型，但IsDate仍然返回true。CDate也会将单元格转换为字符串类型。现在，单元格B1将返回0（因为它是一个字符串*1）。
   
   因此，我想总结一下问题：
   1. 为什么Format和CDate会将我的单元格更改为字符串？
   2. 我如何确保单元格返回的是日期值，而不仅仅是看起来像日期的字符串？

- gtwebb

Answer 1

区分单元格的内容、显示格式、VBA从单元格读取的数据类型以及从VBA写入单元格的数据类型与Excel如何自动解释这些是很重要的。由此产生的关系可能有点复杂，因为Excel会将某种类型的值（例如字符串）解释为某种其他数据类型（例如日期），然后根据此自动更改显示格式。最安全的方法是明确地执行所有操作，而不依赖于这种自动方式。（参见例如此先前的答案。）

我运行了您的实验，结果与您的结果不同。我的A1单元格始终保持为日期，B1保持为41575。因此，我无法回答您的问题＃1。结果可能取决于您的Excel版本/设置如何选择根据其内容自动检测/更改单元格的数字格式。

问题＃2，“如何确保单元格返回日期值”：好吧，不确定您所说的“返回”日期值是什么意思，但如果您希望它包含一个数值，该数值基于从VBA写入它的内容以日期形式显示，则可以：

• 向单元格写入字符串值，希望Excel会自动解释为日期并格式化为日期。双手合十。显然，这不是非常健壮的方法。或者，

• 从VBA向单元格写入数字值（显然，Date类型是预期类型，但Integer、Long、Single或Double也可以），并使用.NumberFormat属性（或在Excel中手动）明确地设置所需的日期格式单元格的数字格式。这更加健壮。

如果您想检查现有单元格内容是否可以显示为日期，则可以使用以下函数：

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

示例用法：

Dim cell As Range
Set cell = Range("A1")

If CellContentCanBeInterpretedAsADate(cell) Then
    cell.NumberFormat = "mm/dd/yyyy hh:mm"
Else
    cell.NumberFormat = "General"
End If

- Jean-François Corbett

---

小注释 - 在您的代码中，尽快恢复错误处理是一个好习惯，因此我建议将 "On Error GoTo 0" 移动到更高的一行（在 if 语句之前）。 - Juliusz

@Juliusz：不，那样行不通。On Error Goto会将Err对象重置为零，因此您无法判断是否发生了错误，函数将始终返回True。有关更多信息，请参见此问题，如果您有任何好的想法，请告诉我！ - Jean-François Corbett

@JFC，您说得完全正确！刚刚确认了一下。如果是这样的话，通过分配错误编号并打开错误处理，您的效果仍然会更好。在此示例中，这不会有太大的区别，但对于更复杂的“if”语句来说情况就不同了。 - Juliusz

答案看起来不错，但很遗憾代码在你的电脑上没有做同样的事情。使用你的代码，即使可以解释为日期，它仍然无法将单元格转换为数字，不确定原因。它会将格式更改为“mm/dd/yyyy hh:mm”，但仍然只显示单元格中的字符串，并且没有数值。 - gtwebb

Answer 2

Format将值转换为字符串。由于它可以解析该字符串并获得有效日期，因此IsDate仍然返回true。

如果您不想将单元格更改为字符串，请不要使用Format。（也就是说，一开始不要将它们转换为字符串。）使用Cell.NumberFormat，并将其设置为所需的日期格式。

ActiveCell.NumberFormat = "mm/dd/yy"   ' Outputs 10/28/13
ActiveCell.NumberFormat = "dd/mm/yyyy" ' Outputs 28/10/2013

- Ken White

---

不，CDate将转换为日期类型，而不是字符串。因此使用CDate实际上是一个好主意。它不会将单元格的内容更改为字符串。 - Jean-François Corbett

@Jean-FrançoisCorbett：感谢您的纠正。我已经有一段时间没有使用VBA了，也不记得在其中使用过CDate。我已经进行了编辑以进行更正。 - Ken White

Answer 3

感谢您的输入。显然我看到了一些在其他机器上没有复制的问题。根据Jean的答案，我想出了一个不太优雅的解决方案，似乎可以解决这个问题。

由于如果我直接从cdate传递单元格值，或者只是将其格式化为数字，它会将单元格值保留为字符串，所以我必须将日期值传递到数字变量中，然后再将该数字传递回单元格。

Function CellContentCanBeInterpretedAsADate(cell As Range) As Boolean
    Dim d As Date
    On Error Resume Next
    d = CDate(cell.Value)
    If Err.Number <> 0 Then
        CellContentCanBeInterpretedAsADate = False
    Else
        CellContentCanBeInterpretedAsADate = True
    End If
    On Error GoTo 0
End Function

使用示例：

Dim cell As Range
dim cvalue as double
Set cell = Range("A1")

If CellContentCanBeInterpretedAsADate(cell) Then
    cvalue = cdate(cell.value)
    cell.value = cvalue
    cell.NumberFormat = "mm/dd/yyyy hh:mm"
Else
    cell.NumberFormat = "General"
End If

- gtwebb

---

哇，这太奇怪了。我根本不需要做这个切换。+1 为找到一个好的解决方法！ - Jean-François Corbett

Answer 4

为确保单元格返回的是日期值而不仅仅是看起来像日期的字符串，首先必须将NumberFormat属性设置为日期格式，然后将实际日期放入单元格内容中。

Sub test_date_or_String()
 Set c = ActiveCell
 c.NumberFormat = "@"
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a String
 c.NumberFormat = "m/d/yyyy"
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is still a String
 c.Value = CDate("03/04/2014")
   Debug.Print c.Value & " is a " & TypeName(c.Value) 'C is a date    
End Sub

- Docmarti

Answer 5

使用侧边栏上的value(cellref)来评估单元格。字符串将产生“#Value”错误，但日期会解析为数字（例如43173）。

- Craig