Excel VBAコレクション

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

masahiro yoshida

Last updated on 6月 8, 2022

In this Article

VBAのコレクションとは？
- コレクションと配列の比較
Collectionオブジェクトのスコープ
コレクションを作成し、アイテムを追加し、アイテムにアクセスする

このチュートリアルでは、VBAでコレクションを使用する方法について説明します。

VBAのコレクションとは？

コレクションとは、類似した項目を多数保持するオブジェクトで、コレクション内に多数の項目がある場合でも、簡単にアクセスして操作することができます。

独自のコレクションを作成することもできますが、VBAには、例えばワークブック内のすべてのシートが格納されるSheetsコレクションのような、組み込みコレクションも用意されています。For Eachループを使用すれば、 Sheetsコレクション内の各ワークシートを繰り返し処理することができます。

Sub TestWorksheets()
Dim Sh As Worksheet
For Each Sh In Sheets
    MsgBox Sh.Name
    MsgBox Sh.Visible
Next Sh
End Sub

インデックス値（コレクション内の位置）、またはワークシートの実際の名前を使用して、コレクション内の特定のワークシートを指定することもできます。

MsgBox Sheets(1).Name
MsgBox Sheets("Sheet1").Name

ワークシートが追加または削除されると、Sheetsコレクションはサイズが大きくなったり小さくなったりします。

VBAのコレクションでは、インデックス番号は0でなく1から始まることに注意してください。

コレクションと配列の比較

配列とコレクションはどちらもデータを格納することができますが、いくつかの大きな違いがあります。

配列は多次元であるのに対し、コレクションは1次元です。

Dim MyArray(10, 2) As String

このコードは、ワークシートのように、10行2列の配列を作成します。コレクションは、実質的に1列のデータです。

配列に値を格納する際には、配列の各要素に値を格納するためのコードが必要です。例えば、2次元の配列の場合、1列目と2列目にそれぞれ値を入れるために2行のコードが必要になります。
コレクションでは、Add メソッドを使用することで、新しい項目がコレクションに追加され、インデックスの値が自動的に調整されます。
配列からデータを削除する場合、より複雑な処理が必要になります。要素の値を空白にすることはできますが、要素自体はまだ配列の中に存在します。 For Next ループで配列を繰り返し処理する場合、ループは空白の値を返すので、空白の値が無視されるようにコーディングする必要があります。
コレクションでは、Add や Remove メソッドを使用すると、すべてのインデックス作成とサイズ変更が自動的に行われます。削除された項目は完全に消えます。配列は固定サイズのデータに対して有効ですが、コレクションはデータ量が変化しやすい場合に有効です。
配列の値はVBAで変更できますが、コレクションは読み取り専用です。コレクションで値を変更する場合、まず変更する値を削除してから、新しい変更値を追加する必要があります。
配列の場合、要素に使用できるデータ型は1つだけで、それは配列を宣言した時点で決まります。しかし、配列の値そのものを工夫し、自分で設計したカスタムデータ型を使用することはできます。カスタムデータ型を使用して非常に複雑な配列構造を作成し、その下にいくつかのカスタムデータ型を配置することができます。
コレクションでは、各項目で使用するデータ型を追加することができます。数値、日付、文字列など、コレクションはどのようなデータ型でも受け取ることができます。もし、数値として宣言されている配列に文字列の値を入れようとすると、エラーメッセージが表示されるでしょう。
コレクションは、一般に配列よりも使いやすいとされています。コーディングの面でも、コレクションは2つのメソッド（AddとRemove）と2つのプロパティ（CountとItem）を持つだけなので、決してプログラミングを複雑にするわけではありません。
コレクションは、キーを使ってデータを探すことができます。配列にはこの機能がなく、特定の値を見つけるために配列内を反復するループコードが必要です。
配列のサイズは、最初に作成するときに定義しておく必要があります。どれくらいのデータを格納するのか、見当をつけておく必要があります。配列のサイズを大きくしたい場合は ReDim を使用してサイズを変更しますが、すでに配列に格納されているデータを失いたくない場合は Preserve というキーワードを使用する必要があります。
コレクションでは、サイズは定義する必要がありません。アイテムが追加されたり削除されたりすると、自動的に大きくなったり小さくなったりするだけです。

Collectionオブジェクトのスコープ

スコープに関して、Collectionオブジェクトは、ワークブックが開いている間のみ利用可能です。ワークブックが保存しても、コレクションは保存されません。ワークブックが再び開かれた場合、コレクションはVBAコードを使用して再作成されます。コレクションをコードモジュール内のすべてのコードで使用できるようにしたい場合、モジュールウィンドウの上部にある Declare セクションでCollectionオブジェクトを宣言する必要があります。

PIC 01 new

これにより、そのモジュール内のすべてのコードで、コレクションにアクセスできるようになります。もし、ワークブック内のどのモジュールでもコレクションにアクセスできるようにしたい場合は、グローバルオブジェクトとして定義してください。

Global MyCollection As New Collection

コレクションを作成し、アイテムを追加し、アイテムにアクセスする

単純なCollectionオブジェクトは、VBAで次のコードを使用して作成できます。

Sub CreateCollection()
'コレクションを作成する
Dim MyCollection As New Collection

'コレクションにアイテムを追加
MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"
End Sub

このコードでは、「MyCollection」という新しいオブジェクトを作成し、次のコード行でAddメソッドを使用して3つの新しい値を追加しています。

コレクションをループする

次に、コレクション内の各項目をループで処理できます。

For Each Item In MyCollection
    MsgBox Item
Next Item

また、次のコードでは、.Countでコレクションのサイズを取得し、各インデックス番号でループしています。

For n = 1 To MyCollection.Count
    MsgBox MyCollection(n)
Next n

最初のFor Eachループは、2番目のFor Nextループよりも高速ですが、一方向（低いインデックスから高いインデックス）しか動作しません。For Nextループは、別の方向（高インデックスから低インデックス）を使用できることと、Stepメソッドを使用して増分を変更できることが利点です。これは、複数のアイテムを削除する場合に便利です。削除が行われるとインデックスが変化するため、コレクションの最後から最初へ削除を実行する必要があるからです。

コレクションへのアイテムの追加

コレクションへのAddメソッドには、Key、Before、Afterの3つのオプションのパラメータがあります。

BeforeまたはAfterパラメータを使用すると、コレクション内に新しいアイテムを追加するときに、他のアイテムに対する位置を指定することができます。

これは、新しいアイテムの位置を相対的なインデックス番号で指定します。

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", , 1
MyCollection.Add "Item3"

End Sub

この例では、Item2 はコレクション内の最初のインデックス付きアイテム (これは Item1) の前に追加されるように指定されています。このコレクションを繰り返し処理すると、最初に Item2 が表示され、次に Item1 と Item3 が表示されます。

Before や After パラメータを指定すると、コレクション内のインデックス値が自動的に調整され、 Item2 がインデックス値 1 になり、 Item1 がインデックス値 2 に移動します。 Keyパラメータを使用すると、コレクションアイテムを特定するための参照値を追加することもできます。キーは文字列で、コレクション内で一意でなければならないことに注意しましょう。

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", "MyKey"
MyCollection.Add "Item3"

MsgBox MyCollection("MyKey")
End Sub

Item2にはMyKeyというキーの値が与えられているので、インデックス番号の2ではなくMyKeyの値を使ってその項目を参照することができます。

キー値は文字列でなければならないことに注意してください。他のデータ型は使用できません。コレクションは Read Only であり、一度設定された Key の値を更新することはできないことに注意してください。また、コレクション内の特定の項目にキー値が存在するかどうかを確認したり、キー値を表示したりすることができないのが少し難点です。

キーを使うと、コードが読みやすくなるという利点もあります。特に、サポートする同僚に渡す場合は、その値を見つけるためにコレクション全体を繰り返し処理する必要がありません。もし10,000ものアイテムからなるコレクションがあったとして、特定のアイテムを参照するのがどれだけ大変なことか想像してみてください。

コレクションからアイテムを削除する

コレクションから項目を削除するには、Removeメソッドを使用します。

MyCollection.Remove (2)

しかし、コレクションに含まれるアイテムの数が多い場合、削除したいアイテムのインデックスを調べるのは簡単ではありません。そこで、コレクションを作成するときに Key パラメータを使用すると便利です。

MyCollection.Remove ("MyKey")

