VBA在Excel 2016中对日期的处理方式不同吗?是否有关于此的任何文档? [英] VBA treating dates differently in Excel 2016? Is there any documentation about this?
问题描述
VBA似乎已在Excel 2016/2013中进行了更改,只要它开始在不会在Excel 2010上引发的地方引发错误即可.
It seems that VBA has been changed in Excel 2016/2013, as far as it starts throwing errors on places, where it does not throw on Excel 2010.
下面的代码执行以下操作:
The code below does the following:
- 在ActiveSheet的第一行中创建17个日期;
- 使用4个值填充日期数组
- 第一行中的一个
- 不在第一行中
- 一个在1913年
- 1904年之前的一个
- creates 17 dates in a the first row of the ActiveSheet;
- fills date array, with 4 values
- one within the first row
- one not in the first row
- one in the year 1913
- one before the year 1904
在Excel 2010中,它运行平稳,找到了一个值,而没有找到其他三个值.一切都很好.
在Excel 2016/2013中,它对1904年之前的值不满意并抛出错误
In Excel 2016/2013, it gets unhappy with the value before 1904 and throws an error
无效的过程调用或参数(错误5)
Invalid procedure call or argument (Error 5)
上的 .
on the
Set foundRange = Rows(1).Find(someDates(cnt))
.因此,似乎在Excel 2016/2013中,在 Date1904 规则,系统会进行某种检查,确认
04.01.1900
在年份1904
之前,因此不能在Excel日期系统中解析为某个日期?在Excel 2010中不是这种情况.So, it seems that in Excel 2016/2013 under the Date1904 rule, there is some kind of a check that is made that
04.01.1900
is before the year1904
and thus it cannot be parsed to a date in the Excel date system? While in Excel 2010 this is not the case.所以问题-此功能/行为是否已记录?
Public Sub TestMe() ThisWorkbook.Date1904 = True Cells.Clear 'clearing up all. Dim cnt As Long For cnt = 3 To 20 Cells(1, cnt) = DateAdd("M", cnt, DateSerial(2016, 1, 1)) Cells(1, cnt).NumberFormat = "MMM-YY" Next cnt Dim someDates(3) As Date someDates(0) = DateSerial(2016, 1, 1) 'exists someDates(1) = DateSerial(2012, 1, 1) 'does not exist in the range someDates(2) = 5000 '08.09.1913 (in VBA) someDates(3) = 5 '04.01.1900 (in VBA) Dim foundRange As Range For cnt = LBound(someDates) To UBound(someDates) Set foundRange = Rows(1).Find(someDates(cnt)) 'Error 5 in Excel 2016 If Not foundRange Is Nothing Then foundRange.Interior.Color = vbRed End If Next cnt ThisWorkbook.Date1904 = False 'all dates with 4 years back End Sub
搜索2016年1月时为什么选择2016年11月:
Why is November 2016 selected when searching for January 2016:
推荐答案
此功能/行为记录了吗?
It this feature / behaviour documented?
好吧,我们说
yes
.尽管未指定它将失败...但是它确实说1904年日期系统does not support
在1904
此处.Well, let's say
yes
. Although does not specify that it will fail... it does say that the 1904 Date Systemdoes not support
dates before1904
here.在1904年日期系统中,支持的第一天是1904年1月1日.
In the 1904 date system, the first day that is supported is January 1, 1904
我想以上对您来说都不是新鲜事.
Nothing new for you above, I suppose.
因此,似乎在Excel 2016/2013中,根据Date1904规则,进行了某种检查,确定04.01.1900在1904年之前,因此无法将其解析为Excel日期中的日期.系统?在Excel 2010中不是这种情况
So, it seems that in Excel 2016/2013 under the Date1904 rule, there is some kind of a check that is made that 04.01.1900 is before the year 1904 and thus it cannot be parsed to a date in the Excel date system? While in Excel 2010 this is not the case
也许它永远都不应该起作用?如果您尝试以下代码,它将无法在
Worksheet
环境(Office 2016)上编写:Perhaps it never should have worked? if you try the following code, it will fail to write on the
Worksheet
environment (Office 2016):Cells(1, 1) = CDate("01-01-1902") ' will fail to write the date only in Date1904 Cells(1, 2) = CDate(-200) ' will fail to write the date (runtime error 1004)
因此,您无法在
Worksheet
中找到不应写的内容是有道理的.So, it makes sense that you fail to find something that should not be able to write in the
Worksheet
.以下代码为您提供了在1904年日期系统->
1461
(VBA/1900年日期系统:31.12.1903
)中失败的第一个序列日期:The following code gives you the first serial date that fails in 1904 Date System ->
1461
(VBA / 1900 Date System:31.12.1903
):Public Sub TestMe2() Dim strMsg As String On Error Resume Next ThisWorkbook.Date1904 = True For cnt = 5000 To 5 Step -1 Set foundRange = Rows(1).Find(CDate(cnt)) If Err.Number <> 0 Then 'Error 5: cnt = 1461 (31.12.1903 in VBA) strMsg = "Range.Find failed for CDate(" & cnt & ")" & Chr(13) & _ Err.Description MsgBox strMsg, vbInformation, "Gap found" GoTo Test2_End End If If Not foundRange Is Nothing Then foundRange.Interior.Color = vbGreen Next cnt Test2_End: ThisWorkbook.Date1904 = False End Sub
这意味着,当调用
Range.Find
时,它会按照1900 System将1461
解释为31-12-1903
.由于Worksheet
切换到1904 System,因此不支持该日期.因此,是的,后面有某种转换可以引用31-12-1903
; 否则,对于1904 System,它会尝试找到1/01/1908
(对于1904 System =1461
=>,对于1900 System =2923
=1461
+1462
),并且该错误不会显示(1904序列> 0).It means that when calling
Range.Find
, it does interpret1461
as31-12-1903
, so as per 1900 System. As theWorksheet
is switched to the 1904 System, that date is not supported. So, yes, there is some kind of conversion behind to refer to31-12-1903
; otherwise, for 1904 System, it would have tried to find1/01/1908
(1461
for 1904 System => which is2923
for the 1900 System =1461
+1462
), and the error would have not showed up (1904 serial > 0).我猜想
VBA
上的Date
保留其身份,而不管该特定Worksheet
中使用的 Date System .但是,当您在Worksheet
上执行操作时,会受到限制.不知道这是否是发行版,但我可以肯定他们已经等了从这个意义上摆脱进一步支持的机会.I guess that what is on the
VBA
asDate
preserves its identity, regardless of the Date System used in that specificWorksheet
. However, when you go to do operations on theWorksheet
, limitations apply. Not sure if it's a release cut, but I am pretty sure they had waited the chance to get rid of further support in this sense.除非您避免使用不同的系统混合源数据,否则我根本无法解决它.即使使用
Long
或Double
(序列日期)进行比较,如果您不应用天数的offset
,似乎也不起作用.I don't think you can work it around at all, unless you avoid to mix source data using different systems. Even though, the comparisons using
Long
orDouble
(serial date) do not seem to work if you do not apply theoffset
of days.下面的代码是您的修改,以找出其中的所有选项:
The code below is a modification of yours, to find out about all the options there:
Public Sub TestMe() Dim cnt As Long Dim ws As Worksheet, rnCell As Range Dim txt As String Dim bln1904 As Boolean, offsetDays As Integer, blnOffset As Boolean On Error Resume Next ' Select testing mode bln1904 = (vbYes = MsgBox("Switch temporarily to Date1904?", vbYesNo, "Date Mode")) ThisWorkbook.Date1904 = bln1904 If bln1904 Then blnOffset = (vbYes = MsgBox("Apply Offset Days for 1904 to date serials? (1642 days)", vbYesNo, "Date Conversion")) Else blnOffset = False End If ' Fill in Worksheet test data Cells.Clear 'clearing up all. Cells(1, 1) = CDate("01-01-1902") ' will fail to write the date only in Date1904 Cells(1, 2) = CDate(-200) ' will fail to write the date Cells(1, 3) = CDate("31-03-2012") ' in Date1904 will write 30-03-2008 Cells(1, 4) = CDate("31-10-2012") ' in Date1904 will write 30-10-2008 For cnt = 5 To 20 Set rnCell = Cells(1, cnt) rnCell = DateAdd("M", cnt - 2, DateSerial(2016, 1, 1)) Next For cnt = 1 To 20 Set rnCell = Cells(1, cnt) With rnCell .NumberFormat = "DD-MMM-YY" .ColumnWidth = 10 End With Next cnt ' Fill in data to find Dim someDates(9) As Date someDates(0) = DateSerial(2016, 1, 1) - IIf(bln1904 , offsetDays, 0) someDates(1) = 42370 - IIf(bln1904, offsetDays, 0) '01.01.2016 (in VBA) someDates(2) = CDate("01-04-2016") - IIf(bln1904, offsetDays, 0) someDates(3) = 42461 - IIf(bln1904, offsetDays, 0) '01.04.2016 (in VBA) someDates(4) = 42675 - IIf(bln1904, offsetDays, 0) '01.11.2016 (in VBA) someDates(5) = 40999 - IIf(bln1904, offsetDays, 0) '31.03.2012 (in VBA): 42461 - 1462 someDates(6) = 41213 - IIf(bln1904, offsetDays, 0) '31.10.2012 (in VBA): 42675 - 1462 someDates(7) = 1462 - IIf(bln1904, offsetDays, 0) '01.01.1904 (in VBA) someDates(8) = 1461 - IIf(bln1904, offsetDays, 0) '31.12.1903 (in VBA) someDates(9) = 5 - IIf(bln1904, offsetDays, 0) '04.01.1900 (in VBA) Dim foundRange As Range Err.Clear For cnt = LBound(someDates) To UBound(someDates) Set foundRange = Rows(1).Find(someDates(cnt)) If Err.Number <> 0 Then 'Error 5: cnt = 8 strMsg = "Range.Find failed for date " & Format(someDates(cnt), "DD-MM-YYYY") & _ ". cnt = " & cnt & Chr(13) & Err.Description MsgBox strMsg, vbInformation, "Error found" GoTo Test_End: End If If Not foundRange Is Nothing Then With foundRange .Interior.Color = vbGreen If Not (.Comment Is Nothing) Then txt = .Comment.Text If Not (Trim(txt) = vbNullString) Then .Comment.Delete txt = IIf(Trim(txt) = vbNullString, "Found for date(s): ", txt & ", ") & _ "D" & cnt & " " & Format(someDates(cnt), "DD-MM-YYYY") .AddComment (txt) txt = vbNullString End With End If Next cnt Test_End: ThisWorkbook.Date1904 = False 'all dates with 4 years back End Sub
如果尝试不同的选项,则会发现没有偏移量,情况会变得一团糟.顺便说一句,我不太确定它如何混淆
January
和November
(显然是个错误?)If you try the different options, will find out that without the offset, things get messed. Aside note, I am not quite sure as per how it confuses
January
withNovember
(clearly a bug?)这篇关于VBA在Excel 2016中对日期的处理方式不同吗?是否有关于此的任何文档?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!