VBAのコレクション(Collection)について
VBAにはコレクション(Collection)と呼ばれるオブジェクトがあります。
コレクション(Collection)オブジェクトはオブジェクトの集合体であり、コレクション(Collection)自体もまたオブジェクトとなります。
こちらの記事を読んでおられるあなたが、仕事などでエクセルやスプレッドシートをつかう機会が多ければ、それらに存在しているシートをコレクション(Collection)としてイメージできるかと思いますが、VBAではオブジェクトの集まりをコレクションと呼び、例えばシートオブジェクトのシートコレクションと呼びます。
VBAにおいてくり返し処理を実現する場合によく使われるForNextステートメントのほかに、ForEachステートメントをつかうことがありますが、こちらは特定のオブジェクトの集合体に対してくり返し処理を指定したいときに使います。ForEachステートメントでは条件分岐を指定しない限り、すべてのオブジェクトへの処理が完了するまで処理をくり返します。
つまり、ForEachステートメントでワークシートコレクションを指定した場合は、ブック内のすべてのワークシートに対して処理をくり返す指定をしたことになるので、ワークシートオブジェクトの集合体が、シートコレクションになります。
VBAのコレクション(Collection)は任意に作成ができる
冒頭でVBAにはコレクション(Collection)オブジェクトと呼ばれるものがあり、いくつかのワークシートの集合体であるとイメージすればわかりやすいのではないかとお伝えしました。
しかし、エクセルにはすでにたくさんのコレクションが用意されています。先に紹介したとおり、ワークシートのコレクションも既にありますので、シートオブジェクト全体への処理をおこなうのであれば「Worksheetsコレクション」を利用することで目的の処理の実現ができます。
一方、ここで紹介する内容はこれらの用意されたコレクションの利用ではなく、自ら作成したコレクション(Collection)に、追加する要素(メンバ)を指定してオブジェクトにまとめてしまう方法となります。
つまり、オリジナルのコレクションをつくって、まとめて処理に利用する方法の紹介となります。
なぜ、膨大な事務作業でも定時で退社できるのか。
実務をプロから学べる「ユースフル」の動画は永年見放題。Q&A機能で分からないを放置しないから安心。
詳しくは以下のリンクをクリック
VBAのコレクション(Collection)をつかうメリット
VBAのコレクション(Collection)オブジェクトであるとお伝えしました。
これに類似するものとして、配列(Ａrray)や連想配列(Ｄictionary)のようなものがありますが、コレクション(Collection)オブジェクトと比較した場合、コレクションをつかう利点としては以下のようなものがあります。
サイズ変更につよいまとまりができる
配列(Array)でもサイズの変更ができないわけではありませんが、配列(Array)の場合、要素の追加の前に要素の数を指定しなおす必要があります。一方、コレクション(Collection)の場合は要素数の追加がカンタンにできるため、柔軟な対応がしやすいです。
データ型がちがう要素をまとめることができる
配列(Ａrray)の場合、文字列データ型なら文字列のみといったデータ型が固定されてしまうところ、コレクション(Collection)であれば異なるデータ型の要素(メンバ)でも追加することができます。数値や文字列、図形やシートオブジェクトが混在している独自のコレクション(Collection)が作成できるので自由度が高いです。
キー(Key)の指定ができる
コレクション(Collection)も数値(インデックス)以外に、Dictionary(ディクショナリー)オブジェクトと同じように指定した文字列などをキー(Key)に指定することができるため、要素(メンバ)の指定がしやすいです。
配列(Array)や連想配列(Ｄictionary)の使いかたについては、ほかの記事で紹介しています。コレクションではなく、配列(Array)や連想配列(Ｄictionary)の使いかたを知りたい人は以下のリンクよりそちらの記事を参考にしてください。
VBAのコレクション(Collection)オブジェクトのつかいかた
VBAでコレクション(Collection)をつかうためには、宣言セクションまたは、プロシージャのなかで宣言をする必要があります。また、宣言したコレクション(Collection)はプロパティやメソッドがあり、これらをつかってコレクション(Collection)の情報を取得することや、処理をおこなうことができます。
それぞれの方法について見ていきましょう。
VBAのコレクション(Collection)の宣言
まずはコレクション(Collection)をつかうための宣言方法ですが、以下のように行います。
Public | Private | Dim コレクション名 As Collection
※宣言セクションで宣言する場合はPublic またはPrivateをつかいます。Public またはPrivateで宣言した場合はモジュールレベルの適用範囲(スコープ)となります。一方、プロシージャのなかでのみコレクションを利用する場合はDimで宣言するとよいでしょう。
また、コレクション(Collection)を利用するためには、宣言コードとあわせて以下を書く必要があります。
Set コレクション名 = New Collection
最もわかりやすいケースのプロシージャのなかでのみコレクション(Collection)を利用するケースの場合は、この2つのコードを1行または2行で書きます。それぞれの書きかたは以下のとおりです。
コレクション(Collection)の宣言を2行で書いた場合
Option Explicit
Dim コレクション名 As Collection
Set コレクション名 = New Collection
コレクション(Collection)の宣言を1行で書いた場合
Option Explicit
Dim コレクション名 As New Collection
いずれかを書くことでコレクション(Collection)の宣言をしたことで、コレクションオブジェクトの使用準備ができている状態になりました。
コレクション(Collection)を宣言すると自動メンバー表示ができるよ。
コレクション(Collection)が認識されると、コレクション名のあとに(.)ピリオドを入力すると、コレクションで実行できるプロパティやメソッドがメンバーとして選択項目として表示されるようになります。
それでは、次の章からコレクション(Collection)のメンバーであるプロパティやメソッドを紹介していきます。
VBAのコレクション(Collection)のプロパティ・メソッド
コレクション(Collection)オブジェクトのメンバーには、プロパティとメソッドがあります。
それぞれがどのような働きをするのか紹介していきます。
プロパティはオブジェクトの状態の取得、メソッドはオブジェクトに対する処理だよ。
VBAのコレクション(Collection)のCountプロパティ
Countプロパティは、コレクション(Collection)オブジェクトの要素(メンバ)の数を取得することができるプロパティです。
| プロパティ名 | 説明 |
|---|---|
| Count | Collection(コレクション)オブジェクトの要素(メンバ)の数が取得できます。出力される値は長整数型(Long)の値です。 |
コレクション(Collection)のCountプロパティ
コレクション名.Count
※以下の例はコレクションを宣言しただけで、まだ何も代入されていない状態なので、Countプロパティをつかって要素(メンバ)の数を出力すると「0」が返ってきます。
コレクション(Collection)のCountプロパティの使用例
Option Explicit
Sub test001()
Dim コレクション名 As Collection
Set コレクション名 = New Collection
Debug.Print コレクション名.Count'実行結果：0
End Sub
08行目
コレクション(Collection)のCountプロパティをつかってオブジェクトの要素数を返します。サンプルマクロではイミディエイトウィンドウに結果を表示します。
実行結果
サンプルマクロでは、コレクション(Collection)オブジェクトになにも追加されていない状態なので、要素数が0でかえってきます。
コレクション(Collection)オブジェクトにいくつものがあるかを教えてくれるよ。
VBAのコレクション(Collection)のAddメソッド
Addメソッドは、コレクション(Collection)オブジェクトに要素(メンバ)の追加ができます。
| メソッド名 | 説明 |
|---|---|
| Add | Collection(コレクション)オブジェクトに要素(メンバ)を追加します。 |
なお、Addメソッドには以下の引数があります。
コレクション(Collection)のAddメソッド
コレクション名.Add Item,[Key],[before], [after]
※[]の引数は省略可能です。
コレクション(Collection)のAddメソッドの引数について
| 引数名 | 必須Or省略可能 | 説明 |
|---|---|---|
| コレクション名 (Object) |
必須 | Collection(コレクション)オブジェクトを指定します。 |
| メンバ (Item) |
必須 | Collection(コレクション)オブジェクトに追加する要素(メンバ)を指定します。 Itemはほかの要素(メンバ)と同じ値を指定することができます。 |
| メンバの位置を表す文字列 (key) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を文字列で指定します。 省略した場合はKeyをつかって要素(メンバ)を返すことはできません。また、Keyはほかの要素(メンバ)と同じ値を指定することはできません。 |
| メンバの追加位置 (before) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を既に存在する要素(メンバ)を指定して、指定要素(メンバ)の直前に追加します。「●●の前に追加する」といったイメージです。 ※afterとあわせて指定することはできません。 |
| メンバの追加位置 (after) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を既に存在する要素(メンバ)を指定して、指定要素(メンバ)の直後に追加します。「●●の後に追加する」といったイメージです。 ※beforeとあわせて指定することはできません。 |
コレクション(Collection)のAddメソッドの使用例
Option Explicit
Sub Test002()
'コレクション名「ひな人形」を宣言する
Dim ひな人形 As Collection
Set ひな人形 = New Collection
'①ひな人形の種類でどの段に何を飾るかをコレクションにする。
ひな人形.Add Item:="五人囃子", Key:="3段目"
'＝==最上段から5段目から順番どおりにならべる(順番はKeyを参照)＝==
'②五人囃子の直前に追加する(Keyで順番を指定)
ひな人形.Add Item:="親王", Key:="最上段", before:="3段目"
'③親王の直後に追加する(インデックスで順番を指定)
ひな人形.Add Item:="三人官女", Key:="2段目", after:=1
'④引数のbefore・afterを指定せず仕丁(じちょう)を追加する
ひな人形.Add Item:="仕丁", Key:="5段目" 'コレクションの末尾に追加される。
'⑤4段目に飾る随身(ずいじん)を3段目の直後に追加する
ひな人形.Add Item:="随身", Key:="4段目", after:="3段目" '下の行のコードでも同じ結果になる。
'ひな人形.Add Item:="随身", Key:="4段目", before:=4
End Sub
06行目と07行目
コレクション(Collection)のオブジェクトとしてひな人形という名前で宣言します。
10行目
ひな人形のAddメソッドを利用して要素を追加します。サンプルマクロのなかでは、”五人囃子”という文字列にキー(Key)として”3段目”を紐づけて要素を追加しています。
15行目
ひな人形のAddメソッドを利用して要素を追加します。サンプルマクロのなかでは、”親王”という文字列にキー(Key)として”最上段”を紐づけて要素を追加しています。さらに引数のbeforeに”3段目”を指定することで、3段目の前に追加する指示になります。
18行目
ひな人形のAddメソッドを利用して要素を追加します。こんどは”三人官女”という文字列にキー(Key)として”2段目”を紐づけて要素を追加しています。さらに引数のafterに1を指定することで、1つ目の要素のあとに追加する指示になります。
21行目
ひな人形のAddメソッドを利用して要素を追加します。さらに”仕丁”という文字列にキー(Key)として”5段目”を紐づけて要素を追加しています。引数にbeforeやafterがないため、さいごの要素として追加します。
24行目
ひな人形のAddメソッドを利用して要素を追加します。さいごに”随身”という文字列にキー(Key)として”4段目”を紐づけて要素を追加しています。サンプルマクロで有効となっているコードでは、引数のafterに”3段目”を指定していますので、”五人囃子”のあとに追加されます。こちらは25行目に記載したコメントアウトのコードとどちらを有効にしても同じ結果となります。
(※引数の指定にある after:=”3段目”と、before:=4は同じ位置への追加となるため。)
コレクション(Collection)に要素(メンバ)を追加していくながれ
サンプルマクロにおける要素追加のながれを図にすると以下のとおりです。
beforeとafterの指定で存在しないものを指定するとエラーになるよ。
VBAのコレクション(Collection)のItemメソッド
Itemメソッドはコレクション(Collection)オブジェクトに要素(メンバ)を返します。
| メソッド名 | 説明 |
|---|---|
| Item | Collection(コレクション)オブジェクトの要素(メンバ)を返します。 返したい要素(メンバ)の、数値や文字列を指定します。 |
コレクション(Collection)のItemメソッド
コレクション名.Item(1)
コレクション名.Item(Key)
※数値や指定したKeyで要素(メンバ)を返します。追加時にKeyを指定していない要素(メンバ)はKeyをつかって値を返すことはできません。
コレクション(Collection)のItemメソッドの使用例
Option Explicit
Sub test003()
Dim 集合場所 As Collection
Set 集合場所 = New Collection
集合場所.Add Item:="グラウンド", Key:="はれ"
集合場所.Add Item:="グラウンド", Key:="くもり" 'Itemは同じ値を指定可能
集合場所.Add Item:="体育館", Key:="あめ"
集合場所.Add Item:="自宅待機" '引数のKeyを省略
Debug.Print 集合場所.Item(1) 'グラウンド
Debug.Print 集合場所.Item("くもり") 'グラウンド
Debug.Print 集合場所.Item("あめ") '体育館
Debug.Print 集合場所.Item(4) '自宅待機
'Debug.Print 集合場所.Item("体育館") 'エラー(プロシージャの呼び出しまたは引数が不正)
'Debug.Print 集合場所.Item("たいふう") 'エラー(プロシージャの呼び出しまたは引数が不正)
End Sub
05行目と06行目
コレクション(Collection)のオブジェクトとして集合場所という名前で宣言します。
08行目と11行目
Addメソッドを利用して、集合場所に要素を追加していきます。Itemは同じものが追加できますが、キー(Key)は同じものを指定できません。
13行目と16行目
Itemメソッドを利用して、集合場所の要素を取り出します。コレクション(Collection)オブジェクトのインデックスの数値または、キー(Key)となっている文字列で取り出したい要素を指定します。
18行目と19行目
サンプルマクロではコメントアウトしていますが、コレクション(Collection)オブジェクトのItemメソッドでItemを指定した場合や、存在しないキー(Key)を指定した場合はエラーが発生します。
実行結果
グラウンド
体育館
自宅待機
VBAのコレクション(Collection)のRemoveメソッド
Removeメソッドはコレクション(Collection)オブジェクトに要素(メンバ)を削除します。
| メソッド名 | 説明 |
|---|---|
| Remove | Collection(コレクション)オブジェクトの要素(メンバ)を削除します。 削除したい要素(メンバ)の、数値や文字列を指定します。 |
コレクション(Collection)のRemoveメソッド
コレクション名. Remove 1
コレクション名. Remove Key
コレクション(Collection)のRemoveメソッドの引数について
| 引数名 | 必須Or省略可能 | 説明 |
|---|---|---|
| コレクション名 (Object) |
必須 | Collection(コレクション)オブジェクトを指定します。 |
| メンバの位置を表す数値または文字列 (Index) |
必須 | Collection(コレクション)オブジェクトに含まれる要素(メンバ)の位置や文字列(Key)を指定します。 ただし、要素(メンバ)の追加時にKeyを指定していない場合、文字列(Key)で指定することはできません。 ※エラーが発生します。 |
コレクション(Collection)のRemoveメソッドの使用例
Option Explicit
Sub test004()
'コレクション名「Alphabet」をコレクションとして宣言する
Dim Alphabet As Collection
Set Alphabet = New Collection
'コレクション「Alphabet」のAddメソッドで要素(メンバ)を追加する
Alphabet.Add Item:="A", Key:="アルファベット1"
Alphabet.Add Item:="B", Key:="アルファベット2"
Alphabet.Add Item:="C", Key:="アルファベット3"
Alphabet.Add Item:="X", Key:="アルファベット24"
Alphabet.Add Item:="Y", Key:="アルファベット25"
Alphabet.Add Item:="Z", Key:="アルファベット26"
'コレクション「Alphabet」のRemoveメソッドで要素(メンバ)を削除する
Alphabet.Remove 6 'Indexの指定で要素(メンバ)のZを削除。
Alphabet.Remove "アルファベット25" 'Keyの指定で要素(メンバ)のYを削除。
End Sub
06行目と07行目
コレクション(Collection)のオブジェクトとしてAlphabetという名前で宣言します。
10行目と15行目
Addメソッドを利用して、Alphabetに要素を追加していきます。指定したアルファベットとそのアルファベットが何番目であるかをキー(Key)として紐づけています。
18行目と19行目
Removeメソッドを利用して、Alphabetの要素を削除します。18行目では、Alphabetのインデックスで6を指定、19行目ではキー(Key)が”アルファベット25″の要素を指定しています。
Removeメソッドでコレクション(Collection)の要素(メンバ)を削除するながれ
サンプルマクロにおける要素削除のながれを図にすると以下のとおりです。
削除する前のコレクションの状態
先頭の要素(メンバ)を削除したあとのコレクションの状態
要素のIndex2番目が先頭につめられ、Index 6番目がなくなる。
この状態で6番目の要素(メンバ)を指定した場合はエラーが発生します。
中間の要素(メンバ)を削除したあとのコレクションの状態
Index 3番目のCが削除され、それまで４番目だったXの順番が詰められて3番目になります、結果、Index6番はなくなる。削除されたIndex 3番目が空白になるわけではない点に注意。
末尾の要素(メンバ)を削除したあとのコレクションの状態
Index 6番目はなくなる。(空白になるわけではない)
Removeメソッドで要素に削除をしたときの勘違いしやすいケース
削除された要素(メンバ)が空白になるわけではない
コレクション(Collection)の先頭の要素が空白になるわけではない点に注意
要素(メンバ)がつめられて、末尾の要素(メンバ)が空白になるわけではない
コレクション(Collection)の末尾の要素が空白になるわけではない点に注意
オンラインスクールで現役エンジニアのサポートがあるテックアカデミーがおすすめ。
スキマ時間に学べて仕事も保証。必ず副業、始められます。まずは無料でプログラミング体験
VBAのコレクション(Collection)のサンプルマクロ
ここからは、コレクション(Collection)オブジェクトをつかったサンプルマクロを紹介します。
コレクションを宣言して、要素(メンバ)を出力するマクロを紹介したいと思います。
これまでに紹介したプロパティやメソッドのサンプルマクロと異なるポイントととして、それぞれのプロパティやメソッドの処理ごとをまとめたSubプロシージャとして呼び出すマクロであること。もう一点は、ループ処理をつかってコレクションの中身をすべて出力する処理をしています。
コレクション(Collection)のすべての要素(メンバ)を出力する
サンプルマクロのVBAコード
Option Explicit
Sub コレクションテスト_宣言()
'Collectionの宣言方法は2種類
'①
Dim キュウシュウ As Collection
Set キュウシュウ = New Collection
'②
'Dim キュウシュウ As New Collection
'コレクションに要素を追加する処理を呼び出す
Call コレクションテスト_Add(キュウシュウ)
'コレクションをメンバの数を出力する処理を呼び出す
Call コレクションテスト_Count(キュウシュウ)
'コレクションをメンバの数を出力する処理を呼び出す
Call コレクションテスト_Remove(キュウシュウ)
'コレクションに要素を追加する処理を呼び出す
Call コレクションテスト_Add2(キュウシュウ)
'コレクションに要素を追加する処理を呼び出す
Call コレクションテスト_値の表示(キュウシュウ)
End Sub
Sub コレクションテスト_Add(キュウシュウ)
'コレクションにメンバ(要素)を追加する
'Itemはメンバの値を指定します。
'keyは数値以外のインデックスとして指定する文字列(Keyは重複登録ができない)
キュウシュウ.Add Item:="熊本県", Key:="熊本"
キュウシュウ.Add Item:="佐賀県", Key:="佐賀"
キュウシュウ.Add Item:="石川県", Key:="石川"
キュウシュウ.Add Item:="長崎県", Key:="長崎"
キュウシュウ.Add Item:="福岡県", Key:="福岡"
キュウシュウ.Add Item:="青森県", Key:="青森"
End Sub
Sub コレクションテスト_Count(キュウシュウ)
'CollectionのCountプロパティはコレクションのメンバの数を出力する
Debug.Print キュウシュウ.Count '実行結果は6
End Sub
Sub コレクションテスト_Remove(キュウシュウ)
'CollectionのRemoveメソッドはコレクションからメンバを削除する
キュウシュウ.Remove 3 'インデックスで3を指定(石川県)
'CollectionのRemoveメソッドはコレクションからメンバを削除する
キュウシュウ.Remove "青森" 'keyで指定(青森県)
End Sub
Sub コレクションテスト_Add2(キュウシュウ)
'追加する場所をbeforeやafterでコレクションのどこに追加するかを指定する
'CollectionのAddメソッドの「before」はメンバを追加する位置を指定する(インデックス1よりも前に追加)
キュウシュウ.Add Item:="大分県", Key:="大分", before:=1
'CollectionのAddメソッドの「before」はメンバを追加する位置を指定する(Keyの”熊本”の前に追加)
キュウシュウ.Add Item:="鹿児島県", Key:="鹿児島", before:=("熊本")
'CollectionのAddメソッドの「after」はメンバを追加する位置を指定する(最後のインデックスよりも後に追加)
キュウシュウ.Add Item:="宮崎県", Key:="宮崎", after:=キュウシュウ.Count
End Sub
Sub コレクションテスト_値の表示(キュウシュウ)
Debug.Print キュウシュウ(2) 'コレクションの2番目は「鹿児島県」
Debug.Print キュウシュウ("鹿児島") 'Keyの鹿児島を指定してItemの「鹿児島県」を取り出す
Debug.Print キュウシュウ.Item(6) 'Itemメソッドでコレクションの6番目を指定：福岡県
Debug.Print キュウシュウ.Item("福岡") 'ItemメソッドでコレクションのKeyを指定：福岡県
'Collectionの要素をすべて表示する(ForNextでは要素が多いと遅くなる傾向あり)
Dim i As Long
For i = 1 To キュウシュウ.Count
Debug.Print キュウシュウ.Item(i)
Next i
'Collectionの要素をすべて表示する(ForEach)
Dim Val As Variant
For Each Val In キュウシュウ
Debug.Print Val
Next Val
'ループ処理の実行結果はどちらも以下のとおりとなります。
'================
'大分県
'鹿児島県
'熊本県
'佐賀県
'長崎県
'福岡県
'宮崎県
'================
End Sub
07行目と08行目
コレクション(Collection)のオブジェクトとしてキュウシュウという名前で宣言します。
14行目・17行目・20行目・23行目・26行目
Callステートメントにより他のSubプロシージャ呼び出します。呼び出し先にキュウシュウを渡します。
・14行目では、コレクションテスト_Addを呼び出します。
・17行目では、コレクションテスト_Countを呼び出します。
・20行目では、コレクションテスト_Removeを呼び出します。
・23行目では、コレクションテスト_Add2を呼び出します。
・26行目では、コレクションテスト_値の表示を呼び出します。
30行目
Subプロシージャとしてコレクションテスト_Addを作成します。呼び出し元からキュウシュウを受け取ります。
35行目から40行目
Addメソッドを利用して要素を追加します。サンプルマクロでは、都道府県名を追加しています。キー(Key)には、各都道府県の名称部分のみを文字列として登録します。
例：登録する要素が”熊本県”の場合は、キー(Key)を”熊本”にする。
44行目
Subプロシージャとしてコレクションテスト_Countを作成します。呼び出し元からキュウシュウを受け取ります。
47行目
Countプロパティを利用してキュウシュウの要素数をイミディエイトウィンドウに表示します。
51行目
Subプロシージャとしてコレクションテスト_Removeを作成します。呼び出し元からキュウシュウを受け取ります。
54行目と57行目
Removeメソッドを利用してキュウシュウの要素を削除します。インデックスが3とキー(Key)で”青森“の2つの要素を削除しています。
62行目
Subプロシージャとしてコレクションテスト_Add2を作成します。呼び出し元からキュウシュウを受け取ります。
67行目・70行目・73行目
再びAddメソッドを利用してキュウシュウの要素を追加します。引数のbeforeやafterによりコレクション(Collection)オブジェクトの何番目に追加するかを指定しています。
77行目
Subプロシージャとしてコレクションテスト_値の表示を作成します。呼び出し元からキュウシュウを受け取ります。
79行目から82行目
キュウシュウに登録されている要素を各種指定方法でイミディエイトウィンドウに表示します。
86行目から88行目
ForNextステートメントでくり返しを指定しています。Itemメソッドによりキュウシュウのすべての要素をイミディエイトウィンドウに表示します。
92行目から94行目
ForEachステートメントでくり返しを指定しています。キュウシュウのすべての要素をイミディエイトウィンドウに表示します。
実行結果
鹿児島県
鹿児島県
福岡県
福岡県
大分県
鹿児島県
熊本県
佐賀県
長崎県
福岡県
宮崎県
大分県
鹿児島県
熊本県
佐賀県
長崎県
福岡県
宮崎県
実行結果の詳細は以下のとおりです。サンプルマクロのコード47行目では要素数の6が表示されます。79行目は”鹿児島県”、80行目は”鹿児島県”、81行目は”福岡県”、82行目は”福岡県”、86行目から88行目と92行目から94行目ではくり返しによるコレクション(Collection)オブジェクトのすべての要素が表示されています。
それぞれのプロシージャの関係性と処理のながれのイメージ
VBAで書いたコードだと長文になりますが、それぞれのプロシージャの処理の内容は単純なものであり、中心となっているプロシージャから呼び出してつないでいるだけです。
プロシージャとはSub 〇〇 ～ End SubまでのVBAコードの単位のことだよ。
ひとつずつのプロシージャが処理していることは単純だよね。
ユーザーフォームのコントロールをコレクション(Collection)として管理する
コレクション(Collection)はオブジェクトをまとめることができるとお伝えしました。つづいてのサンプルマクロは、ユーザーフォームのコントロール(テキストボックスやコンボボックス)をコレクション(Collection)の要素として条件判定などの管理をするマクロを紹介します。
まずは、ここで紹介するマクロで利用するユーザーフォームを作成しました。
こちらはエクセルのワークシートにデータを入力するフォームを想定して作成していますが、ごらんの通りユーザーフォーム上にいくつかのコントロール(テキストボックスやボタンなど)を配置しています。
ユーザーフォームやコントロールの配置方法については別の記事で紹介していますので、以下のリンクをクリックして記事を参考にすすめてください。
さて、こちらのユーザーフォームには、ユーザーが入力するものとして、テキストボックスが5つとコンボボックスが2つ配置されていて「登録」ボタンを押すと、ユーザーが入力した値をエクセルのワークシートに記入する処理をします。
これらのコントロールを、コレクション(Collection)の要素(メンバ)としてまとめて制御するために、それぞれのコントロールを以下の画像をようにコレクション(Collection)の要素(メンバ)に追加します。
つづいて、ユーザーフォームに以下のVBAコードを書きこみます。
サンプルマクロのVBAコード
Option Explicit
Private Sub UserForm_Initialize()
'コレクション(Collection)のTypesを宣言する
Dim Types As Collection
Set Types = New Collection
'コレクション(Collection)の要素を追加する
Types.Add Item:="ノーマルタイプ", Key:="ノーマル"
Types.Add Item:="ほのおタイプ", Key:="ほのお"
Types.Add Item:="みずタイプ", Key:="みず"
Types.Add Item:="くさタイプ", Key:="くさ"
Types.Add Item:="でんきタイプ", Key:="でんき"
Types.Add Item:="こおりタイプ", Key:="こおり"
Types.Add Item:="かくとうタイプ", Key:="かくとう"
Types.Add Item:="どくタイプ", Key:="どく"
Types.Add Item:="じめんタイプ", Key:="じめん"
Types.Add Item:="ひこうタイプ", Key:="ひこう"
Types.Add Item:="エスパータイプ", Key:="エスパー"
Types.Add Item:="むしタイプ", Key:="むし"
Types.Add Item:="いわタイプ", Key:="いわ"
Types.Add Item:="ゴーストタイプ", Key:="ゴースト"
Types.Add Item:="ドラゴンタイプ", Key:="ドラゴン"
Types.Add Item:="あくタイプ", Key:="あく"
Types.Add Item:="はがねタイプ", Key:="はがね"
Types.Add Item:="フェアリータイプ", Key:="フェアリー"
'ForNextによるコレクション(Collection)の項目の追加
Dim i As Integer 'くり返し処理用の変数の宣言
For i = 1 To Types.Count
タイプ1コンボ.AddItem Types(i)
'タイプ2コンボ.AddItem Types(i)
Next i
'ForEachによるコレクション(Collection)の項目の追加
Dim WType As Variant 'くり返し処理用の変数の宣言
For Each WType In Types
'タイプ1コンボ.AddItem WType
タイプ2コンボ.AddItem WType
Next WType
End Sub
Private Sub 登録ボタン_Click()
'コレクション(Collection)のInputBoxsを宣言する
Dim InputBoxs As New Collection
Dim TBox As Variant
'コレクション(Collection)のInputBoxsに要素(メンバ)を追加する
With InputBoxs
.Add Item:=名前ボックス, Key:="名前"
.Add Item:=分類ボックス, Key:="分類"
.Add Item:=特性ボックス, Key:="特性"
.Add Item:=高さ入力ボックス, Key:="高さ"
.Add Item:=重さ入力ボックス, Key:="重さ"
End With
'コレクション(Collection)のInputBoxs2を宣言する
Dim InputBoxs2 As New Collection
Dim CBox As Variant
'コレクション(Collection)のInputBoxs2に要素(メンバ)を追加する
With InputBoxs2
.Add Item:=タイプ1コンボ, Key:="タイプ1"
.Add Item:=タイプ2コンボ, Key:="タイプ2"
End With
'InputBoxsの要素(メンバ)に空白がある場合の処理をループ処理にする
For Each TBox In InputBoxs
If TBox = "" Then
MsgBox "なまえ・ぶんるい・とくせい・たかさ・おもさのいずれかに空白があります", vbCritical + vbOKOnly, "入力不正通知"
Exit Sub
End If
Next TBox
'InputBoxsの要素(メンバ)である「たかさ」または「おもさ」の入力が0であった場合のループ処理にする
Dim NCheck As Integer
For NCheck = 4 To 5 '数値をあつかう「たかさ」と「おもさ」ボックスの範囲でくり返す
If InputBoxs(NCheck) <= 0 Then
MsgBox "「たかさ」または「おもさ」が0以下です。", vbCritical + vbOKOnly, "入力不正通知"
Exit Sub
End If
Next NCheck
'InputBoxs2に要素(メンバ)のタイプ1とタイプ2がいずれも空白
If InputBoxs2.Item("タイプ1").Value = "" And InputBoxs2.Item("タイプ2").Value = "" Then
MsgBox "タイプ名が未入力です", vbCritical + vbOKOnly, "入力不正通知"
Exit Sub
'InputBoxs2に要素(メンバ)のタイプ1とタイプ2が同じ値である場合
ElseIf InputBoxs2.Item(1).Value = InputBoxs2.Item(2).Value Then
MsgBox "タイプ名が重複しています", vbCritical + vbOKOnly, "入力不正通知"
Exit Sub
'InputBoxs2に要素(メンバ)のタイプ2だけが入力されている場合
ElseIf InputBoxs2("タイプ1").Value = "" And InputBoxs2.Item(2).Value <> "" Then
MsgBox "タイプが1つである場合は、タイプ1に入力してください。", vbCritical + vbOKOnly, "入力不正通知"
Exit Sub
'それ以外(正常に入力された)
Else
With ThisWorkbook.Worksheets("サンプルデータリスト")
Dim i As Long: i = 2
Do Until .Cells(i, 1).Value = ""
i = i + 1
Loop
.Cells(i, 1) = .Range("A" & i).Row - 1
.Cells(i, 2) = 名前ボックス.Value
.Cells(i, 3) = 分類ボックス.Value
.Cells(i, 4) = 特性ボックス.Value
.Cells(i, 5) = 高さ入力ボックス.Value
.Cells(i, 6) = 重さ入力ボックス.Value
.Cells(i, 7) = タイプ1コンボ.Value
.Cells(i, 8) = タイプ2コンボ.Value
End With
MsgBox "入力が完了しました。", vbInformation + vbOKOnly, "入力完了通知"
End If
End Sub
Private Sub キャンセルボタン_Click()
'ユーザーフォームを閉じる
Unload サンプルフォーム
End Sub
03行目
ユーザーフォームを起動するしたときのプロシージャを作成します。UserForm_Initializeはユーザーフォームの初期状態を指定することができるプロシージャです。
06行目と07行目
コレクション(Collection)オブジェクトとしてTypesを宣言します。
10行目から27行目
Addメソッドを利用してコンボボックスの選択肢となる要素をTypesに追加しています。追加した項目をユーザーフォーム上のコンボボックスで選択できるようにする準備です。
(※詳細は以下に掲載している表”コレクション(Collection)オブジェクトTypesの内容”をご参照ください。)
31行目から34行目
ForNextステートメントでくり返しを指定しています。ユーザーフォーム上のコンボボックス(タイプ1コンボ)にTypesを追加していきます。こちらの処理により選択肢にTypesの要素が表示されるようになります。
33行目のコメントアウトを解除するとコンボボックス(タイプ2コンボ)に対しても同じ処理を実行しますが、これはサンプルマクロで後に紹介するForEachステートメントでそれぞれのくり返し書きかたを採用しているためです。
(※こちらのコードを利用される場合は、ForNextまたはForEachステートメントのいずれかを採用すると良いでしょう。)
38行目から41行目
ForEachステートメントでくり返しを指定しています。ユーザーフォーム上のコンボボックス(タイプ2コンボ)にTypesを追加していきます。こちらの処理により選択肢にTypesの要素が表示されるようになります。
39行目のコメントアウトを解除するとコンボボックス(タイプ1コンボ)に対しても同じ処理を実行しますが、これはサンプルマクロで先に紹介したForNextステートメントでそれぞれのくり返し書きかたを採用しているためです。
(※こちらのコードを利用される場合は、ForNextまたはForEachステートメントのいずれかを採用すると良いでしょう。)
45行目
ユーザーフォームをボタンをクリックしたときの処理を書いていくために、登録ボタン_Clickプロシージャを作成します。
48行目と49行目
コレクション(Collection)オブジェクトとしてInputBoxsとバリアント型の変数としてTBoxを宣言します。
52行目から58行目
Addメソッドを利用してコレクション(Collection)オブジェクトのInputBoxsに複数のテキストボックスを追加しています。
(※サンプルマクロではWithステートメントを利用して対象となるオブジェクトをまとめています。)
(※詳細は以下に掲載している表”コレクション(Collection)オブジェクトInputBoxsとInputBoxs2の内容”をご参照ください。)
61行目と62行目
コレクション(Collection)オブジェクトとしてInputBoxs2とバリアント型の変数としてCBoxを宣言します。
65行目と68行目
Addメソッドを利用してコレクション(Collection)オブジェクトのInputBoxs2に複数のコンボボックスを追加しています。
(※サンプルマクロではWithステートメントを利用して対象となるオブジェクトをまとめています。)
(※詳細は以下に掲載している表”コレクション(Collection)オブジェクトInputBoxsとInputBoxs2の内容”をご参照ください。)
71行目と76行目
ForEachステートメントをつかってくり返しを指定しています。コレクション(Collection)オブジェクトのInputBoxsに追加されたすべてのテキストボックスのうち、いずれかが空白であるかを判定して、空白である場合は特定のメッセージを表示をした後にマクロを終了します。
80行目と85行目
ForNextステートメントをつかってくり返しを指定しています。コレクション(Collection)オブジェクトのInputBoxsに追加されたすべてのテキストボックスのうち、たかさとおもさを入力するテキストボックスが0以下であるかを判定して、0以下の場合は特定のメッセージを表示をした後にマクロを終了します。
88行目から90行目
コレクション(Collection)オブジェクトとしてInputBoxs2の要素であるタイプ1コンボとタイプ2コンボのどちらも空白であった場合は特定のメッセージを表示した後にマクロを終了します。
93行目から95行目
コレクション(Collection)オブジェクトとしてInputBoxs2の要素であるタイプ1コンボとタイプ2コンボに入力された値がおなじものであった場合は特定のメッセージを表示した後にマクロを終了します。
98行目から100行目
コレクション(Collection)オブジェクトとしてInputBoxs2の要素であるタイプ1コンボが空白であり、タイプ2コンボが空白でなかった場合に特定のメッセージを表示した後にマクロを終了します。
108行目から110行目
エクセルのワークシートにある表の最終行を取得しています。DoLoopステートメントでＡ列が空白ではないときは変数iに1を加算することで空白行になるまで行を下にずらしていきます。
112行目から119行目
サンプルデータシートという名前のワークシートの各セルにユーザーフォームに入力された値を書きこむ処理です。
122行目
ワークシートへの入力が完了した旨のメッセージを表示します。
コレクション(Collection)オブジェクトTypesの内容
| コレクション名 | Index | Item | Key |
|---|---|---|---|
| Types | 1 | ノーマルタイプ | ノーマル |
| 2 | ほのおタイプ | ほのお | |
| 3 | みずタイプ | みず | |
| 4 | くさタイプ | くさ | |
| 5 | でんきタイプ | でんき | |
| 6 | こおりタイプ | こおり | |
| 7 | かくとうタイプ | かくとう | |
| 8 | どくタイプ | どく | |
| 9 | じめんタイプ | じめん | |
| 10 | ひこうタイプ | ひこう | |
| 11 | エスパータイプ | エスパー | |
| 12 | むしタイプ | むし | |
| 13 | いわタイプ | タイプ | |
| 14 | ゴーストタイプ | ゴースト | |
| 15 | ドラゴンタイプ | ドラゴン | |
| 16 | あくタイプ | あく | |
| 17 | はがねタイプ | はがね | |
| 18 | フェアリータイプ | フェアリー |
コレクション(Collection)オブジェクトInputBoxsとInputBoxs2の内容
| コレクション名 | 項目名 | コントロール種別 | オブジェクト名 |
|---|---|---|---|
| InputBoxs | なまえ | テキストボックス | 名前ボックス |
| ぶんるい | テキストボックス | 分類ボックス | |
| とくせい | テキストボックス | 特性ボックス | |
| たかさ | テキストボックス | 高さ入力ボックス | |
| おもさ | テキストボックス | 重さ入力ボックス | |
| InputBoxs2 | タイプ1 | コンボボックス | タイプ1コンボ |
| タイプ2 | コンボボックス | タイプ2コンボ |
VBAのコレクション(Collection)まとめ
この記事では、VBAのコレクションについて紹介しました。
コレクションは、オブジェクトをまとめられるオブジェクトです。コレクションでは数値や文字列、オブジェクトをまとめることができ、ひとつのかたまりとして扱うことでできます。
さいごにコレクションについてまとめておきます。
コレクションとはどういったものか。
配列(Array)や辞書(Dictionary)のようにデータやオブジェクトをまとめられるものです。エクセルに既に存在するWorkbooksコレクションや、Worksheetsコレクションのようにオブジェクトのかたまりのコレクションもありますが、これらと同じように、オリジナルでコレクションを作成できるものと考えておけばいいでしょう。
また、コレクション自体もオブジェクトのひとつです。
コレクションをつかうメリット
配列のようにリストのサイズを宣言しなくても要素(メンバ)の追加ができる
配列では、宣言のときに要素(メンバ)の数を宣言しておく必要がありますが、コレクションではAddメソッドをつかうことで簡単に要素(メンバ)が追加できます。
データの種類が異なるものであってもコレクションに追加ができる
配列では、宣言のときに要素(メンバ)の数を宣言しておく必要がありますが、コレクションではAddメソッドをつかうことで簡単に要素(メンバ)が追加できます。
Keyをつかって要素(メンバ)の取得ができる
辞書(Dictionary)と同じく、要素(メンバ)の登録時に指定した文字列であるKeyをつかって、要素(メンバ)の取り出しができます。コレクションに登録された要素の番号(Index)ではなく、Keyで要素(メンバ)にアクセスできることはプログラム全体の読みやすく、扱いやすいものとなります。
コレクションのつかいかた
マクロの中でコレクションをつかうためには、コレクション名を指定して事前に宣言する必要があります。
宣言するVBAコードの書きかたは2種類あります。コレクションにはプロパティやメソッドがあり、要素(メンバ)を数える、追加・出力・削除などの処理につかいます。
また、コレクションはオブジェクトとしてループ処理ができます。
コレクションオブジェクトは、くり返し文(ループ)である「For Each文」であつかえるようになり、要素(メンバ)に対してくり返す(ループ)処理を実行するマクロで利用できます。
コレクションのプロパティとメソッド
コレクションには以下のプロパティとメソッドが存在する。
Countプロパティは、コレクションに登録されている要素(メンバ)の数をかえす
Addメソッドは、コレクションに新たに要素(メンバ)を追加する
Itemメソッドは、コレクションに登録されている要素(メンバ)をかえす
Removeメソッドは、コレクションに登録されている要素(メンバ)を削除する
今回はここまで。
この記事があなたのVBA学習や職場での課題解決に役立つことがあれば幸いです。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント