この記事でわかること
- VBAのCollectionにデータをAddで追加し、For Eachで取り出す基本操作ができる
- 重複を除いた一意リストをCollectionで作成してシートに出力できる
- Collection・Dictionary・配列の使い分けが分かる
対象: VBAを少し触ったことがある人。配列のReDim Preserveに苦戦している人。
所要時間: コピペ → 実行まで約5分
—
どんな場面で使う？
- 重複を除いた一意なリスト（部署名・カテゴリ名など）を動的に作りたい
- 配列のサイズを事前に決められないときに要素を動的に追加したい
- キーで値を管理して素早く取り出したい
- DictionaryやArrayとの使い分けを整理したい—
完成イメージ（Before / After）
Before（A列に重複データあり）:
| A列（商品名） |
|---|
| りんご |
| みかん |
| りんご |
| バナナ |
| みかん |
| ぶどう |
After（B列に重複なし一意リスト）:
| A列（商品名） | B列（一意リスト） |
|---|---|
| りんご | りんご |
| みかん | みかん |
| りんご | バナナ |
| バナナ | ぶどう |
| みかん | |
| ぶどう |
—
自分も以前、データ件数が毎回変わるリストを配列で管理していた。ReDim Preserveを何度も書くのが正直めんどくさかったし、件数を間違えるたびに「インデックスが有効範囲にありません」エラーが出て地味にストレスだった。Collectionを覚えてからは、件数を気にせずAddするだけで済むようになった。コードがシンプルになって、メンテナンスも格段に楽になった。配列のReDimで消耗している人が、この記事でCollectionに乗り換えてスッキリ書けるようになればうれしい。
Collectionを使えば、データ件数が変わるリストを「Addで追加、For Eachで取り出し」のシンプルなコードで管理できる。
Collectionとよく比較されるDictionaryについては Dictionaryで重複チェック・集計を高速化する方法 で詳しく解説している。この記事ではCollectionの基本操作に集中する。
—
実行前の準備
バックアップを取る
実務版コードはB列にデータを書き込む。必ずファイルのコピーを別フォルダに保存してから実行する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロが保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
—
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
Alt + F8→ マクロ名を選んで「実行」
ボタンに割り当てれば毎回Alt+F8を押さなくて済む。方法は マクロをボタン1つで実行する方法 を参照。
—
コード（最小版）– CollectionにAddして取り出す
A列のデータをCollectionに追加し、イミディエイトウィンドウに一覧表示する。まずはこれでCollectionの基本動作を確認する。
'============================================================
' ■ CollectionにAddして取り出す（最小版）
' → A列のデータをCollectionに追加し、一覧表示
' → 貼り付け場所: 標準モジュール
'============================================================
Sub CollectionBasic()
Dim col As New Collection
'--- A列のデータをCollectionに追加
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim r As Long
For r = 2 To lastRow '← 1行目はヘッダー想定
col.Add ws.Cells(r, 1).Value
Next r
'--- Collectionの中身を取り出して表示
Dim item As Variant
For Each item In col
Debug.Print item
Next item
MsgBox col.Count & " 件のデータをCollectionに追加しました。" & vbCrLf & _
"イミディエイトウィンドウ（Ctrl+G）で一覧を確認できます。", vbInformation
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
For r = 2 |
データの開始行 | 2（1行目はヘッダー） |
ws.Cells(r, 1) |
データが入っている列 | 1（A列） |
ポイント: Dim col As New Collection で宣言と同時にインスタンスが作られる。配列のようにサイズを指定する必要がない。col.Add を呼ぶだけでデータが追加され、Collectionが自動的にサイズを管理する。
ループの基本については Do While/Do Untilで条件付きループを回す方法 も参考になる。
—
コード（実務版）– 重複を除いた一意リストをシートに出力
A列のデータからCollectionを使って重複を除外し、一意リストをB列に出力する。
この方法を覚えてからは、一意リストの作成をCollectionでサッとやるようになった。Dictionaryほど大げさにしたくない場面で重宝している。同僚に「このリスト、重複除いて」と頼まれたときにも、すぐ対応できるようになった。
※ B列の既存データは上書きされます。実行前にバックアップを取ってください。
'============================================================
' ■ 重複を除いた一意リストをB列に出力（実務版）
' → A列のデータからCollectionで重複除外
' → B列に一意リストを書き込み
' → 対象シート: アクティブシート
' → 貼り付け場所: 標準モジュール
'============================================================
Sub CollectionUniqueList()
'--- ★書き換えポイント ---
Dim startRow As Long
startRow = 2 '← データの開始行（1行目はヘッダー）
Dim srcCol As Long
srcCol = 1 '← 元データの列（A列=1）
Dim dstCol As Long
dstCol = 2 '← 一意リストの出力列（B列=2）
'--- ★ここまで ---
Dim ws As Worksheet
Set ws = ActiveSheet
'--- 画面更新を止めて高速化
Application.ScreenUpdating = False
'--- エラー処理の設定
On Error GoTo ErrHandler
'--- 最終行を取得
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, srcCol).End(xlUp).Row
If lastRow < startRow Then
MsgBox "データがありません。A列にデータを入力してから実行してください。", vbExclamation
GoTo Cleanup
End If
'--- Collectionに重複を除いてデータを追加
Dim col As New Collection
Dim cellValue As String
Dim r As Long
For r = startRow To lastRow
cellValue = Trim(CStr(ws.Cells(r, srcCol).Value))
If cellValue <> "" Then
'--- キーに値そのものを使い、重複時のエラーをスキップ
On Error Resume Next
col.Add cellValue, cellValue
On Error GoTo ErrHandler
End If
Next r
'--- B列のヘッダーを書き込み
ws.Cells(1, dstCol).Value = "一意リスト"
'--- B列の既存データをクリア（ヘッダー行の下から）
Dim clearLastRow As Long
clearLastRow = ws.Cells(ws.Rows.Count, dstCol).End(xlUp).Row
If clearLastRow >= startRow Then
ws.Range(ws.Cells(startRow, dstCol), ws.Cells(clearLastRow, dstCol)).ClearContents
End If
'--- Collectionの中身をB列に出力
Dim i As Long
For i = 1 To col.Count
ws.Cells(startRow + i - 1, dstCol).Value = col(i)
Next i
MsgBox "完了: " & col.Count & " 件の一意データをB列に出力しました。" & vbCrLf & _
"（元データ: " & (lastRow - startRow + 1) & " 行）", vbInformation
Cleanup:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox "エラーが発生しました。" & vbCrLf & _
"エラー番号: " & Err.Number & vbCrLf & _
"内容: " & Err.Description, vbCritical
Resume Cleanup
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
startRow |
データの開始行 | 2（1行目はヘッダー） |
srcCol |
元データの列 | 1（A列） |
dstCol |
一意リストの出力列 | 2（B列） |
重複除外の仕組み
On Error Resume Next
col.Add cellValue, cellValue '← 第2引数がキー
On Error GoTo ErrHandler
Collectionの第2引数（キー）は一意でなければならない。同じキーで2回目のAddを試みるとエラー457が発生する。On Error Resume Next でこのエラーをスキップすることで、重複データを自然に除外している。
エラー処理の基本については エラー処理（On Error）で止まらないマクロを作る方法 を参照。
配列を使った高速処理については 配列を使ってVBAの処理速度を10倍にする方法 を参照。大量データ（数万行以上）の場合は配列の方が速い。
—
よくある落とし穴5選
1. ループ中にRemoveするとインデックスがズレる
自分もこれで30分溶かした。For i = 1 To col.Countで回しながらcol.Remove iしたら、想定と違うアイテムが消えていた。原因は、Removeした瞬間にインデックスが詰まること。iが進んでいるのにCollectionの方が縮んでいて、噛み合わなくなる。
原因: Removeするとインデックスが詰められ、Countも減る。しかしForのカウンタは進み続ける。
対策: 後ろから逆順でRemoveする。
' NG: 前から順にRemoveするとインデックスがズレる
For i = 1 To col.Count
If col(i) = "削除対象" Then col.Remove i
Next i
' OK: 後ろから逆順でRemove
Dim i As Long
For i = col.Count To 1 Step -1
If col(i) = "削除対象" Then col.Remove i
Next i
2. キー重複でエラー457が出る
原因: col.Add "値", "キー" の第2引数（キー）は一意でなければならない。同じキーで2回Addすると実行時エラー457「このキーは既にこのコレクションの要素に割り当てられています」が発生する。
対策: 重複除外が目的なら On Error Resume Next でスキップする（実務版コードの手法）。エラーをスキップしたくない場合は、事前にキーの存在を確認する関数を作る。
3. キーの一覧を取得しようとしてもできない
原因: CollectionにはDictionaryのようなKeysメソッドがない。追加したキーの一覧を後から取り出すことはできない。
対策: キーの一覧が必要ならDictionaryを使う。詳しくは 重複データを一括削除して一意のリストを作る方法 を参照。
4. インデックスが1始まりで0を指定するとエラーになる
原因: Collectionのインデックスは1始まり。配列のLBound（既定は0）とは異なる。col(0) を指定すると「インデックスが有効範囲にありません」エラーが出る。
対策: For i = 1 To col.Count で回す。配列を普段使っている人は0始まりの癖に注意。
5. Set col = Nothingした後にAddしてエラーになる
原因: Set col = Nothing でCollectionオブジェクトが破棄される。その後に col.Add を呼ぶと「オブジェクト変数またはWithブロック変数が設定されていません」エラーが出る。
対策: Collectionを空にして再利用したい場合は Set col = New Collection で新しいインスタンスを作る。Nothingは「破棄」であって「初期化」ではない。
| # | 症状 | 原因 | 対策 |
|---|---|---|---|
| 1 | ループ中のRemoveで意図しないアイテムが消える | Remove後にインデックスが詰まる | 後ろから逆順でRemove |
| 2 | エラー457「このキーは既に〜」 | 同じキーで2回Add | On Error Resume Nextでスキップ |
| 3 | キーの一覧を取得できない | CollectionにKeysメソッドがない | Dictionaryを使う |
| 4 | 「インデックスが有効範囲にありません」 | インデックス0を指定 | 1始まりでアクセス |
| 5 | 「オブジェクト変数が設定されていません」 | Nothing後にAdd | Set col = New Collectionで再生成 |
—
VBAのCollectionでキー重複エラー457が出るときの対処法
「Collection.Addで同じキーを追加したらエラー457が出る」という場合、原因はCollectionが同一キーの重複を許可しないためだ。重複除外の一意リストを作るときは、On Error Resume Nextでエラーを無視してAddし続ける方法が定番パターン。
VBAのCollectionでループ中にRemoveしたらインデックスがズレるときの対処法
「For iループでRemoveしたら要素がスキップされる」という場合、原因はRemoveで要素が詰まってインデックスがずれるためだ。末尾から逆順にループする（For i = col.Count To 1 Step -1）ことでインデックスのズレを防げる。
FAQ
Q1: Collection・Dictionary・配列はどう使い分ける？
| 観点 | Collection | Dictionary | 配列 |
|---|---|---|---|
| 参照設定 | 不要（標準機能） | CreateObject or 参照設定 | 不要 |
| サイズ管理 | 自動（Add/Remove） | 自動（Add/Remove） | 手動（ReDim Preserve） |
| キーでアクセス | 可（文字列のみ） | 可（文字列/数値） | 不可 |
| キー一覧の取得 | 不可 | 可（Keys） | – |
| キーの存在確認 | エラーで判定 | Exists | – |
| 速度（大量データ） | 遅め | 速い | 最速 |
| 向いている場面 | 順番に追加・取り出すリスト | 重複チェック・集計・検索 | 大量データの一括処理 |
単純なリスト管理ならCollection、キーと値のペアで管理・検索するならDictionary、大量データの高速処理なら配列。
Q2: For EachとFor i、どちらでループする？
- For Each: シンプルで読みやすい。取り出すだけならこちらが推奨
- For i = 1 To col.Count: インデックスでアクセスが必要な場合（途中のRemove、特定位置の取得など）
' For Each（推奨）
Dim item As Variant
For Each item In col
Debug.Print item
Next item
' For i（インデックスが必要な場合）
Dim i As Long
For i = 1 To col.Count
Debug.Print col(i)
Next i
Q3: Collectionにオブジェクト（ワークシートなど）も入れられる？
入れられる。ただしオブジェクトの場合は取り出し時の扱いに注意。
Dim col As New Collection
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
col.Add ws
Next ws
' 取り出し
Dim item As Variant
For Each item In col
Debug.Print item.Name '← シート名を表示
Next item
Q4: Collectionのキーは数値でも使える？
キーには文字列型のみ指定可能。数値を渡すと型の不一致でエラーになる。数値をキーにしたい場合は CStr() で文字列に変換する。
col.Add "値", CStr(12345) '← 数値を文字列に変換してキーに使う
Q5: Collectionは何件まで入る？
メモリの許す限り。一般的な実務データ（数千〜数万件）なら問題ない。ただし、数万件以上で処理速度が気になる場合は配列やDictionaryの方が速い。自分の経験では、1万件程度のリストならCollectionでも十分実用的だった。
—
まとめ
- Collection.Add: 件数を気にせずデータを追加できる（ReDim不要）
- For Each: Collectionの中身をシンプルに取り出せる
- キー付きAdd + On Error Resume Next: 重複を除外した一意リストを作れる
- Collection.Remove: インデックス指定で削除。ループ中は逆順で
- 使い分け: 単純なリスト→Collection、キー検索・集計→Dictionary、大量データ高速処理→配列
関連記事
- Dictionaryで重複チェック・集計を高速化する方法 — キーと値のペアで管理したい場合
- 配列を使ってVBAの処理速度を10倍にする方法 — 大量データの高速処理
- 重複データを一括削除して一意のリストを作る方法 — Dictionary版の重複削除
- エラー処理（On Error）で止まらないマクロを作る方法 — On Error Resume Nextの使い方
- Do While/Do Untilで条件付きループを回す方法 — ループの基本
—
次にやりたくなること
- Dictionaryで重複チェック・集計を高速化する方法: Collectionよりも高機能なデータ管理が必要な場合。キーの一覧取得、Existsメソッドでの存在確認ができる
- 配列を使ってVBAの処理速度を10倍にする方法: 大量データ（数万行以上）を高速に処理したい場合。セル範囲との一括入出力が可能
- Enumで定数グループを定義してコードを読みやすくする方法: CollectionのキーにEnum定数を使ってデータ管理を型安全にしたい場合
コメント