コレクションから項目を削除すると、コレクション内のすべてのインデックスが自動的にリセットされます。そこで、一度に複数の項目を削除する場合にキーが非常に役に立ちます。たとえば、項目インデックス 105 を削除すると、瞬時に項目インデックス 106 はインデックス 105 となり、この項目より上の項目はすべてインデックス値が下がることになります。キーを使用すれば、どのインデックス値を削除すればいいのか悩む必要はありません。コレクションをすべて削除して新しいコレクションを作成するには、再びDimステートメントを使用して、空のコレクションを作成します。

Dim MyCollection As New Collection

実際のコレクションオブジェクトを完全に削除するには、オブジェクトをNothingに設定します。

Set MyCollection = Nothing

これは、コレクションがコードで不要になった場合に便利です。コレクションオブジェクトをNothingに設定すると、そのオブジェクトへの参照がすべて削除され、使用していたメモリが解放されます。これはコードの実行速度に重要な影響を与えることがあります。

コレクション内のアイテムの数を数える

コレクションに含まれるアイテムの数は、Count プロパティを使用することで簡単に確認できます。

MsgBox MyCollection.Count

このプロパティは、For Next ループでコレクションを反復処理する場合に使用します。

特定の値についてコレクションをテストする

For Eachループを使用すると、コレクションを繰り返し、項目の特定の値を検索することができます。

Sub SearchCollection()
Dim MyCollection as New Collection

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For Each Item In MyCollection
    If Item = "Item2" Then
        MsgBox Item & " が見つかりました"
    End If
Next
End Sub

このコードでは、小さなコレクションを作成し、その中からitem2というアイテムを探し、見つかった場合は、特定のアイテムが見つかったというメッセージボックスを表示します。この方法の欠点は、インデックスの値やキーの値にアクセスできないことです。代わりにFor Nextループを使用すると、For Nextカウンターを使用してインデックス値を取得することができますが、それでもキーの値を取得することはできません。

Sub SearchCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item 1"
MyCollection.Add "Item 2"
MyCollection.Add "Item 3"

For n = 1 To MyCollection.Count
    If MyCollection.Item(n) = "Item2" Then
        MsgBox MyCollection.Item(n) & " がインデックス位置 " & n & " で見つかりました"
    End If
Next n
End Sub

For Nextのカウンタ n がインデックス位置を表します。

コレクションをソートする

コレクションをソートする機能は組み込まれていませんが、「常識にとらわれない」発想で、Excelのワークシートのソート機能を利用して、ソートを行うコードを書くことができます。このコードでは、「SortSheet」と呼ばれる空のワークシートを使って実際のソートを行います。

Sub SortCollection()
Dim MyCollection As New Collection
Dim Counter As Long

'ランダムな順序のアイテムでコレクションを構築する
MyCollection.Add "Item5"
MyCollection.Add "Item2"
MyCollection.Add "Item4"
MyCollection.Add "Item1"
MyCollection.Add "Item3"

'将来使用するためにコレクション内のアイテム数を取得する
Counter = MyCollection.Count

'コレクションを繰り返し、各項目を'SortSheet' (column A)の連続したセルにコピーする
For n = 1 To MyCollection.Count
    Sheets("SortSheet").Cells(n, 1) = MyCollection(n)

Next n

'ソートシートをアクティブにして、Excelのソートルーチンを使用してデータを昇順にソートする
Sheets("SortSheet").Activate
Range("A1:A" & MyCollection.Count).Select
    ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("SortSheet").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'コレクション内のすべてのアイテムを削除する - このFor Next Loopは逆順で実行されることに注意してください
For n = MyCollection.Count To 1 Step -1
     MyCollection.Remove (n)
Next n

'保存しておいたサイズ(Counter)を使用して、セルの値を空のコレクションオブジェクトに戻す
For n = 1 To Counter
    MyCollection.Add Sheets("SortSheet").Cells(n, 1).Value
Next n

'アイテムの順序を確認する
For Each Item In MyCollection
    MsgBox Item
Next Item

'ワークシートSortSheetをクリアする - 必要であれば、それも削除する
Sheets("SortSheet").Range(Cells(1, 1), Cells(Counter, 1)).Clear
End Sub

