VBでExcel読み込みが遅いので配列を使った件

業務効率プログラムでExcel操作は避けて通れません。

何かの方法でExcelの読み込みが必要。

Excelに接続して｢ループしながら処理をする」方法が一番簡単に思えます。

ですが、その方法は処理時間の問題が出てきます。

ループしながらでなく、Excelデータを配列に取り込み、その後にループする処理の方が速そうですね。

そこで、Visual Basicで｢Excelを読み込む際にループさせる方法」と｢配列に取り込む方法」で、｢どのくらい処理速度が違うのか」を試してみました。

テスト結果
テストデータ
Excelをループしながら読み込む方法
Excelデータを配列にまとめて取り込む方法
配列に取り込みには

テスト結果

まずはテストの結果です。

Excelをループしながら読み込むと４分以上かかりましたが、配列に取り込むと１分以内で処理が終了します。

※条件を揃えるために｢配列に取り込む方法」では、配列に取り込んだあとにループさせて、別の配列にデータをコピーしています。

データ件数	５列×10,000件
ループしながら読み込む方法	4分～5分
配列にまとめて取り込む方法	1分以内

テストデータ

テストデータは５列×10,000件のExcelデータです。

Excelをループしながら読み込む方法

Excelをループしながら読み込む方法のサンプルコーディングと実行結果です。

ループ処理でExcelのセルごとに、データを取り込んでいる事が分かると思います。

Dim xl As Excel.Application = New Excel.Application
Dim wbs As Excel.Workbooks = xl.Workbooks
Dim wb As Excel.Workbook = wbs.Open("D:\data.xls")

Dim arrdata2(10000, 5) As Object
Dim i As Integer
Dim wStart As Date

wStart = Now

Try
    '-- Excelデータをセルごとに取り込む
    For i = 1 To 10000
        arrdata2(i, 1) = wb.Worksheets(1).Cells(i, 1).value
        arrdata2(i, 2) = wb.Worksheets(1).Cells(i, 2).value
        arrdata2(i, 3) = wb.Worksheets(1).Cells(i, 3).value
        arrdata2(i, 4) = wb.Worksheets(1).Cells(i, 4).value
        arrdata2(i, 5) = wb.Worksheets(1).Cells(i, 5).value
    Next

Catch ex As Exception
    MsgBox("エラー " & ex.Message)

Finally
    wb.Close()
    wbs.Close()
    xl.Quit()
    xl = Nothing

End Try

MsgBox("start=" & Format(wStart, "hh:mm:ss") & " end=" & Format(Now(), "hh:mm:ss"))

実行結果として、最後にメッセージボックスで開始時間と終了時間を表示します。

開始から終了まで4分20秒もかかっていますね。

Excelデータを配列にまとめて取り込む方法

Excelデータを配列にまとめて方法のサンプルコーディングと実行結果です。

条件を合わせるために、取り込み後にループさせて別の配列にデータをコピーしています。

Dim xl As Excel.Application = New Excel.Application
Dim wbs As Excel.Workbooks = xl.Workbooks
Dim wb As Excel.Workbook = wbs.Open("D:\data.xls")

Dim arrdata(10000, 5) As Object
Dim arrdata2(10000, 5) As Object
Dim i As Integer

Dim wStart As Date

wStart = Now()

Try
    '-- Excelデータを配列にまとめて取り込む
    arrdata = wb.Worksheets(1) _
            .range(wb.Worksheets(1).Cells(1, 1) _
            , wb.Worksheets(1).cells(10000, 5)).value
Catch ex As Exception
    MsgBox("エクセル読み込みエラー " & ex.Message)
Finally
    wb.Close()
    wbs.Close()
    xl.Quit()
    xl = Nothing
End Try

'-- 1件ずつ、別の配列にコピーする
For i = 1 To 10000
    arrdata2(i, 1) = arrdata(i, 1)
    arrdata2(i, 2) = arrdata(i, 2)
    arrdata2(i, 3) = arrdata(i, 3)
    arrdata2(i, 4) = arrdata(i, 4)
    arrdata2(i, 5) = arrdata(i, 5)
Next i

MsgBox("start=" & Format(wStart, "hh:mm:ss") & " end=" & Format(Now(), "hh:mm:ss"))

実行結果として、最後にメッセージボックスで開始時間と終了時間を表示します。

開始から終了まで1秒かかっていません。

配列に取り込みには

Excelデータを配列に取り込むには以下になります。

Dim arrdata(, ) As Object

arrdata = wb.Worksheets(1).range(wb.Worksheets(1).Cells(1, 1),wb.Worksheets(1).cells(10000, 5)).value

｢配列 = ブック名.シート名.範囲」です。