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)や連想配列(Ｄ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行で書きます。
Dim コレクション名 As Collection Set コレクション名 = New Collection
また、以下のように1行で書くこともできます。
Dim コレクション名 As New Collection
いずれかを書くことでコレクション(Collection)の宣言ができました。
現時点では宣言しただけなので、実際に使っていくためにはもうすこしすることがあります。つまりコレクション(Collection)という箱はあるけど中身が入っていない状態です。
コレクション(Collection)を宣言すると自動メンバー表示ができるよ。
コレクション(Collection)が認識されると、コレクション名のあとに(.)ピリオドを入力すると、コレクションで実行できるプロパティやメソッドがメンバーとして選択項目として表示されるようになります。
それでは、次の章からコレクション(Collection)のメンバーであるプロパティやメソッドを紹介していきます。
VBAのコレクション(Collection)のプロパティ・メソッド
コレクション(Collection)オブジェクトのメンバーには、プロパティとメソッドがあります。
それぞれがどのような働きをするのか紹介していきます。
プロパティはオブジェクトの状態の取得、メソッドはオブジェクトに対する処理だよ。
VBAのコレクション(Collection)のCountプロパティ
Countプロパティは、コレクション(Collection)オブジェクトの要素(メンバ)の数を取得することができるプロパティです。
| プロパティ名 | 説明 |
|---|---|
| Count | Collection(コレクション)オブジェクトの要素(メンバ)の数が取得できます。出力される値は長整数型(Long)の値です。 |
使用方法
コレクション名.Count
※以下の例はコレクションを宣言しただけで、まだ何も代入されていない状態なので、Countプロパティをつかって要素(メンバ)の数を出力すると「0」が返ってきます。
使用例
Option Explicit
Sub test001()
Dim コレクション名 As Collection
Set コレクション名 = New Collection
Debug.Print コレクション名.Count'実行結果：0
End Sub
デバッグプリントなので、イミディエイトウィンドウに0が出力されます。
VBAのコレクション(Collection)のAddメソッド
Addメソッドは、コレクション(Collection)オブジェクトに要素(メンバ)の追加ができます。
| メソッド名 | 説明 |
|---|---|
| Add | Collection(コレクション)オブジェクトに要素(メンバ)を追加します。 |
なお、Addメソッドには以下の引数があります。
使用方法
コレクション名.Add Item,[Key],[before], [after]
※[]の引数は省略可能です。
引数について
| 引数名 | 必須Or省略可能 | 説明 |
|---|---|---|
| コレクション名 (Object) |
必須 | Collection(コレクション)オブジェクトを指定します。 |
| メンバ (Item) |
必須 | Collection(コレクション)オブジェクトに追加する要素(メンバ)を指定します。 Itemはほかの要素(メンバ)と同じ値を指定することができます。 |
| メンバの位置を表す文字列 (key) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を文字列で指定します。 省略した場合はKeyをつかって要素(メンバ)を返すことはできません。また、Keyはほかの要素(メンバ)と同じ値を指定することはできません。 |
| メンバの追加位置 (before) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を既に存在する要素(メンバ)を指定して、指定要素(メンバ)の直前に追加します。「●●の前に追加する」といったイメージです。 ※afterとあわせて指定することはできません。 |
| メンバの追加位置 (after) |
省略可能 | Collection(コレクション)オブジェクトに追加する要素(メンバ)の位置を既に存在する要素(メンバ)を指定して、指定要素(メンバ)の直後に追加します。「●●の後に追加する」といったイメージです。 ※beforeとあわせて指定することはできません。 |
使用例
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
コレクション(Collection)に要素(メンバ)を追加していくながれ
VBAのコレクション(Collection)のItemメソッド
Itemメソッドはコレクション(Collection)オブジェクトに要素(メンバ)を返します。
| メソッド名 | 説明 |
|---|---|
| Item | Collection(コレクション)オブジェクトの要素(メンバ)を返します。 返したい要素(メンバ)の、数値や文字列を指定します。 |
使用方法
コレクション名.Item(1)
コレクション名.Item(Key)
※数値や指定したKeyで要素(メンバ)を返します。追加時にKeyを指定していない要素(メンバ)はKeyをつかって値を返すことはできません。
使用例
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 集合場所(1) 'グラウンド
Debug.Print 集合場所("くもり") 'グラウンド
Debug.Print 集合場所(4) '自宅待機
Debug.Print 集合場所("たいふう") 'エラー(プロシージャの呼び出しまたは引数が不正)
End Sub
※コレクション(Collection)では、同じKeyの指定はできませんが、同じItemの指定は可能です。
VBAのコレクション(Collection)のRemoveメソッド
Removeメソッドはコレクション(Collection)オブジェクトに要素(メンバ)を削除します。
| メソッド名 | 説明 |
|---|---|
| Remove | Collection(コレクション)オブジェクトの要素(メンバ)を削除します。 削除したい要素(メンバ)の、数値や文字列を指定します。 |
引数について
| 引数名 | 必須Or省略可能 | 説明 |
|---|---|---|
| コレクション名 (Object) |
必須 | Collection(コレクション)オブジェクトを指定します。 |
| メンバの位置を表す数値または文字列 (Index) |
必須 | Collection(コレクション)オブジェクトに含まれる要素(メンバ)の位置や文字列(Key)を指定します。 ただし、要素(メンバ)の追加時にKeyを指定していない場合、文字列(Key)で指定することはできません。 ※エラーが発生します。 |
使用方法
コレクション名. Remove 1
コレクション名. Remove Key
※数値や指定したKeyで要素(メンバ)を指定します。追加時にKeyを指定していない要素(メンバ)はKeyをつかって指定することはできない。
使用例
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
Removeメソッドで要素(メンバ)を削除したときの動作
要素(メンバ)を削除したコレクションの状態の変化のようす
削除する前のコレクションの状態
先頭の要素(メンバ)を削除したあとのコレクションの状態
要素のIndex2番目が先頭につめられ、Index 6番目がなくなる。
この場合、6番目の要素(メンバ)を指定した場合はエラーが発生します。
以下のような結果になるわけではないことを理解しておきましょう。
先頭の要素(メンバ)が空白になるわけではない。
要素(メンバ)がつめられて、末尾の要素(メンバ)が空白になるわけではない。
中間の要素(メンバ)を削除したあとのコレクションの状態
Index 3番目のCが削除され、それまで４番目だったXの順番が詰められて3番目になります、結果、Index6番はなくなる。削除されたIndex 3番目が空白になるわけではない点に注意。
末尾の要素(メンバ)を削除したあとのコレクションの状態
Index 6番目はなくなる。(空白になるわけではない)
オンラインスクールで現役エンジニアのサポートがあるテックアカデミーがおすすめ。
スキマ時間に学べて仕事も保証。必ず副業、始められます。まずは無料でプログラミング体験
VBAのコレクション(Collection)のサンプルマクロ
コレクションをつかったサンプルマクロを紹介します。
ここではコレクションを宣言して、要素(メンバ)を出力するマクロを紹介したいと思います。
これまでのプロパティやメソッドの紹介で書いたサンプルマクロと異なるポイントととして、それぞれのプロパティやメソッドの処理ごとをまとめたプロシージャとして呼び出すマクロであること。もう一点は、ループ処理をつかってコレクションの中身をすべて出力する処理をしている点です。
For文をつかったループ処理は以前の記事で解説しています。
コレクションオブジェクトにするとFor Eachでの処理が利用できるようになります。For Eachの詳しい説明などについては以下の記事をご覧ください。
コレクション(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 キュウシュウ(i)
Next i
'Collectionの要素をすべて表示する(ForEach)
Dim Val As Variant
For Each Val In キュウシュウ
Debug.Print Val
Next Val
'ループ処理の実行結果はどちらも以下のとおりとなります。
'================
'大分県
'鹿児島県
'熊本県
'佐賀県
'長崎県
'福岡県
'宮崎県
'================
End Sub
それぞれのプロシージャの関係性と処理のながれのイメージ
VBAで書いたコードだと長文になりますが、それぞれのプロシージャの処理の内容は単純なものであり、中心となっているプロシージャから呼び出してつないでいるだけです。
プロシージャとはSub 〇〇 ～ End SubまでのVBAコードの単位のことだよ。
プロシージャごとの解説
コレクションテスト_宣言 プロシージャ
コレクション名「キュウシュウ」を宣言しています。宣言方法は①と②のいずれでも大丈夫です。そのあとはすべてCallステートメントをつかってほかのプロシージャを呼び出す処理をしています。
なお、Callステートメントをほかのプロシージャを呼び出すときに、引数としてコレクション「キュウシュウ」を指定することで、呼び出したプロシージャでもコレクションを利用できるようにしています。
コレクションテスト_Addプロシージャ
コレクション「キュウシュウ」に要素(メンバ)を追加する処理を実行します。
※ここではあえてコレクションの「キュウシュウ」に九州以外の都道府県を登録しています。
コレクションテスト_Countプロシージャ
コレクションのCountプロパティをつかってコレクション「キュウシュウ」の要素(メンバ)の数を取得して、デバッグプリントでイミディエイトウィンドウに出力しています。
コレクションテスト_Remove プロシージャ
コレクションのRemoveメソッドをつかってコレクション「キュウシュウ」の要素(メンバ)を削除する処理をします。ここでは、「コレクションテスト_Add」プロシージャで追加した要素(メンバ)のうち、「石川県」と「青森県」を削除しています。
コレクションテスト_Add2 プロシージャ
コレクションに改めて要素(メンバ)を追加していきます。
登録する要素(メンバ)は、「大分県」、「鹿児島県」、「宮崎県」の3つです。ここでは、引数のAddメソッドのbeforeとafterをつかって「大分県」を先頭に追加、「鹿児島県」を熊本の直前に追加、「宮崎県」をコレクションの末尾に追加しています。
コレクションテスト_値の表示 プロシージャ
要素(メンバ)の出力をしています。それぞれの要素(メンバ)の出力と、2種類のループ処理(For文)をつかってコレクションの要素(メンバ)をすべて出力しています。出力方法はすべてデバッグプリントなので、イミディエイトウィンドウに要素(メンバ)となる値を出力します。
ひとつずつのプロシージャが処理していることは単純だよね。
ユーザーフォームのコントロールをコレクション(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
プロシージャごとの解説
Private Sub UserForm_Initialize プロシージャ
ユーザーフォームを起動したときの初期化処理になります。ここでは、コンボボックスで選択する項目をコレクション(Collection)の要素(メンバ)として追加し、コンボボックス1とコンボボックス2でユーザーが選択できる項目にしています。
コンボボックスはテキストボックスとリストボックスが合わさったような機能をもち、ユーザーが任意に文字を入力することや、事前に準備した項目から選択ができるコントロールです。
コンボボックス1とコンボボックス2は、タイプを選択することを想定していますので、18種類のタイプより任意の項目が選択できるようになります。
| コレクション名 | Index | Item | Key |
|---|---|---|---|
| Types | 1 | ノーマルタイプ | ノーマル |
| 2 | ほのおタイプ | ほのお | |
| 3 | みずタイプ | みず | |
| 4 | くさタイプ | くさ | |
| 5 | でんきタイプ | でんき | |
| 6 | こおりタイプ | こおり | |
| 7 | かくとうタイプ | かくとう | |
| 8 | どくタイプ | どく | |
| 9 | じめんタイプ | じめん | |
| 10 | ひこうタイプ | ひこう | |
| 11 | エスパータイプ | エスパー | |
| 12 | むしタイプ | むし | |
| 13 | いわタイプ | タイプ | |
| 14 | ゴーストタイプ | ゴースト | |
| 15 | ドラゴンタイプ | ドラゴン | |
| 16 | あくタイプ | あく | |
| 17 | はがねタイプ | はがね | |
| 18 | フェアリータイプ | フェアリー |
Private Sub 登録ボタン_Click プロシージャ
作成したユーザーフォームの「登録」ボタンをクリックしたときの処理になります。
ここでは、「InputBoxs」と「InputBoxs2」という名前のコレクションを宣言しています。その後、テキストボックスの5つをInputBoxsコレクションに追加、コンボボックスの２つをInputBoxs2に追加します。
それぞれのコレクション(Collection)と追加した要素(メンバ)については、以下の表にまとめましたので参考にしてください。
| コレクション名 | 項目名 | コントロール名 | オブジェクト名 |
|---|---|---|---|
| InputBoxs | なまえ | テキストボックス | 名前ボックス |
| ぶんるい | テキストボックス | 分類ボックス | |
| とくせい | テキストボックス | 特性ボックス | |
| たかさ | テキストボックス | 高さ入力ボックス | |
| おもさ | テキストボックス | 重さ入力ボックス | |
| InputBoxs2 | タイプ1 | コンボボックス | タイプ1コンボ |
| タイプ2 | コンボボックス | タイプ2コンボ |
コレクション(Collection)のInputBoxsでは、すべてのテキストボックスが空白でないかをくり返し(ループ)処理をしています。処理内容は、InputBoxsの要素(メンバ)のすべてのテキストボックスが空白かどうかの判定であり、テキストボックスが空白であればエラーメッセージを返すものとなります。
それぞれのテキストボックスが空白であるかはIf文を利用しています。If文の使いかたについては別の記事で紹介していますので、詳しくは下記のリンクより記事をご覧になってください。
そして、この条件判定をコレクションの要素(メンバ)の数だけくり返すようにForEach文を使っています。
つづいて、たかさやおもさを入力するテキストボックスに入力された値が0以下であった場合の処理をIf文とForNextでくり返しています。
処理をしたいテキストボックスは、コレクションInputBoxsのすべての要素(メンバ)ではなく、Index4番と5番のみの範囲に限定してくり返しを指定しているところがポイントになります。
さらに、コレクションInputBoxs2では、タイプを入力するコンボボックス1とコンボボックス2のどちらも空白であった場合、入力された値が同じであった場合にエラー通知をします。
また、タイプが1つのみである場合で、コンボボックス2のみを入力した場合は、コンボボックス1のみを入力する旨のメッセージを表示します。
Private Sub キャンセルボタン_Click プロシージャ
作成したユーザーフォームの「キャンセル」ボタンをクリックしたときの処理になります。具体的にはクリックしたときの動作はユーザーフォームを閉じます。
在宅やPCで稼げる仕事をしていきたい！
日本最大のクラウドソーシング事業のスクールなので仕事や副業に直結した学びが得られる！
無料相談はコチラ
VBAのコレクション(Collection)まとめ
この記事では、VBAのコレクションについて紹介しました。
コレクションは、オブジェクトをまとめられるオブジェクトです。コレクションでは数値や文字列、オブジェクトをまとめることができ、ひとつのかたまりとして扱うことでできます。
さいごにコレクションについてまとめておきます。
コレクションとは
配列(Array)や辞書(Dictionary)のようにデータやオブジェクトをまとめられるものです。
エクセルに既に存在するWorkbooksコレクションや、Worksheetsコレクションのようにオブジェクトのかたまりのコレクションもありますが、これらと同じように、オリジナルでコレクションを作成できるものと考えておけばいいでしょう。
また、コレクション自体もオブジェクトのひとつです。
コレクションをつかうメリット
配列のようにリストのサイズを宣言しなくても要素(メンバ)の追加ができる
配列では、宣言のときに要素(メンバ)の数を宣言しておく必要がありますが、コレクションではAddメソッドをつかうことで簡単に要素(メンバ)が追加できます。
データの種類が異なるものであってもコレクションに追加ができる
配列では、宣言のときに要素(メンバ)の数を宣言しておく必要がありますが、コレクションではAddメソッドをつかうことで簡単に要素(メンバ)が追加できます。
Keyをつかって要素(メンバ)の取得ができる
辞書(Dictionary)と同じく、要素(メンバ)の登録時に指定した文字列であるKeyをつかって、要素(メンバ)の取り出しができます。コレクションに登録された要素の番号(Index)ではなく、Keyで要素(メンバ)にアクセスできることはプログラム全体の読みやすく、扱いやすいものとなります。
コレクションのつかいかた
マクロの中でコレクションをつかうためには、コレクション名を指定して事前に宣言する必要があります。
宣言するVBAコードの書きかたは2種類あります。コレクションにはプロパティやメソッドがあり、要素(メンバ)を数える、追加・出力・削除などの処理につかいます。
また、コレクションはオブジェクトとしてループ処理ができます。
コレクションオブジェクトは、くり返し文(ループ)である「For Each文」であつかえるようになり、要素(メンバ)に対してくり返す(ループ)処理を実行するマクロで利用できます。
コレクションのプロパティとメソッド
コレクションには以下のプロパティとメソッドが存在する。
Countプロパティは、コレクションに登録されている要素(メンバ)の数をかえす
Addメソッドは、コレクションに新たに要素(メンバ)を追加する
Itemメソッドは、コレクションに登録されている要素(メンバ)をかえす
Removeメソッドは、コレクションに登録されている要素(メンバ)を削除する
今回はここまで。
この記事があなたのVBA学習や職場での課題解決に役立つことがあれば幸いです。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント