【ExcelVBA】配列による読み書き処理を分割した場合の処理時間検証（大容量データを扱う場合）

本記事では処理速度検証を行っていますが、正直やり方に自信がありません。
ご意見ください…。

事の発端は、
「10万行を超えるCSVデータの読込等の処理をVBAで自動化したが、時間が掛かり過ぎる。」
というものでした。
本件についてChatGPT様に相談させていただいた所、
「PC環境にもよるさかい、試行錯誤や性能テストが必要じゃろ？」と仰られたので、検証させていただくものです。

検証方法

・10万行×100列データの読み書きを想定。
・10万÷n行×100列の配列を宣言しデータ格納　→　ワークシート上にn回に分けて書き出す。
・1回の書き出し毎にEraseステートメントで配列に割り当てたメモリを解放する。
　→　配列宣言に最適な要素数を判定

処理時間自体は一般的にApplicationの以下のプロパティ変更で高速化される余地がありますが、今回は割愛しております。
　.Calculation = xlCalculationManual
　.EnableEvents = False
　.ScreenUpdating = False
※上記プロパティ変更を実行する場合は、処理実行後に元に戻すことを忘れずに！

検証画面及びソースコード

ソースコード類はこちら（GitHub）　※結果が見えちゃってるのはご愛敬

Sub 配列分割読み書き検証()
    Dim 分割数 As Long, 回 As Long, 行 As Long, 列 As Long, 値 As Long
    Dim 始時 As Date, 終時 As Date
    With Sheets("配列分割テスト")
        分割数 = .Range("分割数")
        If Int(100000 / 分割数) <> 100000 / 分割数 Then
            MsgBox "除算の余りが生じています" & vbCrLf & "10万を割って余りの出ない分割数を入力してください"
            Exit Sub
        End If
        始時 = Now
        For 回 = 1 To 分割数
            ReDim 配列(1 To 100000 / 分割数, 1 To 100)
            For 行 = 1 To 100000 / 分割数
                For 列 = 1 To 100
                    値 = 値 + 1
                    配列(行, 列) = 値
                Next
            Next
            Range(.Cells(7 + 100000 / 分割数 * (回 - 1), 4), .Cells(7 + 100000 / 分割数 * 回 - 1, 103)) = 配列
            Erase 配列
        Next
        .Cells(7, 4).Resize(100000, 100).ClearContents
        終時 = Now
        .Range("処理時間") = (終時 - 始時) * 24 * 60 * 60
        .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = 分割数
        .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1) = .Range("処理時間")
    End With
End Sub

検証結果

1,2,4,5,8,10及び100,200,400,500,800,1000に分割して検証しましたが、
自身のPC環境下での処理時間は毎回20～22秒とほぼ変化がありませんでした。

まとめ

ChatGPT様は「実に興味深い…PCスペックとか検証コード自体が最適でない可能性もあるで。精進しいや。」と仰られました。

Excelの限界を感じるので、Accessとの連携を視野に処理速度改善を検討したいと思います…。