このコードでは、まず、項目がランダムな順序で追加されたコレクションを作成し、それらをワークシート(SortSheet)の最初のカラムにコピーします。

そして、Excelのソート機能を使って列のデータを昇順に並べ替えます。このコードは、降順に並べ替えるように修正することもできます。

次に、For Next ループを使用して、コレクションからデータを削除します。Stepオプションは、コレクションの最後尾から先頭に向かって削除していることに注意してください。もし先頭から削除しようとすると、そのたびにインデックス値がリセットされてしまい、正しく削除されません。(インデックス 2 がインデックス 1 になってしまいます。)

最後に、別のFor Nextループを使用して、アイテムの値を空のコレクションに戻します。さらにFor Eachループで、コレクションが正しい昇順になったことを証明しています。

なお、残念ながらこのコードは、入力されていたかもしれないキーの値については考慮していません。

コレクションをサブ／関数に渡す

コレクションは、他のパラメータと同じように、Subや Functionに渡すことができます。

Function MyFunction(ByRef MyCollection as Collection)

コレクションは、ByRef を使用して渡すことが重要です。これは、元のコレクションが使用されることを意味します。もし、コレクションをByValで渡すと、コレクションのコピーが作成され、残念な結果になる可能性があります。 ByVal を使用してコピーを作成すると、関数内でコレクションを変更する際に、元のコレクションではなく、コピーに対してのみ行われるようになります。たとえば、関数内でコレクションに新しい項目を追加しても、元のコレクションには反映されないので、コードにバグが発生します。

関数からコレクションを返す

コレクションを関数から返す方法は、他のオブジェクトを返すのと同じです。この場合、Set キーワードを使用する必要があります。

Sub ReturnFromFunction()
Dim MyCollection As Collection
Set MyCollection = PopulateCollection
MsgBox MyCollection.Count
End Sub

このコードでは、MyCollectionというオブジェクトを作成するサブルーチンを作成し、Setキーワードを使用して、そのコレクションにデータを入力する関数を効果的に呼び出しています。これが完了すると、2つのアイテムのカウントを表示するメッセージボックスが表示されます。

Function PopulateCollection() As Collection
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"

Set PopulateCollection = MyCollection
End Function

関数PopulateCollectionは、新しいコレクションオブジェクトを作成し、それに2つのアイテムを追加します。そして、このオブジェクトを元のサブルーチンで作成されたコレクションオブジェクトに戻します。

コレクションを配列に変換する

コレクションを配列に変換したい場合があるかもしれません。また、データを変更したり操作したりできる場所に保存したい場合もあるでしょう。このコードでは、小さなコレクションを作成し、それを配列に変換しています。コレクションのインデックスが 1 で始まるのに対して、配列のインデックスは 0 で始まることに注目しましょう。コレクションには 3 つの項目がありますが、配列のサイズは、要素 0 があるので 2 にします。

Sub ConvertCollectionToArray()
Dim MyCollection As New Collection
Dim MyArray(2) As String

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For n = 1 To MyCollection.Count
    MyArray(n - 1) = MyCollection(n)
Next n

For n = 0 To 2
    MsgBox MyArray(n)
Next n
End Sub

配列をコレクションに変換する

配列をコレクションに変換したい場合もあります。例えば、配列の要素を取得するコードよりも高速かつエレガントな方法でデータにアクセスしたい場合です。コレクションは1つの次元しか持たないので、これは配列の1つの次元に対してのみ機能することに留意してください。

Sub ConvertArrayIntoCollection()
Dim MyCollection As New Collection
Dim MyArray(2) As String
MyArray(0) = "Item1"
MyArray(1) = "Item2"
MyArray(2) = "Item3"

For n = 0 To 2
    MyCollection.Add MyArray(n)
Next n
For Each Item In MyCollection
    MsgBox Item
Next Item
End Sub

多次元配列を使用したい場合は、配列の各行ごとに配列の値を連結して、配列の次元の間に区切り文字を使用します。

また、最初の次元の値を 1番目に追加し、次の次元の値を2番目に追加するというように、1次元に変換してデータをコレクションに移動させることもできます。例えば、配列が4次元であれば、コレクション内の4番目の値ごとに新しい値のセットが追加されることになります。

また、配列の値をキーとして追加することもできます。（ただし、一意であることが条件です。）