VBA ArrayList – メガガイド2022年改訂版

VBAのArrayListの使用

ArrayListは、値を格納するために使用できるVBAオブジェクトです。これはCollectionオブジェクトに似ていますが、プログラミングの観点からははるかに大きな柔軟性を持っています。ここでは、ArrayListとCollection、Arrayの違いについて説明します。

• Collection オブジェクトは 2 つのメソッド (Add, Remove) と 2 つのプロパティ (Count, Item) しか持っていませんが、ArrayList にはもっと多くのプロパティがあります。
• Collection オブジェクトは、読み取り専用です。一旦値が追加されると、インデックスされた値を変更することはできませんが、ArrayList では、編集が可能です。
• ArrayList オブジェクトは、その中に含まれるアイテムの数に応じて、サイズが拡大・縮小します。 ArrayListは、配列のように事前に寸法を決める必要はありません。
• ArrayList は 1 次元で（Collection オブジェクトと同じ）、デフォルトのデータ型はVariant で、数値、テキスト、日付など、あらゆるタイプのデータを受け入れることができます。

多くの点で、ArrayListはCollectionオブジェクトの欠点に対処しており、確かに、できることの柔軟性ははるかに高くなっています。

ArrayListオブジェクトは標準的なVBAライブラリの一部ではありません。Excel VBAのコードでは、遅延バインディングまたは事前バインディングの宣言よって使用することができます。

Sub LateBindingExample()
Dim MyList As Object
Set MyList = CreateObject("System.Collections.ArrayList")
End Sub

Sub EarlyBindingExample()
Dim MyList As New ArrayList
End Sub

事前バインディングを使用するには、まずVBAの参照設定で、ファイル ‘mscorlib.tlb’ への参照を入力する必要があります。 これを行うには、Visual Basic Editor (VBE)ウィンドウから、ツール＞参照設定を選択します。ポップアップウィンドウが表示され、利用可能なすべての参照が表示されます。mscorlib.dllまでスクロールダウンし、その横のボックスにチェックを入れます。OKをクリックすると、そのライブラリはプロジェクトの一部となります。

ArrayListオブジェクトの大きな欠点の1つは、「インテリセンス」がないことです。通常、VBAで範囲などのオブジェクトを使用する場合、利用可能なすべてのプロパティとメソッドのポップアップリストが表示されます。 ArrayListオブジェクトでは、このようなことはありません。また、メソッドやプロパティのスペルが正しいかどうか、慎重に確認する必要がある場合もあります。 また、VBEウィンドウでF2を押して’arraylist’で検索しても何も表示されないので、開発者にとってはあまり有益ではありません。 事前バインディングでは、すべて前もってコンパイルされているため、コードの実行速度がかなり速くなります。一方、遅延バインディングでは、コードを実行しながらオブジェクトをコンパイルしなければなりません。

ArrayListを用いたExcel アプリケーションの配布

すでに指摘したように、ArrayListオブジェクトはExcel VBAの一部ではありません。つまり、あなたが配布したアプリケーションを使う同僚は、’mscorlib.tlb’ ファイルにアクセスする必要があるのです。

このファイルは通常、次の場所にあります。

C:\Windows\Microsoft.NET\Framework\v4.0.30319

ユーザーがアプリケーションをロードしたときに、このファイルが存在するかをチェックするコードを（Dirメソッドを使って）書いて、見つからない場合に「軟着陸」するようにするとよいでしょう。もし目的のファイルが存在しなければ、コードを実行するとエラーが発生します。

また、ユーザーは適切なバージョンの.Net Frameworkをインストールする必要があります。たとえ、それ以降のバージョンであっても、V3.5がインストールされていなければ、アプリケーションは動作しません。

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

ArrayListオブジェクトは、ワークブックが開いている間のみ使用可能で、ワークブックを保存しても、オブジェクトは保存されません。ワークブックが再び開かれた場合、ArrayListオブジェクトはVBAコードを使用して再作成される必要があります。ArrayList をコードモジュール内のすべてのコードで使用できるようにするには、モジュールウィンドウの一番上にある宣言セクションでArrayList オブジェクトを宣言する必要があります。 これによって、そのモジュール内のすべてのコードが、ArrayList にアクセスできるようになります。 もし、ワークブック内のどのモジュールでもArrayListオブジェクトにアクセスできるようにしたい場合は、グローバルオブジェクトとして定義してください。

