VBA在Excel 2016中对日期的处理方式不同吗?是否有关于此的任何文档? [英] VBA treating dates differently in Excel 2016? Is there any documentation about this?

查看：93
本文介绍了VBA在Excel 2016中对日期的处理方式不同吗?是否有关于此的任何文档?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

      所以问题-此功能/行为是否已记录? 

      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 System does not support dates before 1904 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 interpret 1461 as 31-12-1903, so as per 1900 System. As the Worksheet is switched to the 1904 System, that date is not supported. So, yes, there is some kind of conversion behind to refer to 31-12-1903; otherwise, for 1904 System, it would have tried to find 1/01/1908 (1461 for 1904 System => which is 2923 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 as Date preserves its identity, regardless of the Date System used in that specific Worksheet. However, when you go to do operations on the Worksheet, 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 or Double (serial date) do not seem to work if you do not apply the offset 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 with November (clearly a bug?)

      这篇关于VBA在Excel 2016中对日期的处理方式不同吗?是否有关于此的任何文档?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