EXCELにせよ、ACCESSにせよ、VBAを使用していると何らかのCollection(コレクション)オブジェクトに触れる機会がたびたびあります。
とても身近なオブジェクトでそれがCollectionであることすら気づかずに使用してしまっている方々も多数いらっしゃるのではないでしょうか。
「Collection(コレクション)って何?使い方?全然分からん!!」とさじを投げてしまう前に、少しこの記事にお付き合い頂けないでしょうか。
例えば、EXCELのWorkSheetsオブジェクト。
EXCELでVBAを使っている場合にはかなりの頻度で使用しているであろうこのWorkSheetsオブジェクトは、WorkSheetオブジェクトをグループ化したCollectionです。
Officeのいたるところで様々なオブジェクトがグループ化され定義済みCollectionとして管理されており、VBAを使ってOfficeを制御しようとした場合、何らかの形で定義済みCollectionにアプローチしていくことは日常茶飯事なことと言えます。
そんな身近なオブジェクトであるCollection(コレクション)ですが、VBAでは未定義のCollectionオブジェクトを作成して独自のデータを管理していくことが可能となっております。
使用できる未定義のCollectionオブジェクトをどのように使用していくかは発想次第ですが、シンプルな仕様なので正しく理解を深めて使用できるようになると、意外と便利なオブジェクトとして活躍してくれます。
今回は基本的な使い方から応用的な使い方までCollectionオブジェクトについて掘り下げて取り上げてみたいと思います。
Collection(コレクション)とは
Collection(コレクション)とは、配列とは異なるが複数のデータを格納することができるオブジェクトです。
格納する変数のデータ型を統一させる必要がなく簡単な記述でデータを格納していくことができるため、動的配列を必要とする場面でCollectionを代用する方もいらっしゃいます。
Collection(コレクション)は仕様上、キーとデータをセットで格納することができるため、Dictionary(連想配列)オブジェクトと同じような使い方も限定的ですがこなすことができます。
Collection(コレクション)はキーの重複を仕様上認めていないため、重複しないリストを作成することが可能であり、この点もDictionary(連想配列)と共通する点と言えます。
Collection(コレクション)はオブジェクトである利点としてプロパティやメソッドがサポートされており、配列を使用するよりも可読性の高いVBAコードを容易に記述することができます。
例えば、以下のような商品名と価格のリストをVBAの処理の中で使用したいとき・・・
商品名 | 価格 |
---|---|
りんご | 150 |
ぶどう | 390 |
バナナ | 180 |
オレンジ | 120 |
いちご | 500 |
メロン | 980 |
洋ナシ | 340 |
処理に使用するのであれば何らかのリストを作成する流れになると思いますが、まずは二次元配列あたりをイメージされる方が多いのではないでしょうか。
このような場面はCollection(コレクション)の得意分野と言えます。
具体的な説明は後ほどするとして、まずはCollectionを使用したサンプルをご覧ください。
Sub Collection_Sample001() Dim objCol As New Collection Dim arrList(6, 1) As Variant Dim arrShopping As Variant Dim v As Variant Dim i As Integer Dim intTotal As Integer '買い物リスト arrShopping = Array("りんご", "いちご", "オレンジ") '******************************************* '* 買い物リストより合計を算出(二次元配列) '******************************************* Debug.Print "[二次元配列]" '商品リスト作成 arrList(0, 0) = "りんご" arrList(0, 1) = 150 arrList(1, 0) = "ぶどう" arrList(1, 1) = 390 arrList(2, 0) = "バナナ" arrList(2, 1) = 180 arrList(3, 0) = "オレンジ" arrList(3, 1) = 120 arrList(4, 0) = "いちご" arrList(4, 1) = 500 arrList(5, 0) = "メロン" arrList(5, 1) = 980 arrList(6, 0) = "洋ナシ" arrList(6, 1) = 340 intTotal = 0 For Each v In arrShopping For i = LBound(arrList) To UBound(arrList) If arrList(i, 0) = v Then '買い物リストをデバッグ出力 Debug.Print v & " => " & arrList(i, 1) & " 円" '価格を合算 intTotal = intTotal + arrList(i, 1) Exit For End If Next Next '買い物の合計をデバッグ出力(二次元配列) Debug.Print "======================" Debug.Print "合計 " & intTotal & " 円" & vbCrLf '******************************************* '* 買い物リストより合計を算出(Collection) '******************************************* Debug.Print "[Collection(コレクション)]" '商品リスト作成(Collection) objCol.Add Key:="りんご", Item:=150 objCol.Add Key:="ぶどう", Item:=390 objCol.Add Key:="バナナ", Item:=180 objCol.Add Key:="オレンジ", Item:=120 objCol.Add Key:="いちご", Item:=500 objCol.Add Key:="メロン", Item:=980 objCol.Add Key:="洋ナシ", Item:=340 intTotal = 0 For Each v In arrShopping '買い物リストをデバッグ出力 Debug.Print v & " => " & objCol(v) & " 円" '価格を合算 intTotal = intTotal + objCol(v) Next '買い物の合計をデバッグ出力(Collection) Debug.Print "======================" Debug.Print "合計 " & intTotal & " 円" Set objCol = Nothing End Sub
上の例では買い物リストをもとに商品名や価格を出力させながら、最終的に合算値を出力するロジックのサンプルになります。
二次元配列ベースの処理とCollection(コレクション)ベースの処理を見比べて頂くことができますが、率直なところどう感じられましたでしょうか。
難解な条件ではないのでどちらもそれほど難しくないように見えますが、ネストの深さや記述量などを比較すると可読性の観点ではCollection(コレクション)に軍配が上がっていると言えるでしょう。
特定のユニークなキーを名指しで格納して、キーを名指しでデータを引き出せる強みが活かせている処理です。
処理結果は以下の通りです。
処理速度も大切な要素ではありますが、コードを作成・管理・共有していくような環境では簡潔な記述・読み解きやすさも大変重要な要素です。
Collection(コレクション)のプロパティとメソッド
ここからはCollection(コレクション)のプロパティとメソッドの説明になります。
VBAで用意されているオブジェクトには必ず何らかのプロパティやメソッドがいずれか定義されており、このCollection(コレクション)オブジェクトも例外ではありません。
プロパティ
プロパティ | 説明 |
---|---|
Count | Collection(コレクション)に格納されたオブジェクト数 |
メソッド
メソッド | 説明 |
---|---|
Add | Collection(コレクション)にメンバーを追加します。 ※追加する要素をメンバーと呼びます。 |
Item | 格納済みメンバーから引数に対応するメンバーを返します。 |
Remove | 格納済みメンバーから引数に対応するメンバーを削除します。 |
Collection(コレクション)の基本的な使い方
プロパティやメソッドが非常にシンプルなCollection(コレクション)オブジェクトですが、宣言からプロパティやメソッドの具体的な使い方などついて触れていきたいと思います。
Collection(コレクション)の宣言
VBAで使用するCollection(コレクション)はVBAに組み込まれたオブジェクトであるため、そのまま参照設定不要で使用することが可能であり、これはメリットと言えます。
Sub Collection_Sample002() '変数宣言 Dim objCol1 As Collection 'オブジェクト生成 Set objCol1 = New Collection '同時に変数宣言+オブジェクト生成の記述でも問題ありません。 Dim objCol2 As New Collection End Sub
プロパティやメソッドを使った基本的なサンプル
ここではCollection(コレクション)の各種プロパティおよびメソッドを使用したサンプルを挙げてみたいと思います。
Sub Collection_Sample003() Dim objCol As New Collection Dim intTotal As Integer Dim i As Integer '商品リスト作成(Collection) objCol.Add Key:="りんご", Item:=150 objCol.Add Key:="ぶどう", Item:=390 objCol.Add Key:="バナナ", Item:=180 objCol.Add Key:="オレンジ", Item:=120 objCol.Add Key:="いちご", Item:=500 objCol.Add Key:="メロン", Item:=980 objCol.Add Key:="洋ナシ", Item:=340 '初期値1でメンバー数だけ繰り返す intTotal = 0 For i = 1 To objCol.Count '価格を合算 intTotal = intTotal + objCol.Item(i) Next Debug.Print "削除前:" & intTotal & " 円" 'Keyを指定してメンバーを削除 objCol.Remove "りんご" '初期値1でメンバー数だけ繰り返す intTotal = 0 For i = 1 To objCol.Count '価格を合算 intTotal = intTotal + objCol.Item(i) Next Debug.Print "削除後:" & intTotal & " 円" Set objCol = Nothing End Sub
サンプルの処理の解説
このサンプルにつきましては、上で挙げていた商品リストを再度使用した形になりますが、以下の処理を実施しております。
- Collection(コレクション)を宣言+オブジェクト生成
- 商品リストの商品名をキーに価格をメンバーに追加
- 繰り返し処理でメンバー内の価格を合算
- 合算値をデバッグ出力
- キー「りんご」に対応するメンバーを削除
- 繰り返し処理でメンバー内の価格を合算
- 合算値をデバッグ出力
- Collection(コレクション)を開放
一応、すべてのプロパティとメソッドを使用した処理となります。
結果は以下の通りです。
削除前後で「りんご」の150円が合算値の差分として確認することができます。
記述についての補足
Collection(コレクション)のメソッドの記述について補足していきたいと思います。
上のサンプルでは「引数名:=値」という形で記述していますが、記述方法は他にもあります。
'記述パターン① objCol.Add Key:="りんご", Item:=150 '記述パターン② objCol.Add 150, "りんご"
実はAddメソッドには引数が複数あるため、引数名を指定しない場合は Item を先に指定する必要があります。
これはVBAのCollection(コレクション)の仕様です。
ただその他の引数は任意となるため、指定がなければ設定する必要はありません。
なんとKeyも任意の扱いとなり省略可能です。
するとメンバー削除はどうすればいいのか?って話になりますよね。
実はRemoveメソッドの引数にもひとクセあります。
Removeメソッドの引数には、キー以外でインデックスを指定することもできる仕様です。
メンバーへのアプローチは仮にキーの設定がなくてもインデックスを指定することで特定のメンバーを指定することができるわけです。
'記述パターン① objCol.Remove "りんご" '記述パターン② objCol.Remove 1
Collection(コレクション)のインデックスは「1」から追加された順番に管理されています。
配列とは異なり、インデックス「0」はありません。
さらにメンバー追加の際に特定の位置にメンバーを追加することも可能です。
その場合のインデックスについては追加順の連番とはならず、指定位置以降のメンバーのインデックスが「1」加算されてインデックス全体の整合性を保ちます。
これは削除の場合も同様で特定のメンバーが削除された場合は以降のメンバーのインデックスが「1」減算される仕様です。
この点はメンバー追加・削除時のインデックスとキーの関係性を確認してみると面白いです。
Sub Collection_Sample004() Dim objCol As New Collection Dim arrList As Variant Dim i As Integer '************************************************************* '* Addメソッドの引数 '************************************************************* '* '* objCol.Add [Item], [Key], [before], [after] '* '* 第1引数[必須]:Item :追加するメンバー ※データ型は任意 '* 第2引数[任意]:key :メンバーを関連付けるキー ※文字列型 '* 第3引数[任意]:before:特定位置の前に追加する際に指定 '* 第4引数[任意]:after :特定位置の後に追加する際に指定 '************************************************************* '商品リストをメンバーに追加 objCol.Add Key:="りんご", Item:=Array("りんご", 150) objCol.Add Key:="バナナ", Item:=Array("バナナ", 180) objCol.Add Key:="いちご", Item:=Array("いちご", 500) objCol.Add Key:="メロン", Item:=Array("メロン", 980) objCol.Add Key:="洋ナシ", Item:=Array("洋ナシ", 340) '商品名「いちご」の前にメンバー追加@キー指定 objCol.Add Key:="オレンジ", Item:=Array("オレンジ", 120), Before:="いちご" '商品名「りんご」の前にメンバー追加@インデックス指定 objCol.Add Key:="ぶどう", Item:=Array("ぶどう", 390), After:=1 '初期値1でメンバー数だけ繰り返す For i = 1 To objCol.Count 'インデックス指定でメンバー(商品の価格)をデバッグ出力 Debug.Print i & " ::: " & objCol.Item(i)(0) & " ::: " & objCol.Item(i)(1) Next 'Collection(コレクション)の解放 Set objCol = Nothing End Sub
上のサンプルではメンバーにキーも確認できるように配列(商品名、価格)で格納してみました。
処理結果は以下の通りとなり、インデックスも想定する順番通りに管理されており、キー指定でもインデックス指定でもAddメソッドは機能していることが確認できます。
もちろんキー設定のされていないメンバーにはインデックス指定しか処理は実行できません。
最後に Item メソッドですが、記述方法は4通りです。
'記述パターン① objCol(1) '記述パターン② objCol.Item(1) '記述パターン③ ※要キー設定 objCol("りんご") '記述パターン④ ※要キー設定 objCol.Item("りんご")
各メソッドについて記述方法がいくつか存在するため、Collectionを使用したコードの記述および読解にはこれらを念頭に置いておくと、いろいろと対処が可能となると思います。
格納できるデータ型や仕様上の注意について
Collection(コレクション)のメンバー追加できるデータ型にはオブジェクト型も含まれているため、文字列型や数値型だけではなく配列(多次元配列も含む)やクラスのインスタンスを格納することができるのでかなり柔軟です。
Collectionの中にCollectionをメンバーとして追加していくこと(入れ子)も可能なので、データを細分化して管理することも実現可能です。
が、何事もやりすぎは禁物で管理データが細分化、肥大化した場合にはデータベース化の検討が必要なケースもありますのでご注意ください。
そのほかではVBAの構造体を格納するのは仕様上NG、しかし独自クラスで実現するならばOKなど。
「構造体か?クラスか?」問題はなるべくクラスで対応していく方が無難なケースが多いので、悩んだ場合は目を閉じてクラスを選択しましょう。
そして最大の注意事項があります。
Collection(コレクション)に追加したメンバーは更新することができません。
Addメソッド時に設定した引数の値をメンバー追加後に変更することはできません。
このあたりは Item がプロパティではなく、「取得するメソッド」であるため、「更新する」ことが対応できない仕様であるためです。
格納した値を更新したい場合には「削除」+「新規追加」で「更新」っぽく振る舞うことはできても、VBAのCollectionはオブジェクトとして更新機能がサポートされておりません。
そのため「データ更新ありき」で管理方法を選定する場合には、配列またはDictionary(連想配列)オブジェクトを検討する必要があります。
Collection(コレクション)の使いどころ
Collection(コレクション)の使いどころとしては、動的配列の代替手段としてよく使用されますが、更新ありきの用途にはやや不向きとなるため、「メンバー格納+参照」の使い方であればおすすめです。
またキーを設定することでメンバーへも素早くアプローチできるため、定数やオブジェクトを管理することにも一定の利用価値が見込めます。
使いどころを組み込んだサンプル
例えば以下のようなデータがあったとしましょう。
こちらは気象庁が発表しているどなたでもダウンロード可能な気象データになりますが、このデータを使用していろいろ処理をする必要があったとしましょう。
Sub Collection_Sample005() Dim oColHeader As New Collection Dim oColList As New Collection Dim oColRecord As Collection Dim arrHeader As Variant Dim arrList As Variant Dim oRange As Range Dim RowCount As Long Dim ColCount As Long Dim v As Variant Dim i As Integer '********************************************************** '* 期間最大値400(mm)以上の観測所番号のリストを作成したい '********************************************************** 'ヘッダー領域の値を2次元配列化 With ThisWorkbook.ActiveSheet arrHeader = .Range("A1").CurrentRegion.Rows(1).Value End With 'ヘッダー位置をCollection(コレクション)化 For i = LBound(arrHeader, 2) To UBound(arrHeader, 2) oColHeader.Add Key:=arrHeader(1, i), Item:=i Next i '明細データ領域の値を2次元配列化 With ThisWorkbook.ActiveSheet Set oRange = .Range("A1").CurrentRegion RowCount = oRange.Rows.Count - 1 ColCount = oRange.Columns.Count Set oRange = oRange.Offset(1, 0) Set oRange = oRange.Resize(RowCount, ColCount) arrList = oRange.Value End With '二次元配列の繰り返し処理 For i = LBound(arrList, 1) To UBound(arrList, 1) 'リスト条件:期間最大値(mm)が400以上 If arrList(i, oColHeader.Item("期間最大値(mm)")) >= 400 Then '明細データ格納用のCollection(コレクション)を作成 Set oColRecord = New Collection 'メンバー追加 With oColRecord .Add Key:="観測所番号", Item:=arrList(i, oColHeader.Item("観測所番号")) .Add Key:="地点", Item:=arrList(i, oColHeader.Item("地点")) .Add Key:="期間最大値(mm)", Item:=arrList(i, oColHeader.Item("期間最大値(mm)")) End With 'リスト用のCollection(コレクション)にoColRecordをメンバーとして追加 oColList.Add Key:=CStr(arrList(i, oColHeader.Item("観測所番号"))), Item:=oColRecord Set oColRecord = Nothing End If Next i 'リスト件数およびリスト内容をデバッグ出力 Debug.Print "対象件数:" & oColList.Count & " 件" Debug.Print "============================================" Debug.Print "観測所番号 ::: 地点 ::: 期間最大値(mm)" For Each v In oColList Debug.Print v.Item("観測所番号") & " ::: " & v.Item("地点") & " ::: " & v.Item("期間最大値(mm)") Next Set oColHeader = Nothing Set oColList = Nothing Set oRange = Nothing End Sub
この処理の補足
少し長い処理になりますが、それぞれ役割の異なる複数のCollection(コレクション)を使ったサンプルになりますが、以下の処理を実施しております。
- Collectionの宣言
- oColHeader(ヘッダー位置格納用):宣言+作成
- oColList(対象リスト格納用):宣言+作成
- oColRecord(明細行メンバー格納用):宣言のみ
- ヘッダー領域の値を2次元配列(arrHeader)に格納
- ヘッダー位置をCollection(oColHeader)に格納
- 明細データ領域の値を2次元配列(arrList)に格納
- 二次元配列(arrList)の繰り返し処理
- リスト条件:期間最大値(mm)が400以上
- 明細行メンバー格納用Collection(oColRecord)を作成(ループ毎)
- [観測所番号],[地点],[期間最大値(mm)]をメンバー追加
- 対象リスト格納用Collection(oColList)にoColRecordをメンバー追加
- Collection(oColRecord)を開放
- リスト件数およびリスト内容をデバッグ出力
- オブジェクトの開放
このような流れで処理が作られているのですが、いかがでしょうか。
EXCELのヘッダー位置をCollection(コレクション)で管理することはかなり便利です。
- EXCELの列ずれに強い
- インデックスでの直接指定だと分かりにくい
これだけでも可読性はかなり向上し、何がしたいのか直感的にも把握しやすいです。メンテナンス時に都度インデックスに問題がないか確認していく作業もキー指定になったことで負担軽減のメリットも見込めます。
業務でEXCELファイルを扱う場合、列ずれトラブルは環境次第で頻繁に発生するケースがあります。
しかしヘッダー位置を管理するコレクションを作成することで問題を回避することができます。
ただ問題もあります。
ヘッダーに同一項目名が設定されていた場合、Collectionのキーの重複違反に引っ掛かりエラーとなってしまうため、処理を実行することができなくなります。
あとキーの設定に全半角・大小文字を区別できず、あいまいに解釈してしまうため、ヘッダー項目が全半角・大小文字で区別している場合にもCollection(コレクション)だとキー違反となってしまいます。
メリット・デメリットを把握した上で適切に活用できれば貢献度の高い使い方となります。
次にロジック後半部分のCollectionの中にCollectionを使った手法ですが、格納する項目数が少ない場合は配列を使用する選択でも問題ないです。
管理する項目数が多くなってインデックスでの記述が煩雑になってしまう場合にはやはりキー指定でロジックの可読性を高める方向性も検討の余地が出てきます。
そのような状況に遭遇した場合にはひとつの考え方として参考にして頂けたらと思います。
Collection(コレクション)とDictionary(連想配列)の違い
Collection(コレクション)を使用しようか検討する場合、高確率でDictionary(連想配列)とどちらを使用するべきか悩んでしまうことがあります。
どちらも同じような作業を行えるがゆえの悩みとなるわけですが、具体的に比較していくことで見えてくることもあるのでまとめてみました。
比較項目 | Collection(コレクション) | Dictionary(連想配列) |
---|---|---|
参照設定 OR CreateObject | 不要 | 要 |
キー設定の必須 | 省略可 | 必須 |
キーの半全角・大小文字の区別 | 不可 | 可 |
キーの値更新 | 不可 | 可 |
キー検索 | メソッドなし | メソッドあり |
キー配列出力 | 不可 | 可 |
配列・オブジェクト格納 | 可 | 可 |
格納データの更新 | 不可 | 可 |
格納データの配列出力 | 不可 | 可 |
インデックス | あり | あり |
ざっと思いつく限りで挙げてみましたが、このほかにもあるかもしれません。
見比べるとCollection(コレクション)でできることのほとんどがDictionary(連想配列)で対応可能であり、その逆の見方からすればDictionary(連想配列)でできることでCollection(コレクション)で対応できない項目の方が目立つくらいです。
Dictionary(連想配列)の方がプロパティとメソッドが充実しており、比較してしまえばCollection(コレクション)の上位版のような存在に見えてしまうでしょう。
ただし以下の内容を意識する場合、Collection(コレクション)の優位性が高まります。
- 参照設定が不要
- キー設定が省略してデータ格納が可能
- 格納値の更新を想定していない
- ほかのツールへの移植時のメンテナンス性
適材適所と言えば聞こえはいいですが、本来そのオブジェクトが何を想定して生まれ、活躍するために存在するのかを突き詰めていけば見えてくる景色も変わってくるのではないでしょうか。
そういった意味ではOfficeで組み込まれている定義済みCollectionが大変参考になると思います。
Collection(コレクション)が苦手とする処理
Collection(コレクション)が苦手とする処理について、挙げるならば以下の2点でしょうか。
- 格納済みのデータを更新できない
- ForEachループは早いが、Forループは注意が必要
更新できないのは仕様のため「削除+新規追加」で対応するしか仕方がない話なのですが、ループ処理について言及しておく必要があります。
少ないデータ量を管理するCollection(コレクション)であれば、影響はほぼありませんが、データ量が増えるにことで処理速度に影響がでてきます。
Sub Collection_Sample006() Dim objCol As Collection Dim DataRow As Variant Dim StartTime As Double Dim tmp As String Dim v As Variant Dim i As Long For Each DataRow In Array(1, 10, 100, 1000, 10000, 100000) i = 0 StartTime = Timer Set objCol = New Collection Do objCol.Add "data_" & i, CStr(i + 1) i = i + 1 Loop Until i > DataRow Debug.Print "コレクション - 格納(" & DataRow & "):" & (Timer - StartTime) & "[秒]" StartTime = Timer For Each v In objCol tmp = v Next Debug.Print "コレクション - 参照(" & DataRow & ") - For Each:" & (Timer - StartTime) & "[秒]" StartTime = Timer For i = 1 To objCol.Count tmp = objCol.Item(i) Next Debug.Print "コレクション - 参照(" & DataRow & ") - For:" & (Timer - StartTime) & "[秒]" Debug.Print "==========================================" Set objCol = Nothing Next End Sub
上のサンプルはCollection(コレクション)の格納数を動的に変更させながら、格納数別のForEachループとForループの処理時間を計測するロジックです。
10万件を上限に処理を実行した結果が以下のとおりです。
当然PCスペックの違いもあるので参考値でとらえて頂ければと思います。
少ないデータ量だと問題ないのですが、だんだん処理速度に違いが発生しているのが確認できます。
上限が10万件で1分くらいでしょうか。Forループの処理速度の傾向から100万件のデータだとおそらくかなりの時間を必要とすることが容易に想像できます。
一方、ForEachループの処理速度は軽快です。10万件の段階で1秒も経過していないので100件もまったく問題ないでしょう。
データの規模と手法がマッチしていない場合は良い結果が得られなくなる可能性もあるので注意しましょう。
Collection(コレクション)のまとめ
さて今回はCollection(コレクション)について取り上げてみましたが、いかがでしたでしょうか。
かなりの長文になってしまったので、ここまでたどり着けた方が何人いるか心配ですが、たどり着けた方には「お疲れ様です」という言葉を贈りたいと思います。
クセのあるオブジェクトではありますが、全体的にはシンプルな仕様で使いどころや使い方を誤らなければとても恩恵の得られるオブジェクトなのでご興味のある方はぜひCollection(コレクション)をいじるところから始めてみて頂けたらと思います。
またCollection(コレクション)の面白い使い方などあればご紹介していきたいと思います。