本ページには広告が含まれています。

Excel VBA クラス コレクションの最大要素数について(Lv.17)

Excel VBA クラス コレクションの最大要素数について
画像はCanva「Text to Image」で生成しました

「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 にも訊いてみましたがこんな感じの答えが返ってきました。

Bingチャットで AI に訊いてみた

メモリなど処理能力の影響で最大要素数は異なるようですね。でも、実質的な最大要素数があるといえるようですので、それを検証してみたいと思います。

【この記事でわかること
Excel VBA クラスをコレクション化する場合の実質的な最大要素数について

実際のデータで検証してみます

それでは、前回までのコードに時間計測用のタイマーをセットして、列数を変化させながら検証してみます。データ量もExcelシートの最下部直前(1,048,575行)まで増やしてテストしました。

はじめに検証結果一覧

初回に実行した結果の一覧です。

列数要素数
(行数)
セル個数コレクション化
に要した時間
UserForm
表示時間
インスタンス
開放時間
21,048,5722,097,1446分9秒573時間9分49秒745分57秒91
31,048,5723,145,7166分21秒923時間0分14秒578分44秒45
4433,1051,732,4203分27秒3317分48秒817分27秒58
533,763168,81511秒939秒4018秒49
633,763202,57812秒6911秒2918秒58
733,763236,34113秒4814秒3722秒32
710,00070,0004秒803秒746秒78

【補足】複数回実行するとバラツキが出ますが平均的な数値です。

2列と3列の検証結果について

列数が2列と3列の検証結果画像
  • 要素数については、シートに設定した表の最終行まで取得できています。
    Long型の最大値が「2,147,483,647」なので最大要素数までにはまだ余裕がありそうです。
  • 「UserForm表示時間」が3時間以上! これでは実用に耐えられないですね。
  • コレクション化に要する時間やインスタンス開放時間も5分以上かかっています。

4列の検証結果について

列数が4列の検証結果画像
  • 要素数が「433,105」を超えると実行時エラー6「オーバーフローしました」が発生!
    要素数が超えないようにコードを修正して再実行しています。
  • 「UserForm表示時間」は17分48秒、「コレクション化」が3分27秒でした。
  • 我慢すれば実用に耐えられるのかもしれない速度ですが、それでも4列の場合のことです。

5~7列の場合

列数が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 クラスをコレクション化できるの最大要素数について

【リファレンスなどの情報から】

  1. VBAでコレクション化できる最大要素数の制限は不明(=ない)。
  2. コレクションのCountプロパティはLong型なのでそれが最大値ともいえる。

【実際の検証結果から】

  1. 取得するデータの「列数×行数」の量によって制限される最大要素数が変わる。
  2. 実行する環境(メモリやPC等々)による影響で最大要素数が異なる。
  3. 最大要素数が多くても処理速度(時間)によって実用的な数量を決定すべき

自分で感じた基本的なことはこんな感じです。

参考にしていただければ幸いです。

Excel VBA クラスについての記事一覧

Excel VBA クラスの作成・使い方について(Lv.1)
Excel VBA クラスのプロパティ設定について(Lv.2)
Excel VBA クラスにメソッドを設定する方法(Lv.3)
Excel VBA クラスをインスタンス化する方法(Lv.4)
Excel VBA クラス Propertyプロシージャ(Lv.5)
Excel VBA クラス Property Getプロシージャ(Lv.6)
Excel VBA クラス Property Letプロシージャ(Lv.7)
Excel VBA クラス Property Setプロシージャ(Lv.8)
Excel VBA シートの表データからクラスを作成 (Lv.9)
Excel VBA クラス インスタンスをコレクション化(Lv.10)
Excel VBA クラス コンストラクタとデストラクタ(Lv.11)
Excel VBA クラス コンストラクタを利用する方法(Lv.12)
Excel VBA クラス コレクションの要素取得方法(Lv.13)
Excel VBA クラス コレクション要素の追加と削除(Lv.14)
Excel VBA クラス コレクションの内容を書き出す(Lv.15)
Excel VBA クラス UserFormで機能を操作する(Lv.16)
Excel VBA クラス コレクションの最大要素数について(Lv.17)

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

スポンサーリンク
スポンサーリンク

記事のサンプルファイルをダウンロードできます

この記事で使用したサンプルファイルを登録していません。前回記事のサンプルはリンク先からダウンロードできます。

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください