【VBA】行数の多いListObject.ListRowsをFor Eachで回すとエラーになる

こんにちは!大阪市を拠点に活動している『縁紡ぐ』の稲垣です。
SWELLを使ったホームページ制作や、Excel、ACCESS、RPAなどのシステム開発を行っています。
また、Excel、Word、Outlookの研修や、情報セキュリティ研修も行っています。身近なITの相談相手になりたいと思っています。
お気軽にお問い合わせください。
【VBA】行数の多いListObject.ListRowsをFor Eachで回すとエラーになる
今回は、Excelのテーブル機能で作ってある表をVBAで処理するときに発生したエラーについてです。エラー回避の方法については、目次から記事の後半を読んでください。
Excelのテーブルとは
テーブルとは、表を便利に使えるようになるExcelの機能になります。

ホームタブのテーブルとして書式設定でテーブル化することができます。

テーブルに設定すると、フィルタが表示されたり、使える機能が増えます。今回開発する中で、このテーブルですが、VBAで処理をしたい場合に問題が発生しました。
テーブルは、ListObectで取得ができる
まずは、シートにあるテーブルを変数に代入する方法です。
Dim tmpListObject As ListObject
Set tmpListObject = Worksheets("T_テスト").ListObjects("TestTable")
1行目で変数を宣言しています。テーブルは、ListObject型になります。
2行目で、「T_テスト」シートにあるテーブル「TestTable」を取得しています。
ForEachで1行毎に処理する方法
取得したListObjectを1行ずつ取り出しForEachで回す方法です。
'テーブルを取得
Dim tmpListObject As ListObject
Set tmpListObject = Worksheets("T_テスト
").ListObjects("TestTable")
'1行毎処理をする
Dim tmpRow As ListRow
For Each tmpRow In tmpListObject.ListRows
Debug.Print tmpRow(1, 2)
Next tmpRow
8行目でテーブルが格納されているtmpListObjectのListRowsで行が取り出しができるので、 ListRow型の変数(tmpRow)に代入します。

行を取り出すのは、赤い囲みでテーブル全体の行を取り出すイメージです。
テストで50万行あるテーブルで実行するとFor Eachの取り出しでエラーが発生
データ数が増えた時にちゃんと動作するかをテストしたら、エラーになりました。


20万行でテスト

20万行ではエラーが発生します。

10万行でテスト

10万行でもエラーが発生した。

5万行でテスト


5万行では、処理ができました。
レコード数が多く発生するようなテーブルで、ForEachでListRowを取り出す方法は注意が必要!
エラー回避の方法
テーブルの行数が多くなった時に繰り返し処理をしたいときにどうしたらいいか、今回試した回避方法を紹介します。
配列で処理をする
これは、Excelでは王道の方法ですが、配列に入れてぶん回す方法です。
Sub 配列での処理()
'最終行を取得
Dim maxLow As Long
maxLow = Cells(Rows.count, 1).End(xlUp).Rows
'配列に代入
Dim tmpArray As Variant
tmpArray = Range(Cells(1, 1), Cells(maxLow, 8))
'繰り返し処理
Dim i As Long
For i = LBound(tmpArray) To UBound(tmpArray)
Debug.Print tmpArray(i, 2)
Next i
'配列の初期化
Erase tmpArray
End Sub
Excelでよく使われるパターンなのでオススメです!
1行毎に取り出さずにForで回す
Sub リストオブジェクト2()
Dim tmpListObject As ListObject
' シートのテーブルを取得
On Error GoTo ErrorHandler
Set tmpListObject = Worksheets("T_テスト").ListObjects("TestTable")
'レコード数を取得
Dim maxRow As Long
maxRow = tmpListObject.ListRows.count
'繰り返し処理
Dim i As Long
For i = 2 To maxRow
Debug.Print tmpListObject.Range(i, 2)
Next i
Exit Sub
ErrorHandler:
MsgBox "エラーが発生
End Sub
ListObjectなので、SortやDeleteなどListObjectの機能を使うことが出来る
処理の速度差
では、対応方法の処理時間の差を確かめてみましょう。
レコード数は、約50万行で1行ずつセルの値を加算する処理です。
配列での処理
Sub 配列での処理()
'最終行を取得
Dim maxLow As Long
maxLow = Cells(Rows.count, 1).End(xlUp).Rows
'配列に代入
Dim tmpArray As Variant
tmpArray = Range(Cells(1, 1), Cells(510071, 8))
'繰り返し処理
Dim i As Long
Dim tmp As Long
For i = LBound(tmpArray) To UBound(tmpArray)
tmp = tmp + 1
Next i
'配列の初期化
Erase tmpArray
End Sub

処理時間は、0.26とやはり配列は処理が早いです。
ListObjectをForで処理
Sub リストオブジェクト2()
Dim tmpListObject As ListObject
' シートのテーブルを取得
Set tmpListObject = Worksheets("T_テスト").ListObjects("TestTable")
'レコード数を取得
Dim maxRow As Long
maxRow = tmpListObject.ListRows.count + 1
'繰り返し処理
Dim i As Long
Dim tmp As Long
For i = 2 To maxRow
tmp = tmp + tmpListObject.Range(i, 3)
Next i
End Sub

0.97秒です。
測定結果
配列での処理 0.26秒
ListObjectをForで処理 0.97秒
約4倍弱ListObjectの方が時間がかかりました。
結論
ListObjectをForEachで回す場合は、レコード数が多い場合はエラーに気を付ける必要があります。そんなに、レコード数がいかない場合は、利用する価値はありです。
また、レコード数が多い場合は、ListObjectの機能を使いたい場合は、ListObjectをForで回すのがいいと思います。
それ以外は、配列での処理がいい。
どれが正解というのはありませんので、場面場面で選択してするのが良いと思います。

稲垣
- Excel、ACCESSでのシステム開発が得意
- ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
- 自家焙煎するほどのコーヒー好き
- 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
- 保有資格 Kintoneアソシエイト、日商簿記検定2級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
- 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
- お客様にとって本当に良いことかを第一に考える人
コメント