「Excel VBAのクラス」についての第17回目です。
コレクションが複数のクラスインスタンスを保持する場合、保持できる要素数の限界はあるのでしょうか。メモリ不足になるまでは限界なく保持できるのでしょうか。
前回、データを30,000行 × 7列に増やしてみた結果、17,257行目の処理中に実行時エラー6「オーバーフローしました」で停止しました。
コレクション化できる最大要素数はいくつなのでしょうか？詳しく調べてみたいと思います。
列数が減れば行数を増やせるという感じなのかなぁ。
でも処理時間がすごくかかりそう。今回もよろしくお願いしますm(_ _)m
はじめに情報収集
ネット検索で簡単に調べられると思っていましたが、情報はあまりありませんでした。
データ型で見るVBAのリファレンスがこちらです。
| データ型 | 記憶領域サイズ | 範囲 |
|---|---|---|
| Collection | 不明 | 不明 |
| Dictionary | 不明 | 不明 |
Collection と Dictionary については「不明」となっていました。
Collection オブジェクトの Countプロパティを見ると、Long型となっているので要素数の最大値は「2,147,483,647」と言ってもよいのでしょうか。
Bing チャットで AI にも訊いてみましたがこんな感じの答えが返ってきました。
メモリなど処理能力の影響で最大要素数は異なるようですね。でも、実質的な最大要素数があるといえるようですので、それを検証してみたいと思います。
【この記事でわかること】
Excel VBA クラスをコレクション化する場合の実質的な最大要素数について
実際のデータで検証してみます
それでは、前回までのコードに時間計測用のタイマーをセットして、列数を変化させながら検証してみます。データ量もExcelシートの最下部直前（1,048,575行）まで増やしてテストしました。
はじめに検証結果一覧
初回に実行した結果の一覧です。
| 列数 | 要素数 （行数） | セル個数 | コレクション化 に要した時間 | UserForm 表示時間 | インスタンス 開放時間 |
|---|---|---|---|---|---|
| 2 | 1,048,572 | 2,097,144 | 6分9秒57 | 3時間9分49秒74 | 5分57秒91 |
| 3 | 1,048,572 | 3,145,716 | 6分21秒92 | 3時間0分14秒57 | 8分44秒45 |
| 4 | 433,105 | 1,732,420 | 3分27秒33 | 17分48秒81 | 7分27秒58 |
| 5 | 33,763 | 168,815 | 11秒93 | 9秒40 | 18秒49 |
| 6 | 33,763 | 202,578 | 12秒69 | 11秒29 | 18秒58 |
| 7 | 33,763 | 236,341 | 13秒48 | 14秒37 | 22秒32 |
| 7 | 10,000 | 70,000 | 4秒80 | 3秒74 | 6秒78 |
【補足】複数回実行するとバラツキが出ますが平均的な数値です。
2列と3列の検証結果について
- 要素数については、シートに設定した表の最終行まで取得できています。
Long型の最大値が「2,147,483,647」なので最大要素数までにはまだ余裕がありそうです。 - 「UserForm表示時間」が3時間以上！ これでは実用に耐えられないですね。
- コレクション化に要する時間やインスタンス開放時間も5分以上かかっています。
４列の検証結果について
- 要素数が「433,105」を超えると実行時エラー6「オーバーフローしました」が発生！
要素数が超えないようにコードを修正して再実行しています。 - 「UserForm表示時間」は17分48秒、「コレクション化」が3分27秒でした。
- 我慢すれば実用に耐えられるのかもしれない速度ですが、それでも4列の場合のことです。
5～7列の場合
- これも要素数が「33,763」を超えると実行時エラー6「オーバーフローしました」が発生！
要素数が超えないようにコードを修正して再実行しています。 - 「コレクション化」が14秒以内とかなり速くなりました。
- 「UserForm表示時間」は15秒以内、「インスタンス開放」が約23秒かかります。
- このくらいならなんとか実用に耐えられる速度でしょうか。
7列10,000行の場合
- 結果は、表の一番下の数値です。
- 平均で各工程で5秒程度とほぼストレスのない速度で実行できています。
テストで使ったコード
Option Explicit
Public SelectedKey As String
Public table As clsCol
'タイマー計測用の変数
Public stime As Double 'タイマー開始時刻用
Public etime As Double 'タイマー終了時刻用
'標準モジュール（ここから開始）
Sub rngCollectionTest()
'コレクション化までの計測用タイマーセット
stime = Timer
'インスタンス作成⇒コンストラクタ起動
Set table = New clsCol
'タイマー終了経過時間を計算してメッセージする
etime = Timer - stime
MsgBox "コレクション化経過時間：" & _
Int((etime) / 3600) & "時間" & _
Int((etime) / 60) Mod 60 & "分" & _
Int(etime) Mod 60 & "秒" & _
Int(100 * ((etime) - Int(etime)))
etime = 0 '初期化
stime = 0 '初期化
'UserForm表示までの時間計測開始
If MsgBox("UserFormを表示しますか？", vbYesNo) Then
stime = Timer
UserForm1.Show 'ユーザーフォームを表示
'タイマー計測はUserForm側で計算表示する
End If
'インスタンスを開放する時間を計測開始
stime = Timer
Set table = Nothing
'タイマー終了経過時間を計算してメッセージする
etime = Timer - stime
MsgBox "インスタンス開放_経過時間：" & _
Int((etime) / 3600) & "時間" & _
Int((etime) / 60) Mod 60 & "分" & _
Int(etime) Mod 60 & "秒" & _
Int(100 * ((etime) - Int(etime)))
End Sub【コード補足】
- コード補足は特に必要ないかと思います。
- 計測は3回行っています。「コレクション化」「UserForm表示」「インスタンス開放」それぞれの開始直前に「開始時刻」を計測して変数に代入し終了時「終了時刻」を計測して計算します。
- 「UserForm表示」については、UserForm側で表示された時点で計測表示します。
データ検証のまとめ（おわりに）
以上、Excel VBA クラスをコレクション化できるの最大要素数についての検証結果でした。
メモリなど処理能力の影響で最大要素数は異なることが良くわかりました。前回は、17,257行目の処理中にオーバーフローエラーが発生しましたが、今回の検証では、33,763行目までコレクション化できました。これも何らかの影響によるのでしょう。
最大要素数が大きくても、実質的に運用できる処理時間なのか検証することが大事です。
テストで使った7列のデータでは、約30,000行が実用的に使える最大要素数だと思います。
たとえもっと多くのデータを扱えるとしても、処理時間に3時間もかかってしまうようでは使えませんよね。
そうですね。表示するまでに3時間以上もかかってしまうのはダメですね。
今回、エクセルシートの表データをそのままコレクション化しています。
例えば、必要なデータだけをフィルタリングしてからコレクション化すれば良いかもしれませんね。今度時間があったらテストしてみようと思います。
まとめ
最後に、今回の内容を整理しておきます。
自分で感じた基本的なことはこんな感じです。
参考にしていただければ幸いです。
Excel VBA クラスについての記事一覧
★★★ ランキング参加中！ クリックしてね(^^)/ ★★★
記事のサンプルファイルをダウンロードできます
この記事で使用したサンプルファイルを登録していません。前回記事のサンプルはリンク先からダウンロードできます。
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
