配列に日付を格納して、オーバーフローしたお話（失敗談）

先日、このような表を受け取った。

Ａ列には、何が入っているのだろう？一つ選択してみると、８桁の数字が入ってい
た。セルの幅が狭すぎて、このような表示になったようだ。
※書類のレイアウト上、この列のために広い幅を確保できないとのこと。

システムから出力された値を、Excelで開いたものらしい。「出荷」は出荷日とのことで、8桁の数字で表されている。これを「m/d」の形式で表示したい。
そこで、8桁の数字を日付データに変換することにした。

Function ToDate(val As String) As Date
    If Len(val) = 8 Then
        ToDate = DateSerial(Left(val, 4), _
                            Mid(val, 5, 2), _
                            Right(val, 2))
    End If
End Function

単体でテストしたところ、意図したとおり、８桁の数字が日付に変換された。
よしよし。

それでは、これらの日付をまとめて変換してみる。

Sub test()
    Dim TargetRange As Range
    Set TargetRange = Range("A2:A5")
    Dim arr As Variant
        arr = TargetRange

    Dim i As Long
        For i = 1 To UBound(arr)
            arr(i, 1) = ToDate(CStr(arr(i, 1)))
        Next
        TargetRange = arr
        TargetRange.NumberFormatLocal = "m/d"
End Sub

何の問題もない、はずだった。しかし、ここでエラーが発生した。

オーバーフロー？たったこれだけのデータで？？
確認すると、配列に値を格納する段階で、既にエラーが発生していた。

従って、今回作成したToDate関数は悪くない。

色々と調べてみたが、理由が分かったのは偶然だった。セルの幅を広げても、表示が「###」のままだったのだ。

書式を調べてみて、理由が判明した。セルが既に、日付として書式設定されていたためだった。
例えばセルに10000と入力して、書式を日付にすると、1927年5月18日になる。10000日を365日で割ると、27.3973年と算出できることから見れば、多分そういうことなのだろうと推測できる（1は、1900年1月1日となる）。

20190610は西暦57216年となるため、配列に入るとか入らない以前に、日付として扱えないのだろう。確認してみると、9999年12月31日が限界のようだ。

ということで、原因が分かったので対策してみた。といっても、配列に格納する前に、書式を標準に戻すという単純な対策だ。

Sub test()
    Dim TargetRange As Range
    Set TargetRange = Range("A2:A5")
    
        ' エラー回避のため、一旦標準書式とする。
        TargetRange.NumberFormat = "G/標準"
    
    Dim arr As Variant
        arr = TargetRange

    Dim i As Long
        For i = 1 To UBound(arr)
            arr(i, 1) = ToDate(CStr(arr(i, 1)))
        Next
        TargetRange = arr
        TargetRange.NumberFormatLocal = "m/d"
End Sub

結果、無事に求める結果を得ることが出来た。

参考まで。