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

2023年7月24日2025年12月28日

「Excel VBAのクラス」についての第17回目です。

コレクションが複数のクラスインスタンスを保持する場合、保持できる要素数の限界はあるのでしょうか。メモリ不足になるまでは限界なく保持できるのでしょうか。

くるみこ

前回、データを30,000行 × 7列に増やしてみた結果、17,257行目の処理中に実行時エラー6「オーバーフローしました」で停止しました。
コレクション化できる最大要素数はいくつなのでしょうか？詳しく調べてみたいと思います。

列数が減れば行数を増やせるという感じなのかなぁ。
でも処理時間がすごくかかりそう。今回もよろしくお願いしますm(_ _)m

はじめに情報収集

ネット検索で簡単に調べられると思っていましたが、情報はあまりありませんでした。

データ型で見るVBAのリファレンスがこちらです。

[https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/data-type-summary]

データ型	記憶領域サイズ	範囲
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側で表示された時点で計測表示します。