【実務・中級編】コレクション（Collection）と辞書（Dictionary）の使い分け：データ検索を爆速にする – Excel VBA解析バイブル

ExcelVBA

2026.08.24

Excel VBAを掌握する極限の知見：Collectionか、Dictionaryか。データ検索の「最適解」を語る

業務自動化の現場において、処理速度の遅延は「悪」だ。数万行のデータをシート上でセルを一つずつループして検索する……そんな「セル参照の地獄」から卒業し、プロフェッショナルな設計を導入したいのであれば、今すぐメモリ上のデータ構造を再考すべきだ。

今回は、VBAにおける「Collection」と「Dictionary」の決定的な違いと、爆速検索を実現するための実装の指針を授ける。

—

1. なぜ「セル」を直接操作してはいけないのか

VBAの実行速度を劇的に低下させる最大の要因は、「VBAとExcelワークシート間の通信コスト」にある。セルを読み書きするたびに、Excelの描画エンジンや計算エンジンが介入する。これがボトルネックだ。

我々エンジニアがすべきことは、「一度メモリにデータをロードし、そこで完結させること」。そのための武器が「Collection」と「Dictionary」だ。

—

2. Collection vs Dictionary：明確な使い分けの哲学

結論から言うと、「検索」を目的とするならば、迷わず `Scripting.Dictionary` を選べ。

Collection：要素を追加する順序を保持したい、あるいは単なるキューとして扱う場合に適している。
Dictionary：「社員番号から名前を引く」「商品コードから価格を特定する」といった、キーと値のペアによる高速アクセスが必要な場合に真価を発揮する。

—

3. 爆速検索を実現するプロダクションコード

実務でそのまま使える、堅牢なデータ構造の雛形を提示する。エラーを握りつぶさず、かつ可読性を担保する書き方だ。

‘ 参照設定: Microsoft Scripting Runtime を推奨 (事前バインディング)
‘ 事前バインディングにすることで、IntelliSenseが効き、実行速度も微増する。

Public Sub FastLookupExample()
Dim dict As Object
Set dict = CreateObject(“Scripting.Dictionary”)

Dim dataRange As Variant
Dim i As Long

‘ 1. メモリへの一括読み込み (シート操作を最小限にするための鉄則)
dataRange = Sheet1.Range(“A2:B10000”).Value

‘ 2. Dictionaryへの構築
‘ キー: A列(商品コード), 値: B列(価格)
On Error Resume Next ‘ 重複キーによるエラー対策
For i = 1 To UBound(dataRange, 1)
If Not dict.Exists(dataRange(i, 1)) Then
dict.Add dataRange(i, 1), dataRange(i, 2)
End If
Next i
On Error GoTo 0

‘ 3. 高速検索の実践
Dim targetKey As String
targetKey = “PROD-999”

If dict.Exists(targetKey) Then
Debug.Print “検索結果: ” & dict(targetKey)
Else
Debug.Print “該当なし”
End If
End Sub

このコードの「設計の肝」

配列への転送: `Range.Value` を一度Variant配列に突っ込む。これだけで、セルへの直接アクセスと比較して100倍以上の速度差が出る。
事前判定: `dict.Exists` を使うことで、`On Error` を多用する汚いコードを回避する。これはコードの保守性において非常に重要だ。
メモリ解放: 巨大なデータを扱う場合は、処理の最後に `Set dict = Nothing` を明示する。VBAのガベージコレクションを信用しすぎてはいけない。

—

4. 現場で生き残るための「注意点」

ファイルやデータベースとの連携

外部データを読み込む際、データ型には細心の注意を払え。Excelのセルは数値と文字列の境界が曖昧だが、`Dictionary` のキーは厳密だ。数値の `1` と文字列の `”1″` は別物として扱われる。読み込み時に `CStr()` や `Val()` で型を統一する癖をつけろ。

保守性のためのカプセル化

複雑なシステムを作る場合、Dictionaryをグローバル変数で扱うのは避けるべきだ。クラスモジュールにDictionaryを隠蔽し、`GetItem(key)` や `AddItem(key, value)` といったメソッドのみを公開する「ラッパー」を作るのが、伝説的なエンジニアの流儀である。

—

最後に：なぜ「今」この知識が必要なのか

AIがコードを書く時代になっても、「なぜそのデータ構造を選ぶのか」という判断基準は、エンジニア自身の経験にしか宿らない。

「とりあえずセルのループで動いたからOK」という開発はやめろ。そのツールを使う人の時間を奪っているのは、あなた自身だ。メモリを制する者はVBAを制し、VBAを制する者は業務を制する。

さあ、その汚いループ処理を書き換え、爆速のエンジンに積み替えてこい。それが次のステージへの第一歩だ。