VBA识别/默认为哪个日期系统？ [英] Which date system does VBA recognise/default to?

问题描述

我正在用excel编写vba子例程，以将2个日期之间的所有日期值存储在一个维数组中，然后检查另一张纸上某个范围内的每个值。

我的问题是当代码获取日期值（作为整数（或双精度数-实际使用的那个））时，它在工作表上存储的值大于日期值。1462。

我正在使用1904年日期系统，因此我可以使用负时间值，而且我在澳大利亚，所以日期的格式为dd / mm / yyyy。

例如;

在工作表上：
01/01/2019 = 42004

在代码中：01/01/2019 = 43466

我了解，差异与使用1900年和1904年日期系统时的日期值之间的差异相同，这使我相信VBA可能是

此工作簿的设置如下：

Sheet1 在 Range（ A1：中包含从01/01/2019到25/01/2019的日期。 A25）。

Sheet2 在单元格中包含01/01/2019 A1 和10/01/2019在单元格 A2 和一个activeX 命令按钮中。

和代码：

  Private Sub CommandButton1_Click（）
只要
都是昏暗的myStart 
 
是昏暗的myStart =我。范围（ A1）。值$我要结束
我的端= Me.Range（ A2）。值
 
 Dim v As Variant 
 Dim x As Long 
 Dim myArr As Variant 
 ReDim myArr（0 to 100）
x = 0 
 
对于v = myStart到myEnd 
如果v =空，则对于
退出myArr（x）= v 
 Debug.Print（ v = & v）
 Debug.Print（ myArr =& myArr（x））
x = x + 1 
下一个
 
暗淡长行
 lastrow = Me.Cells（Rows.Count，4）.End（xlUp）.Row 
x = 0 0 
对于Sheet1中的每个单元格。Range（ A1：A100）
 Debug.Print（CDbl（cell））
如果cell = myArr（x）然后
 Me.Cells（lastrow，3）.Value = cell 
 Me.Cells（lastrow，4）。值=格式（像元， dd / mm / yyyy）
 lastrow = lastrow + 1 
 End if 
x = x + 1 
 Next 
 End Sub 
  

第3列和第4列中单元格值的输出如下如下（用于演示的前5行）：

  CD Sheet1.Range（ A1：A5）[当显示为数字格式时] 
 1 43466 01/01/2019 42004 
 2 43467 02/01/2019 42005 
 3 43468 03/01/2019 42006 
 4 43469 04/01/2019 42007 
 5 4347 0 05/01/2019 42008 
  

日期以短日期格式而非数字值写入单元格中。

使用胁迫窗口我注意到？cdbl（DateValue（now））= 43496（31/01/2019）我想知道使用1904年日期系统的日期哪个值正确？根据Sheet1中的值，它应该为 42034 。

是由我使用的数据类型或函数引起的问题是正如先前所质疑的那样-当使用VBA将值分配给Sheet2时，工作表会将其转换为1904，这是excel还是其他工具的错误？

我发现了一个与此相同的问题问题

通常，如果有机会，有人以某种错误的方式更改了您的工作簿，考虑在工作簿的开头添加此支票：

  Private Sub Workbook_Open（）
如果ThisWorkbook.Date1904然后
 MsgBox系统为1904，请考虑采取一些措施！ 
 End If 
 End Sub 
  

如果您想知道选择哪个系统-我可以建议永远不要使用1904。

• 将VBA添加到Excel和1904年问题的故事

I'm writing a vba subroutine in excel to store all date values between 2 dates in a single dimension array and then check for each value in a range on another sheet.

My problem is when the code is grabbing the date values (as an integer(or double - whichever one it is actually using)) it is storing a value 1462 greater than the date value on the sheet.

I am using the 1904 date system so I can use negative time values and i'm in Australia so dates are formatted dd/mm/yyyy.

As an example;

On the sheet: 01/01/2019 = 42004

In the code: 01/01/2019 = 43466

I understand the difference is the same as the difference between date values when using 1900 vs. 1904 date systems which leads me to believe perhaps VBA is defaulting the sheet value to be in 1900 and converting the value to 1904 again when the code is run?

This workbook is set up as follows:

Sheet1 contains dates from 01/01/2019 to 25/01/2019 in Range("A1:A25").

Sheet2 contains 01/01/2019 in cell A1 and 10/01/2019 in cell A2and a activeX commandbutton.

And the code:

Private Sub CommandButton1_Click()
Dim myStart As Long
Dim myEnd As Long

myStart = Me.Range("A1").Value
myEnd = Me.Range("A2").Value

Dim v As Variant
Dim x As Long
Dim myArr As Variant
ReDim myArr(0 To 100)
x = 0

For v = myStart To myEnd
    If v = Empty Then Exit For
    myArr(x) = v
    Debug.Print ("v = " & v)
    Debug.Print ("myArr = " & myArr(x))
    x = x + 1
Next

Dim lastrow As Long
lastrow = Me.Cells(Rows.Count, 4).End(xlUp).Row
x = 0
For Each cell In Sheet1.Range("A1:A100")
Debug.Print (CDbl(cell))
    If cell = myArr(x) Then
        Me.Cells(lastrow, 3).Value = cell
        Me.Cells(lastrow, 4).Value = Format(cell, "dd/mm/yyyy")
        lastrow = lastrow + 1
    End If
    x = x + 1
Next
End Sub

The output for the cell values in columns 3 and 4 are as follows (first 5 rows for demonstration):

       C         D             Sheet1.Range("A1:A5")[when displayed as number format] 
1    43466    01/01/2019                42004
2    43467    02/01/2019                42005
3    43468    03/01/2019                42006
4    43469    04/01/2019                42007
5    43470    05/01/2019                42008

The dates are written into the cell as a short date format not numeric value.

Using the intimidate window I've noticed ?cdbl(DateValue(now)) = 43496 (31/01/2019) which is making me wonder which values are correct for the dates using the 1904 date system? Per the values in Sheet1 it should be 42034.

Is the issue caused by a data type or function I've used, is it as questioned earlier - the sheet is converting them to 1904 when the values are assigned to Sheet2 with VBA, is it a bug with excel or something else?

I found a question with the same problem here on mrexcel however the resolution there was change Excel to use the 1900 date system or subtract 1462 from the values in the code which I'm hoping to avoid both of.

解决方案

In general, the date system in VBA is different than the date system in Excel. You can have these 3 date system options:

• Excel date
• Excel date with 1904 property
• VBA date

This is the difference:

The dates are converted to numbers. E.g., every date is converted to a number, but the starting number is a bit different.

• In Excel, the 1 is converted to 01.January.1900;
• In VBA, the 1 is converted to 31.December.1899;
• In Excel with 1904, the 1 is converted to 02.January.1904;

---

The 1904 system (or its lack) is set per Workbook. Thus, if you have 2 workbooks in Excel, one with 1904 and one without it, it would recognize them correspondingly. The system is set in:

File > Options > Advanced > Use 1904 Date System

In general, if there is a chance, that someone somehow changes your workbook with the wrong system, consider adding this check at the openning of the workbook:

Private Sub Workbook_Open()    
    If ThisWorkbook.Date1904 Then
        MsgBox "System is 1904, consider some action!"
    End If        
End Sub

If you are wondering which system to choose - I can recommend never using the 1904.

• Story for the adding VBA to Excel and the 1904 problem

这篇关于VBA识别/默认为哪个日期系统？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！