Global MyCollection As New ArrayList

ArrayList への入力と読み込み

最も基本的な動作は、ArrayList を作成し、そこにデータを入れて、そのデータを読み取り可能であることを確認ることです。 この記事のすべてのコード例は、事前バインディングを使用しており、上記のようにVBAの参照先に’mscorlib.tlb’を追加していることを前提としています。

Sub ArrayListExample()
'新しいArrayListオブジェクトを作成する
Dim MyList As New ArrayList

'リストにアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"

'ArrayListを繰り返し、値を確認する
For N = 0 To MyList.Count - 1
    MsgBox MyList(N)
Next N

End Sub

この例では、新しい ArrayList オブジェクトを作成して 3 つのアイテムを入力し、各アイテムを表示しながらリストを反復しています。

ArrayList のインデックスは 1 ではなく 0 から始まるので、Count の値から 1 を引く必要があることに注意してください。

また、For Eachループを使って、値を読み取ることもできます。

Sub ArrayListExample()
'新しいArrayListオブジェクトを作成する
Dim MyList As New ArrayList

'リストにアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"

'ArrayListを繰り返して値を確認する
For Each I In MyList
    MsgBox I
Next I

End Sub

ArrayList のアイテムの編集・変更

Collectionに対するArrayList の大きな利点は、リスト内のアイテムをコード内で編集・変更できることです。Collectionオブジェクトは読み込みのみですが、ArrayListオブジェクトは読み込み/書き込みが可能です。

Sub ArrayListExample()
'新しいArrayListオブジェクトを作成する
Dim MyList As New ArrayList

'リストにアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"

'Item(1)を'Item2'から'Changed'に変更する
MyList(1) = "Changed"

'変更がうまくいったことを確認するためにArrayListを繰り返す
For Each I In MyList
    'リストの内容を表示する
    MsgBox I
Next I

End Sub

この例では、2番目のアイテムである「Item2」が値「Changed」に変更されています（インデックスは0から始まることを覚えておいてください）。コードの最後でイテレーションを実行すると、新しい値が表示されます。

ArrayListへの値の配列の追加

これらの値のリストを含む配列、またはワークシートのセルの値への参照を使用して、ArrayListに値を入力することができます。

Sub AddArrayExample()
'ArrayListオブジェクトを作成する
Dim MyList As New ArrayList

'配列の値を繰り返してArrayListに追加する
For Each v In Array("A1", "A2", "A3")
    '配列の各値をリストに追加する
    MyList.Add v
Next

'ワークシートを参照してArrayListに追加する
For Each v In Array(Range("A5").Value, Range("A6").Value)
    MyList.Add v
Next

'ArrayListを繰り返し、値を確認する
For N = 0 To MyList.Count - 1
   'リストのアイテムを表示する
    MsgBox MyList.Item(N)
Next N

End Sub

ArrayListからのアイテムの範囲の読み込み・取得

ArrayListのGetRangeメソッドを使用すると、連続したアイテムの取得範囲を指定することができます。必要な2つのパラメータは、開始インデックス番号と取得するアイテムの数です。このコードでは、2 番目のArrayList・オブジェクトにサブセットのアイテムを入力し、別々に読み取ることができます。

Sub ReadRangeExample()
'オブジェクトの定義
Dim MyList As New ArrayList, MyList1 As Object

'MyList'オブジェクトにアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
MyList.Add "Item6"
MyList.Add "Item4"
MyList.Add "Item7"

'MyListのインデックス番号2から4つのアイテムを取り込む
Set MyList1 = MyList.GetRange(2, 4)

'オブジェクトMyList1を繰り返し、サブセットのアイテムを表示する
For Each I In MyList1
   'アイテムを表示する
    MsgBox I
Next I

End Sub

ArrayList内のアイテムの検索

リスト内に指定したアイテムがあるかどうかを調べるには、 Containsメソッドを使用します。これは、True または False を返します。

MsgBox MyList.Contains("Item2")

IndexOfメソッドを使用すると、実際のインデックスの位置を調べることもできます。その際、検索を開始するインデックスを指定する必要があります。（通常は0。） 返り値は、見つかったアイテムの最初のインスタンスのインデックスです。 その後、ループを使って開始点を次のインデックス値に変更し、 重複する値が複数ある場合はさらにインスタンスを探すことができます。

もし値が見つからなければ、値として-1が返されます。

この例では、Contains を使用してアイテムが見つからず、ArrayListをループしてすべての重複するアイテムの位置を見つけることを示します。

Sub SearchListExample()
'ArrayListと変数を定義する
Dim MyList As New ArrayList, Sp As Integer, Pos As Integer

'重複を含む新しいアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
MyList.Add "Item1"

'Item2がリスト内にあるかどうかをテスト：Trueを返す
MsgBox MyList.Contains("Item2")

'存在しない値のインデックスを取得する：-1を返す
MsgBox MyList.IndexOf("Item", 0)

'検索の開始位置を0に設定する
Sp = 0

'リストを繰り返し、'Item1' のすべての位置を取得する
Do
    '変数Spの位置を元に次の'Item1'のインデックス番号を得る
    Pos = MyList.IndexOf("Item1",Sp)
    'Item1のインスタンスが見つからなければ、ループを終了する
    If Pos = -1 Then Exit Do
    '次に見つかったインスタンスとインデックス番号を表示する
    MsgBox MyList(Pos) & " at index " & Pos
    '最後に見つかったインデックス値に1を追加する - これが次の検索のための新しい開始位置となる
    Sp = Pos + 1
Loop

End Sub

検索テキストは大文字と小文字を区別し、ワイルドカードは使用できないことに注意してください。

アイテムの挿入と削除

リストの末尾にアイテムを追加したくない場合は、特定のインデックス番号にアイテムを挿入して、新しいアイテムがリストの中央に来るようにすることができます。インデックス番号は、後続のアイテムに対して自動的に調整されます。

Sub InsertExample()
'ArrayListオブジェクトの定義
Dim MyList As New ArrayList

'ArrayListにアイテムを追加
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
MyList.Add "Item1"

'インデックス番号2に'Item6'を挿入する
MyList.Insert 2, "Item6"

'新しい順番とインデックス番号を表示するために、ArrayListのアイテムを繰り返し表示する
For N = 0 To MyList.Count - 1
    MsgBox MyList(N) & " インデックス " & N
Next N

End Sub

この例では、インデックス番号2のリストに ‘Item6’ が追加されたので、インデックス番号2にあった ‘item3’ はインデックス番号3に移動しています。 個々のアイテムを削除するには、Remove メソッドを使います。

MyList.Remove "Item"

アイテムが見つからなくても、エラーは発生しないことに注意してください。

それ以降のインデックス番号は、削除に合わせて変更されます。 アイテムのインデックス番号がわかっている場合は、「RemoveAt」メソッドを使用することができます。

MyList.RemoveAt 2

指定したインデックスの位置がArrayListのアイテム数よりも大きい場合は、エラーが返されることに注意しましょう。

RemoveRange メソッドを使用すると、リストから値の範囲を削除することができます。 パラメータには、開始インデックスと削除するアイテムの数を指定します。

MyList.RemoveRange 3, 2

開始値からオフセットされたアイテム数が、ArrayListのアイテム数よりも大きい場合は、 コードでエラーが発生することに注意しましょう。

RemoveAt と RemoveRange の両方のメソッドで、指定したインデックス番号がArrayListのアイテムの総数より大きいかどうかをチェックするコードを記述して、エラーの可能性があるものを捕捉することをお勧めします。 Count プロパティは、ArrayList内のアイテムの総数を表します。

Sub RemoveExample()
'ArrayListオブジェクトの定義
Dim MyList As New ArrayList
'ArrayListにアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
MyList.Add "Item1"
MyList.Add "Item4"
MyList.Add "Item5"
'Item6をインデックス番号2に挿入する
MyList.Insert 2, "Item6"
'Item2を削除する
MyList.Remove "Item2"
'Itemを削除する - これはArrayListに存在しないが、エラーにはならない
MyList.Remove "Item"
'インデックスの位置2のアイテムを削除
MyList.RemoveAt 2
'インデックス番号3から始まる2つの連続したアイテムを削除する
MyList.RemoveRange 3, 2
'ArrayListを繰り返し、何が残っていて、それが今どのインデックス番号にあるかを表示する
For N = 0 To MyList.Count - 1
    MsgBox MyList(N) & " インデックス " & N
Next N
End Sub

RemoveAtを使用して特定の位置のアイテムを削除する場合、そのアイテムが削除されると同時に、それ以降のすべてのインデックス番号が変更されることに注意してください。インデックス番号を使用して複数のアイテムを削除する場合は、最も高いインデックス番号から始めて、ゼロの位置まで後退させると、常に正しいアイテムが削除されるようにすることができます。こうすることで、インデックス番号が変わることによる問題が発生しなくなります。

ArrayListのソート

Collectionと比較した場合のもう一つの大きな利点は、アイテムを昇順または降順に並べ替えることができることです。 ArrayListオブジェクトは、Excel VBAでソートメソッドを持つ唯一のオブジェクトです。 Sortメソッドは非常に高速であり、これはArrayListを使用する際の重要な考慮事項になり得ます。 Collectionオブジェクトでは、すべてのアイテムをソートするために「常識にとらわれない」思考が必要でしたが、ArrayListでは非常にシンプルです。 Sort メソッドは昇順にソートし、 Reverse メソッドは降順にソートします。

Sub ArrayListExample()
'ArrayListオブジェクトの作成
Dim MyList As New ArrayList
'バラバラな順序でアイテムを追加
MyList.Add "Item1"
MyList.Add "Item3"
MyList.Add "Item2"
'アイテムを昇順にソートする
MyList.Sort
'ソートの結果を確認するために繰り返し実行する
For Each I In MyList
    'アイテムを表示する
    MsgBox I
Next I
'アイテムを降順にソートする
MyList.Reverse
'ソートの結果を確認するために繰り返し実行する
For Each I In MyList
    'アイテムを表示する
    MsgBox I
Next I
End Sub

ArrayListのクローニング

ArrayListには、それ自身のクローンやコピーを作成する機能があります。 これは、ユーザーがフロントエンドとVBAコードを使用してアイテムを変更する場合に便利ですが、バックアップとして元の状態のアイテムのコピーを保持する必要があります。 これにより、ユーザーに「元に戻す」機能を提供することができます。彼らは、変更を加えた後、元のリストに戻したいかもしれません。

Sub CloneExample()
'2つのオブジェクトを定義する - ArrayListとオブジェクト
Dim MyList As New ArrayList, MyList1 As Object
'最初のオブジェクトにアイテムを投入
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
'MyListをMyList1へコピー
Set MyList1 = MyList.Clone
'クローンの結果を確認するためにMyList1を繰り返し実行する
For Each I In MyList1
    'アイテムを表示
    MsgBox I
Next I
End Sub

‘MyList1’ には ‘MyList’ のすべてのアイテムが同じ順序で表示されるようになりました。

ArrayListをVBA標準配列オブジェクトへコピーする

ArrayListを通常のVBA配列にコピーする簡単なメソッドがあります。

Sub ArrayExample()
'ArrayListオブジェクトと標準配列オブジェクトを作成する
Dim MyList As New ArrayList, NewArray As Variant
'ArrayListにアイテムを入力する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
'ArrayListを新しい配列にコピー
NewArray = MyList.ToArray
'作成された配列の内容を確認する
For N = 0 To MyList.Count - 1
    'アイテムを表示
    MsgBox NewArray(N)
Next N
End Sub

ArrayListをワークシートの範囲へコピーする

