LoginSignup
0
1

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

Last updated at Posted at 2023-10-18

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

事の発端は、
「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との連携を視野に処理速度改善を検討したいと思います…。

0
1
5

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1