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

问题描述

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

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

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

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

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


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



3. 运行这个小脚本

     Sub test（）
    
    MsgBox 开始：& TypeName（ActiveCell.Value）&& IsDate（ActiveCell.Value））
    
    ActiveCell.Value = Format（ActiveCell.Value，mm / dd / yyyy ）
    MsgBox（格式后：& 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）。

所以我想总结一下问题：

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

解决方案

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

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

问题＃2，如何确保单元格将返回日期值：好的，不知道你的意思是返回一个日期值，但是如果您希望将 ，那么您可以： b
$ b

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




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

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

  Function CellContentCanBeInterpretedAsADate（cell As Range）As Boolean 
 Dim d As Date 
 On Error Resume Next 
d = CDate（cell.Value）
如果Err.Number<> 0然后
 CellContentCanBeInterpretedAsADate = False 
 Else 
 CellContentCanBeInterpretedAsADate = True 
如果
出现错误GoTo 0 
结束函数
  

使用示例：

  Dim cell As范围
设置单元格=范围（A1）
 
如果CellContentCanBeInterpretedAsADate（单元格）然后
 cell.NumberFormat =mm / dd / yyyy hh：mm
 Else 
 cell.NumberFormat =General
 End If 
  

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.

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

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

解决方案

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.

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.

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:

• 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,

• 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

Example usage:

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屋！