ArrayListを繰り返し処理することなく、ArrayListを特定のワークシートとセル参照にコピーすることができます。必要なのは、最初のセル参照を指定することだけです。

Sub RangeExample()
'新しいArrayListオブジェクトを作成
Dim MyList As New ArrayList
'リストにアイテムを追加
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
'対象シートのクリア
Sheets("Sheet1").UsedRange.Clear
'行を跨いでアイテムをコピーする
Sheets("Sheet1").Range("A1").Resize(1, MyList.Count).Value = MyList.toArray
'アイテムを列の下にコピーする
Sheets("Sheet1").Range("A5").Resize(MyList.Count, 1).Value = _ 
WorksheetFunction.Transpose(MyList.toArray)
End Sub

ArrayListのすべてのアイテムを空にする

ArrayListを完全にクリアする簡単な関数(Clear)も用意されています。

Sub ClearListExample()
'ArrayListオブジェクトの作成
Dim MyList As New ArrayList
'新しいアイテムを追加する
MyList.Add "Item1"
MyList.Add "Item2"
MyList.Add "Item3"
'アイテムのカウントを表示
MsgBox MyList.Count
'全アイテムをクリア
MyList.Clear
'クリアが成功したことを証明するためにアイテムの数を表示する
MsgBox MyList.Count
End Sub

この例では、ArrayListにアイテムを作成し、ArrayListをクリアしています。 メッセージボックスは、ArrayListのアイテム数の前後に表示されます。

Excel VBAのArrayListメソッドのまとめ

タスク	パラメータ	操作例
アイテムの追加・編集	値	MyList.Add “Item1”
		MyList(4)=”Item2″
クローニング	なし	Dim MyList As Object
		Set MyList2 = MyList.Clone
標準配列にコピー	なし	Dim MyArray As Variant
		MyArray = MyList.ToArray
ワークシートの範囲(行)へコピー	なし	Sheets(“Sheet1”).Range(“A1”).Resize(1, MyList.Count).Value = MyList.ToArray
ワークシートの範囲(列)へコピー	なし	Sheets(“Sheet1”).Range(“A3”).Resize(MyList.Count, 1).Value = WorksheetFunction.Transpose(MyList.ToArray)
作成する	“System.Collections.ArrayList “	Dim MyList As Object
		Set MyList = CreateObject(“System.Collections.ArrayList”)
宣言	該当なし	Dim MyList As Object
アイテムの検索/存在確認	検索するアイテム	MyList.Contains(“Item2”)
アイテムの位置の検索	1.検索するアイテム	Dim IndexNo As Long
	2.検索を開始する位置	IndexNo = MyList.IndexOf(“Item3”,0)
		IndexNo = MyList.IndexOf(“Item5”, 3)
アイテム数の取得	なし	MsgBox MyList.Count
アイテムの挿入	1.インデックス – 挿入する位置	MyList.Insert 0, “Item5”
	2. 値 – 挿入するオブジェクトまたは値	MyList.Insert 4, “Item7”
アイテムの読み込み	インデックス – 整数値	MsgBox MyList.Item(0)
		MsgBox MyList.Item(4)
最後に追加されたアイテムの読み込み	インデックス – 整数値	MsgBox MyList.Item(list.Count – 1)
最初に追加されたアイテムの読み込み	インデックス – 整数値	MsgBox MyList.Item(0)
全アイテムの読み込み(For Each)	該当なし	Dim element As Variant
		For Each element In MyList
		MsgBox element
		Next element
全アイテムの読み込み(For)	インデックス – 整数値	Dim i As Long
		For i = 0 To MyList.Count – 1
		MsgBox i
		Next i
すべてのアイテムの削除	なし	MyList.Clear
指定した位置のアイテムの削除	アイテムがあるインデックス番号	MyList.RemoveAt 5
名前によるアイテムの削除	ArrayListから削除するアイテム名	MyList.Remove “Item3”
アイテムの範囲の削除	1. インデックス – 開始位置	MyList.RemoveRange 4,3
	2. Count – 削除するアイテムの数
降順でソート	なし	MyList.Reverse
昇順でソート	なし	MyList.Sort