【テクニカル・上級編】コレクション（Collection）のキー重複エラーを回避する：辞書型（Dictionary）との賢い使い分け – Excel VBA解析バイブル

ExcelVBA

2026.08.27

枯れた技術の深淵：CollectionとDictionary、その「境界線」を掌握せよ

VBAを単なる「マクロ」と呼ぶ者は、その真のポテンシャルを見誤っている。我々にとってVBAは、Windows APIという巨大なレガシー・インフラを直叩きし、メモリの深層にまでアクセス可能な「究極のスクリプト言語」である。

今回は、データ管理の要である「キーによる一意性管理」において、なぜ多くのエンジニアが『Collection』を捨て、『Dictionary』に回帰するのか。そして、その選択がシステムの安定性とパフォーマンスにどう直結するのかを、アーキテクトの視点で解剖する。

—

1. Collectionの限界：なぜ「キー重複」に怯えるのか

VBA標準の`Collection`オブジェクトは、極めて軽量だが、致命的な設計欠陥を抱えている。それは「キーの存在確認（Exists）メソッドが存在しない」ことだ。

キーをAddする際、既に存在するキーを渡せば、即座にランタイムエラー（457: このキーは既にこのコレクションのメンバです）が吐き出される。これを回避するには、泥臭いエラーハンドリング（On Error Resume Next）を挟む必要があるが、これは現代の堅牢なコード設計としては悪手だ。

‘ 【アンチパターン】泥臭いエラーハンドリング
On Error Resume Next
col.Add Item, Key
If Err.Number = 457 Then
‘ ここで値を更新する処理が必要だが、Collectionは値を直接書き換えられない
‘ 一度RemoveしてAddし直す必要がある（非効率の極み）
End If
On Error GoTo 0

このコードは、ループの深部で実行されるたびにスタックを汚染し、パフォーマンスを劇的に低下させる。数万件のデータ処理において、この設計は「時限爆弾」となる。

—

2. Dictionaryの真価：メモリと速度のトレードオフ

`Scripting.Dictionary`は、COMコンポーネントであり、内部的にはハッシュテーブルとして実装されている。この設計の最大の特徴は、「キーの存在確認（Exists）がO(1)の計算量で完結する」という点だ。

また、`Collection`と異なり、キーに対応する値を直接書き換えられる（`dict(key) = newValue`）。この単一の仕様が、大規模データ処理におけるメモリ管理とCPU負荷を劇的に改善する。

実践：Dictionaryによる高効率な一意性管理

Public Sub ManageUniqueData()
‘ 事前バインディングで参照設定(Microsoft Scripting Runtime)推奨
‘ パフォーマンスが向上し、IntelliSenseも効く
Dim dict As New Scripting.Dictionary

‘ データを追加・更新する際、エラーハンドリングは不要
If Not dict.Exists(“TargetKey”) Then
dict.Add “TargetKey”, “InitialValue”
Else
‘ 直接書き換え可能。これがCollectionとの最大の差
dict(“TargetKey”) = “UpdatedValue”
End If

‘ プロセス終了後のメモリ解放は必須。COMオブジェクトは自動解放を信用するな
Set dict = Nothing
End Sub

—

3. シニアアーキテクトが教える「使い分けの鉄則」

現場で迷ったとき、以下の基準を指標にしてほしい。

【深淵の知見】メモリ管理とライフサイクル

VBAのメモリ管理は参照カウンタ方式だ。特に`Dictionary`のようなCOMオブジェクトを大規模なループ内で生成し、解放し忘れると、メモリリークの温床となる。

スコープを最小化せよ: 可能な限りプロシージャ内で生成し、`Nothing`で明示的に解放する。
巨大なDictionaryの受け渡し: ByRefで渡し、メモリのコピー（インスタンスの複製）を避けよ。

—

4. 最後に：レガシー環境での共存

システム連携において、Excelファイルを中間媒体とする場合、`Dictionary`で集計した結果を高速にセルへ書き出すために、「二次元配列への一括転送」を組み合わせるのが鉄則だ。

セルを一つずつ叩くのは、VBAにおいて最も低俗な行為である。`Dictionary`でメモリ上にデータを構築し、最後に`Range(Cells).Value = Array`で一気に流し込む。この一連のフローこそが、数百万レコードを数秒で処理する「伝説的」なVBAの作法である。

技術は手段に過ぎない。しかし、その手段を極限まで研ぎ澄ますことが、我々エンジニアの矜持であるはずだ。次回の改修では、不要な`On Error Resume Next`を削ぎ落とし、`Dictionary`の静寂なパフォーマンスを手に入れてほしい。