VBA treating dates differently in Excel 2016? Is there any documentation about this?

Question

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:

• 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
• it looks for each value and colors the cell in red if it is found;

In Excel 2010, it runs smoothly, finding one value and not finding the other 3 as expected. Everything is ok.

In Excel 2016/2013, it gets unhappy with the value before 1904 and throws an error

Invalid procedure call or argument (Error 5)

on the Set foundRange = Rows(1).Find(someDates(cnt)).

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.

So the question - Is this feature/behaviour documented?

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

Why is November 2016 selected when searching for January 2016:

• Range.Find not making a difference between January and November (February and December) in VBA Excel

Answer 1

It this feature / behaviour documented?

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.

In the 1904 date system, the first day that is supported is January 1, 1904

Nothing new for you above, I suppose.

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

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)

So, it makes sense that you fail to find something that should not be able to write in the Worksheet.

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

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

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.

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

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