Excel VBA日期格式 [英] Excel VBA date formats

问题描述

我有一个包含多个日期的电子表格。这些通常出现在 mm / dd / yyyy 或 mm / dd / yyyy hh：mm 。

I've got a spreadsheet that contains a number of dates. These generally appear in either mm/dd/yyyy or mm/dd/yyyy hh:mm.

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

The problem is that the dates aren't always put in correctly and I want to have checks to make sure they are dates in the code.

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

My original thought was to use IsDate to check or CDate but this didn't seem to work: it was still returning strings instead of dates.

我已经设置了一个小实验，显示这些功能不能按照我的期望工作。方法是：

I've since set up a small experiment which shows that these functions don't work the way I expect them to. Methodology is:

1. 在单元格A1中输入公式 = DATE（2013,10,28） code>


2. 单元格B1公式 = A1 * 1 应该等于一个数字（41575）



3. 运行这个小脚本

1. In a cell A1 I enter the formula =DATE(2013,10,28)
2. Cell B1 formula =A1*1 which should equal a number (41575)

3. Run this little script

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

当脚本启动时，单元格是类型date和 IsDate 返回true。它运行通过格式它是字符串类型，但 IsDate 仍然返回true。 CDate 也会将单元格转换为字符串。单元格B1现在也将返回0（因为它的字符串* 1）。

When the script starts the cell is a of type date and IsDate returns true. After it is run through Format it is of type string but IsDate still returns true. CDate will also convert the cell to a string. Cell B1 will also now return 0 (since its a string*1).

所以我想总结一下问题：

So I guess to summarize the questions:

1. 为什么格式化和 CDate 将单元格更改为字符串？ li>
   
2. 如何确保单元格将返回一个日期值，而不只是一个看起来像日期的字符串？

1. Why are Format and CDate changing my cells to strings?
2. How can I ensure that a cell will return a date value and not just a string that looks like a date?

推荐答案

区分细胞的内容，他们的显示格式，数据类型读取来自VBA的单元格，以及从VBA单元格写入的数据类型，以及Excel如何自动解释这一点。 （参见例如此前的答案）。这些之间的关系可能有点复杂，因为Excel会执行解释值作为某种其他数据类型（例如日期）的一种类型（例如字符串），然后基于此自动更改显示格式。你最安全的打赌它明确地做一切，而不是依靠这个自动的东西。

It's important to distinguish between the content of cells, their display format, the data type read from cells by VBA, and the data type written to cells from VBA and how Excel automatically interprets this. (See e.g. this previous answer.) The relationship between these can be a bit complicated, because Excel will do things like interpret values of one type (e.g. string) as being a certain other data type (e.g. date) and then automatically change the display format based on this. Your safest bet it do everything explicitly and not to rely on this automatic stuff.

我运行了实验，我没有得到相同的结果。我的单元格A1一直保持一个日期，而B1保持在41575.所以我无法回答你的问题＃1。结果可能取决于您的Excel版本/设置如何根据其内容自动检测/更改单元格的数字格式。

I ran your experiment and I don't get the same results as you do. My cell A1 stays a Date the whole time, and B1 stays 41575. So I can't answer your question #1. Results probably depend on how your Excel version/settings choose to automatically detect/change a cell's number format based on its content.

问题＃2，我如何确保单元格将返回日期值：好的，不知道你的意思是返回一个日期值，但是如果您希望将 包含 作为日期显示的数值，根据您从VBA写入的数值，您可以：

Question #2, "How can I ensure that a cell will return a date value": well, not sure what you mean by "return" a date value, but if you want it to contain a numerical value that is displayed as a date, based on what you write to it from VBA, then you can either:

• 将单元格写入您希望Excel将自动解释为日期和格式的字符串值。十字手指。显然这不是很强大。或者，

• Write to the cell a string value that you hope Excel will automatically interpret as a date and format as such. Cross fingers. Obviously this is not very robust. Or,

将数值写入VBA的单元格（显然，Date类型是预期类型，但整数，长，单或双也可以使用 .NumberFormat 属性（或在Excel中手动）将单元格的数字格式显式设置为所需的日期格式。这更加强大。

Write a numerical value to the cell from VBA (obviously a Date type is the intended type, but an Integer, Long, Single, or Double could do as well) and explicitly set the cells' number format to your desired date format using the .NumberFormat property (or manually in Excel). This is much more robust.

如果要检查现有的单元格内容是否可以显示为日期，那么这里有一个功能可以帮助：

If you want to check that existing cell contents can be displayed as a date, then here's a function that will help:

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

这篇关于Excel VBA日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！