VBAのデータコンテナを配列Collection辞書ArrayListで整理する

データ構造を先に決めると、VBAの処理は機能の寄せ集めにならない

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

記事要約

Excel VBAで処理が複雑になる原因の多くは、機能が足りないことではなく、データの持ち方が決まっていないことです。複数の表を後から統合しようとして、串刺し計算、統合機能、複雑な関数、場当たり的なループで何とかしようとすると、列ずれ、項目ずれ、重複、集計漏れが起きやすくなります。

プログラムも同じです。アプリケーションはデータ構造が命です。処理や画面やボタンを先に作るのではなく、何を1件のデータとみなし、どの単位で並べ、どのキーで引き、どの順序で処理するかを先に決める必要があります。

この記事では、VBAでデータ構造を作る基本部品として、配列、Collection、Dictionary、ArrayListを整理します。特に、配列を中心に、CollectionでStackやQueueを作り、Dictionaryでキー検索を行い、ArrayListは使いどころを見極める、という実務的な判断軸を示します。

本記事の対象とゴール

想定読者

• VBAで配列、Collection、Dictionaryを何となく使っている人

• Excel表の統合や集計を、関数やループの工夫だけで何とかしようとしている人

• Stack、Queue、List、Dictionaryの違いをVBAの実務で使える形に整理したい人

• ArrayListを使うべきか、配列やCollectionで足りるのか判断したい人

本記事で得られること

1. VBAでデータコンテナを選ぶときの基準が分かります。

2. 配列、Collection、Dictionary、ArrayListの役割を分けて考えられます。

3. 小さな配列操作ライブラリを作る意味が分かります。

4. StackやQueueをCollectionで実装する発想が分かります。

5. ArrayListの便利さと、VBAとの相性の悪さを分けて判断できます。

本記事で扱わないこと

• 高度なアルゴリズム全般

• 大量データ処理の厳密な速度比較

• .NETのジェネリックコレクションをVBAから本格利用する設計

• 外部ライブラリとして完成済みのデータ構造を全面的に導入する手順

先に結論

VBAで最初に身につけるべきデータコンテナは、配列、Collection、Dictionaryの3つです。

配列は、Excelシートやワークシート関数との相性がよく、VBAのデータ処理の中心になります。Collectionは、順序付きの要素群を扱いやすく、StackやQueueの土台になります。Dictionaryは、キーで値を引くための道具であり、配列やCollectionでは代替しにくい強みがあります。

ArrayListは便利な機能を持っていますが、VBAの基本コンテナとして必須ではありません。Sort、Reverse、任意位置へのInsert/Remove、ToArrayなどは魅力ですが、環境依存やVBAからの使いにくさがあります。多くのVBA実務では、配列、Collection、Dictionaryを理解したうえで、必要に応じてArrayListを足す、という順序が現実的です。

第1章 データベースファーストとプログラムのデータ構造は同じ話である

Excelで複数の表を集めて集計する作業では、後からまとめようとするほど苦しくなります。表の形が部署ごとに違う、項目名が違う、列の意味がずれる、同じ項目が別名で存在する。この状態で統合機能や串刺し計算を使うと、見た目は集計できていても、データの意味が崩れていることがあります。

この問題は、VBAでも同じです。

処理を先に作り、データの形を後から合わせようとすると、コードは例外処理だらけになります。条件分岐が増え、変数名が増え、途中結果の意味が分からなくなります。

先に決めるべきなのは、次の問いです。

6. 何を1件のデータとみなすのか。

7. そのデータを順序で扱うのか、キーで扱うのか。

8. 途中で追加、削除、並べ替えが必要なのか。

9. シートへ戻すとき、二次元配列にできるのか。

10. 後続処理は、何を入力として受け取れば自然なのか。

この問いに答えるための部品が、配列、Collection、Dictionary、ArrayListです。

第2章 VBAの基本データコンテナ

VBAで最初に整理するべきデータコンテナは、次の4つです。

1つ目は配列です。

配列は、同じ種類の値を順番に並べる基本構造です。Excelシートの範囲を一括で読み込むと、多くの場合は二次元配列として扱えます。ワークシート関数や組み込み関数の戻り値も、配列として出てくることがあります。

2つ目はCollectionです。

Collectionは、順序を持つ要素群を1つの単位として扱うためのオブジェクトです。Microsoft Learnでは、Collectionを「1つの単位として参照できる順序付きの項目集合」と説明しています。要素の追加、削除、Itemによる取得、For Eachによる列挙ができます。

3つ目はDictionaryです。

Dictionaryは、キーと値の組を保持します。Microsoft Learnでは、Dictionaryを「key/item pairs」を格納するオブジェクトとして説明しています。ある値をキーで一発で取り出したい場合、Dictionaryは配列やCollectionより自然です。

4つ目はArrayListです。

ArrayListは.NETの System.Collections.ArrayList です。Microsoft Learnでは、ArrayListを「IListインターフェイスを配列で実装し、必要に応じてサイズを動的に増やすクラス」と説明しています。VBAから使う場合は、便利なList系コンテナとして扱えますが、VBAに最初から備わった型ではありません。

第3章 まず配列を理解する

VBAでは、配列の理解が最重要です。

理由は、Excelシートとの値の受け渡しが配列中心になるからです。シートの範囲を一括でVariantに読み込むと、行と列を持つ二次元配列として扱えます。セルを1つずつ読むより、配列でまとめて処理したほうが速く、コードの見通しもよくなります。

たとえば、シート範囲を配列へ読み込む基本形は次のようになります。

Dim values As Variant

values = Sheet1.Range("A1").CurrentRegion.Value

この values は、行番号と列番号でアクセスできます。

Dim r As Long

For r = 2 To UBound(values, 1)

    Debug.Print values(r, 1), values(r, 2)

Next r

配列が分かると、シートを直接触る回数を減らせます。シートは入出力の場所、配列は処理の場所、と分けられるようになります。

第4章 小さな配列操作ライブラリを作る

VBAの配列は便利ですが、そのままだと扱いにくい部分があります。

要素を追加したい、配列同士を結合したい、要素数を知りたい、下限をそろえたい、といった処理を毎回手で書くと、細かなバグが入りやすくなります。

最初に作る配列操作関数は、次のようなもので十分です。

11. 配列に要素を追加する。

12. 配列に別の配列の要素を展開して追加する。

13. 配列同士をマージする。

14. 配列の要素数を取得する。

15. 配列の下限をそろえる。

たとえば、一次元配列の要素数は、空配列の扱いまで考えると関数にしておく価値があります。

Public Function ArrayCount(ByVal values As Variant) As Long

    On Error GoTo empty00

    ArrayCount = UBound(values) - LBound(values) + 1

    Exit Function

empty00:

    ArrayCount = 0

End Function

要素追加も、関数にしておくと呼び出し側が読みやすくなります。

Public Function ArrayAppend(ByVal values As Variant, ByVal item As Variant) As Variant

    Dim count As Long

    count = ArrayCount(values)

    Dim result() As Variant

    ReDim result(0 To count)

    Dim i As Long

    For i = 0 To count - 1

        result(i) = values(LBound(values) + i)

    Next i

    result(count) = item

    ArrayAppend = result

End Function

このような小さな関数を作る目的は、立派なライブラリを作ることではありません。

配列の境界、下限、要素数、コピー、追加の感覚を身につけることです。配列操作ライブラリを作ると、VBAの値渡し、Variant、配列の戻り値、エラー処理も自然に学べます。

第5章 CollectionはStackとQueueの土台になる

Collectionは、順序を持つ要素群を扱うための標準的な部品です。

Collectionの分かりやすい用途は、StackとQueueです。

Stackは、最後に入れたものを先に取り出す構造です。処理待ち、戻り先、階層探索などで使います。

Public Sub StackSample()

    Dim stack As New Collection

    stack.Add "A"

    stack.Add "B"

    stack.Add "C"

    Debug.Print stack.Item(stack.Count)

    stack.Remove stack.Count

End Sub

Queueは、先に入れたものを先に取り出す構造です。順番待ち、幅優先探索、処理キューなどで使います。

Public Sub QueueSample()

    Dim queue As New Collection

    queue.Add "A"

    queue.Add "B"

    queue.Add "C"

    Debug.Print queue.Item(1)

    queue.Remove 1

End Sub

実務では、このまま直接Collectionを使うより、Push、Pop、Enqueue、Dequeue のような関数やクラスで包むと、呼び出し側の意味が明確になります。

Collectionは、配列よりも追加と削除を書きやすい場面があります。一方で、シートへの一括出力やワークシート関数との連携では、配列のほうが自然です。つまり、Collectionは処理中の一時的な順序付きコンテナ、配列はExcelとの入出力やまとまった値の処理、と分けると扱いやすくなります。

第6章 Dictionaryはキーで引くための部品である

Dictionaryの強みは、キーで値を取り出せることです。

配列やCollectionでも検索はできます。しかし、毎回ループして探す必要があります。キーが分かっているなら、Dictionaryに入れておくほうが自然です。

Public Sub DictionarySample()

    Dim map As Object

    Set map = CreateObject("Scripting.Dictionary")

    map.Add "A001", "山田"

    map.Add "A002", "佐藤"

    If map.Exists("A001") Then

        Debug.Print map.Item("A001")

    End If

End Sub

Dictionaryを使うべき場面は、次のような場合です。

16. 社員コードから氏名を引く。

17. 商品コードから単価を引く。

18. 取引先コードから集計行を引く。

19. 既に出現した値かどうかを判定する。

20. キーごとに件数や金額を集計する。

Dictionaryは、単に便利な箱ではありません。

「この処理は何をキーにしているのか」をコード上で明示する道具です。キーが明確になると、集計、突合、重複判定、差分確認が安定します。

第7章 ArrayListは便利だがVBAの基本部品ではない

ArrayListは、Listとしての操作を持つ便利なコンテナです。

Microsoft LearnのArrayListには、Add、AddRange、Count、Item、Insert、Remove、Sort、Reverse、ToArrayなど、多くの操作が用意されています。投稿中でも、Sortや任意位置への追加・削除ができる点が話題になっています。

ただし、VBAでArrayListを基本部品にするには注意が必要です。

1つ目は、VBAの配列からArrayListへ一括変換しにくいことです。

ArrayListから配列へは ToArray が使えます。一方で、VBAの配列をArrayListに入れる場合は、基本的にはループしてAddすることになります。

Public Function ToArrayList(ByVal values As Variant) As Object

    Dim list As Object

    Set list = CreateObject("System.Collections.ArrayList")

    Dim i As Long

    For i = LBound(values) To UBound(values)

        list.Add values(i)

    Next i

    Set ToArrayList = list

End Function

2つ目は、環境依存です。

ArrayListはVBAの組み込みコンテナではなく、.NET側のクラスです。環境によって使えない、参照設定やCreateObjectの扱いで差が出る、という問題が起きる可能性があります。

3つ目は、VBAとの相性です。

ArrayListのSortに比較器を渡すような使い方は、.NET側では自然でも、VBAでは比較用クラスを用意する必要があり、関数をそのまま渡せません。参照Gistでも、IComparerを実装する比較用クラスや、比較用メソッド名を文字列で渡す工夫が示されていました。

つまり、ArrayListは便利ですが、VBAの基本学習では、まず配列、Collection、Dictionaryを押さえるほうが先です。

第8章 自作ArrayListから分かるデータコンテナの要件

参照Gistには、自作ArrayListの実装例もありました。

そのコードでは、内部配列、要素数、容量を持ち、Add、AddFromArray、Count、Capacity、Item、Remove、Insert、ToArray、ToCollection、Sortなどを実装しています。

ここから分かるのは、データコンテナに必要な要素です。

21. 内部にデータを保存する領域がある。

22. 現在の要素数を持っている。

23. 容量が足りなくなったら拡張する。

24. 要素を追加できる。

25. 要素を指定位置から取得できる。

26. 要素を削除できる。

27. 必要なら配列やCollectionへ変換できる。

28. For Eachで回せるように列挙方法を用意できる。

特に、Addの内部では、容量が足りなくなったら配列を拡張する考え方が出てきます。

If m_length + 1 >= m_capacity Then

    m_capacity = m_capacity * 2

    ReDim Preserve m_arr(0 To m_capacity - 1)

End If

この処理は、ArrayListが単なる便利関数の集まりではなく、「内部に配列を持ち、必要に応じて伸ばすコンテナ」であることを示しています。

自作ArrayListをそのまま使うかどうかは別問題です。しかし、こうした実装を見ると、配列、Collection、Dictionary、ArrayListの違いが、単なる名前の違いではなく、内部構造と操作の違いであることが分かります。

第9章 データコンテナはアルゴリズムの前に学ぶ

アルゴリズムを学ぶ前に、データコンテナを学ぶ必要があります。

理由は、アルゴリズムはデータの持ち方に依存するからです。

同じ検索でも、配列を先頭から探すのか、Dictionaryでキー検索するのかで、処理の考え方が変わります。同じ追加でも、配列をReDim Preserveするのか、CollectionにAddするのか、ArrayListにAddするのかで、コードの形が変わります。

知識がないまま工夫すると、時間を使って複雑なコードを作るだけになります。

まず、次の判断をできるようにします。

29. シートからまとめて読むなら配列。

30. 順序付きの作業リストならCollection。

31. キーで引くならDictionary。

32. Sortや任意位置操作が必要で、環境条件を許容できるならArrayList。

33. それでも足りないなら、自作クラスで意味のあるコンテナを作る。

この順序で考えると、機能からコードを作るのではなく、データからコードを作れるようになります。

第10章 実務での選び方

実務では、次のように選ぶと迷いが減ります。

Excel表を読み込んで加工し、また表へ戻すなら、配列を中心にします。

処理待ち、親子探索、Stack、Queueのように順序だけが重要なら、Collectionを使います。

コード、ID、日付、分類名などで対応表を作るなら、Dictionaryを使います。

ArrayListは、並べ替え、任意位置挿入、任意位置削除などListとしての操作が欲しいときに検討します。ただし、VBAの配列やCollectionで十分なら、無理にArrayListを導入する必要はありません。

大事なのは、どれが高級かではありません。

その処理で、データをどの形で持つのが自然かです。

第11章 まとめ

VBAでは、データ構造を先に決めることが重要です。

処理を機能で押し切ると、コードは複雑になります。データの形、順序、キー、入出力先を先に決めれば、必要な処理は自然に決まります。

基本は、配列、Collection、Dictionaryです。

配列はExcelとの入出力とまとまった値の処理に向いています。Collectionは順序付きの要素群、Stack、Queueに向いています。Dictionaryはキーで引く処理に向いています。

ArrayListは、VBAでも使える便利なデータコンテナです。しかし、環境依存やVBAとの相性を考えると、最初に学ぶ中心ではありません。配列、Collection、Dictionaryを理解したうえで、必要なときに使う部品です。

データコンテナを学ぶことは、単に道具を増やすことではありません。

VBAで「何をどう持つか」を決める力を持つことです。その力があれば、関数、ループ、集計、検索、変換のコードは、ずっと整理しやすくなります。

出典メモ

• 元Togetter URL: https://togetter.com/li/1832274

• 正規Posfie URL: https://posfie.com/@hoehoe1234/p/p6ClioV

• 取得日: 2026-06-11

• 主な素材投稿者: @sugoi_kaizen、@hoehoe1234、@furyutei、@eternal_xlsm、@yamato_1413

• 参照Gist: https://gist.github.com/furyutei/7e6b716519912d189001948e93e4926c

• 参照Gist: https://gist.github.com/yamato1413/a69f6496bb2d47fc555c10fcdfd5d5da

• Microsoft Learn ArrayList: https://learn.microsoft.com/en-us/dotnet/api/system.collections.arraylist

• Microsoft Learn Dictionary object: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object

• Microsoft Learn Collection object: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object

• 画像確認: Posfie/Togetter HTML内の直接画像は0件。X投稿2件をMCPブラウザーで公開表示確認したが、本文とUI/プロフィール画像のみで、記事素材になるコード画像・画面画像は確認できなかった。Firefox指定でのNode/Playwright起動はサンドボックス初期化エラーで実行できなかったため、利用可能なPlaywright MCPで確認した。

• 本文への反映: 投稿本文は長文転載せず、データコンテナ選択の判断軸として再構成した。Gistの長いコードは全文転載せず、Add、Count、Item、Insert、ToArray、Sortなど、データコンテナの要件説明に必要な範囲だけを概略化した。