VBAでCollectionを使って処理をする場合に、このような局面に遭遇したことはあるでしょうか。
- EXCELのワークシート上のデータが対象である
- ワークシート関数では実現するには限界がありVBAを使う必要がある
- VBAでCollectionを使いたい
- セルの範囲を指定してデータ抽出した場合、二次元配列になってしまう
- なんとか二次元配列からCollectionに変換しなければならない
- 実行したい処理は完了したが、結果はセルに張り付けたい
- 今度は逆にCollectionから二次元配列に変換しなければならない
- 二次元配列をセルに張り付ける
・・・いやぁこの流れは面倒だ。別の方法を考えるか。
VBAでシンプルな処理を求めようとした場合、「変換して、またそれを戻して」みたいな処理は難解なややこしい処理になってしまうんじゃないかと警戒しちゃいますよね。
たしかに何らかのメソッドが変換機能をサポートしている場合はまったく問題ないのですが、サポートされていない場合は「自らロジックを組んで問題を解決するか」「別のやり方を模索するか」のどちらかになってしまうでしょう。
今回のテーマは「Collectionと２次元配列の相互変換」です。
つまりは「自らロジックを組んで問題を解決する」を選択したケースですね。
「自らロジックを組んで問題を解決するか」「別のやり方を模索するか」でお悩みの方は結論を出す前にぜひご一読ください。
２次元配列をCollectionに変換する
まず２次元配列をCollectionに変換する前に最初に決めておく重要なことがあります。
「２次元配列をCollectionに変換して何がしたいの？」と。
なぜなら、、、この回答次第で採用する変換方法が変わってきてしまうからです。
ただ闇雲にCollection化してみても、用途に合わないメンバーでCollection化してしまっては後続の処理でうまく活用することができず、全然意味を成さなくなってしまうわけです。
え？まったく意味を成さないってどういうこと？
例えば２次元配列を何も考えずにシンプルにCollectionの変換してみるとしましょう。
前提としてEXCELのRangeオブジェクトのValueプロパティでワークシートの値を２次元配列に出力していて、その２次元配列を引数にCollectionへ変換する関数を作成してみたいと思います。
'***********************************************
'* ２次元配列からCollectionへのシンプルな変換
'***********************************************
Function ArrayToCollection(ByVal arrCell As Variant) As Collection
Dim oCol As New Collection
Dim i As Integer
Dim j As Integer
'行方向のループ
For i = LBound(arrCell, 1) To UBound(arrCell, 1)
'列方向のループ
For j = LBound(arrCell, 2) To UBound(arrCell, 2)
'Collectionへメンバー追加
oCol.Add arrCell(i, j)
Next
Next
Set ArrayToCollection = oCol
End Function
いかがでしょうか。２次元配列をForループでぐるぐる回して配列の値をCollectionのメンバーに追加していくシンプルな関数になります。
これに何か問題があるかと聞かれれば、確かに２次元配列をCollectionに変換しているためこれはこれで問題はありません。
問題はありませんが、考えなければならないのはこの後の処理についてです。
上のサンプルで２次元配列は確かにCollectionへ変換されましたが、この後このCollectionでどんな処理をしたいのか？
何の考慮もないシンプルなCollectionへの変換で後続の処理は成立するのか？
- 行単位で何か処理したいのか？
- 列単位で何か処理したいのか？
- 行列関係なくすべての値に対して何か処理をしたいのか？
- 同じ定義の２次元配列へ戻したいのか？
- メンバー追加に応じた配列の定義変更も想定内か？
などと、この他にもいろいろ考えを巡らせておかなければ用途に合致したCollectionを作成していくことは厳しくなってしまうかもしれません。
上に挙げたシンプルな関数は何も考えずに配列の値をどんどんメンバー追加していっているため、Collectionの中で２次元配列のような行列の関係性は維持できていません。
そうなってくると行単位でとか列単位で何かすること自体が困難になってきてしまうし、２次元配列に戻すことはさらに難しくなってくるでしょう。
RangeオブジェクトからRowとColumnプロパティを予め取得して、メンバーをカウントしながら泥臭く処理を作っていくのも可能ではありますが、あまりおすすめはしません。
単一の行単位、列単位のセル範囲の２次元配列にして行列を考慮せずカバーしていく方法ももちろんあります。
しかしどうあっても泥臭くなってしまう処理ならば仕方がない部分もあるかもしれませんが、たいていの場合はCollection作成時の考慮もれが原因である可能性に目を向けてみましょう。
少しの考慮で最適なCollection化を意識して変換させていくことで問題を解決できる可能性があるわけです。
ArrayToCollection（行列対応版）
'************************************************
'* ２次元配列からCollectionへの変換（行列対応版）
'************************************************
Function ArrayToCollection(ByVal arrCell As Variant) As Collection
Dim oCol_Row As New Collection
Dim oCol_Column As Collection
Dim i As Integer
Dim j As Integer
'行方向のループ
For i = LBound(arrCell, 1) To UBound(arrCell, 1)
'各列の値をCollection化するオブジェクト生成
Set oCol_Column = New Collection
'列方向のループ
For j = LBound(arrCell, 2) To UBound(arrCell, 2)
'各列の値をメンバー追加
oCol_Column.Add arrCell(i, j)
Next
'コレクション（列）をコレクション（行）へメンバー追加
oCol_Row.Add oCol_Column
Next
'コレクション（行）を戻り値とする
Set ArrayToCollection = oCol_Row
End Function
ロジックの補足
このロジックは２次元配列の行と列の関係性を維持した仕様になっています。
ループ処理により行単位で列の各項目をメンバー追加したCollectionを作成し、メインのCollectionに行単位のCollectionをメンバーとしてどんどん追加していくロジックになります。
※俗にいう「Collectionの入れ子」ってヤツですね。
例えばこのようなデータがあったとします。
ひらがなの５０音を入力したデータですが、この内容を２次元配列にしてCollectionへ変換するまでの流れをコードで組んでみたいと思います。
Sub GetHiragana_Sample()
Dim oCol As Collection
Dim tmp As String
Dim v As Variant
Dim i As Integer
Dim j As Integer
'セル範囲の値を２次元配列として出力
v = ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Value
'２次元配列をCollectionへ変換
Set oCol = ArrayToCollection(v)
'Collectionをループ
For i = 1 To oCol.Count
'出力用変数を初期化
tmp = vbNullString
'入れ子のCollectionをループ
For j = 1 To oCol(i).Count
'出力用変数へメンバーを格納＠区切り文字付きで
tmp = tmp & """" & oCol(i)(j) & ""","
Next
'出力用変数をデバッグ出力
Debug.Print Mid(tmp, 1, Len(tmp) - Len(","))
Next
End Sub
セル範囲の値を２次元配列化した上で、Collection変換までは今までの説明で挙げてきましたが、実際はこのような雰囲気です。
２次元配列からCollection変換はたったの２行で終わっていますね（汗）。
変換後のCollectionのループについてはCollection内のメンバーがCollectionである入れ子構造で定義されているため、直感的には２次元配列をループしている雰囲気ととてもよく似ています。
インデックス指定の箇所は少し違いますが、行と列の関係を記述してループで回している様子はとても把握しやすい雰囲気かと思います。
厳密にいえばインデックスの開始が「1」から始まるCollectionに対し配列は「0」からなど細かい話は今回は省いています。あくまで直感的な話です。
結果は以下のとおりです。
行と列の関係がしっかりと維持されているため、デバッグ出力する際にセル範囲の値の配置の様子と同様な並びで値を出力することが可能となります。
また行も列もそれぞれCollectionで管理されているため、メンバーを追加していくことでデータを拡張させていくことも可能です。
ただしCollectionは値の更新が仕様上対応できていないため、更新の用途が想定されている場合は、「削除」＋「追加」の２段階の処理で「更新」っぽく処理することは可能です。
またCollectionの上位版のようなDictionaryというオブジェクトがあり、メソッドやプロパティが充実しており「更新」にも対応しているため、使い方次第で検討の余地があります。
しかしDictionaryでは格納時にKeyの設定が必須のため、Collectionとは勝手が違うのでその点のロジック上の対策は必要になるかと思います。
Collectionを２次元配列に変換する
さて、ここからはCollectionを二次元配列に変換する方法について考えていきたいと思います。
すでに述べていますが、Collectionに行列の関係性が保持されていれば対象となるCollectionを読解していくことで２次元配列に変換していく「やりやすさ」はあると思います。
なぜならば「配列を定義しやすいから」です。
Collectionを１次元配列へ変換する場合は、Collectionのメンバー数に合わせて配列の要素数を調整することで単純にCollectionのメンバーを配列にどんどん格納していけば良いだけなので、そこまで難しい話ではありません。
少しサンプルを挙げてみましょう。
'************************************************
'* Collectionから１次元配列への変換
'************************************************
Sub CollectionToArray_Sample()
Dim oCol As New Collection
Dim vArray As Variant
Dim i As Integer
With oCol
.Add "あ"
.Add "い"
.Add "う"
.Add "え"
.Add "お"
End With
ReDim vArray(oCol.Count - 1)
For i = 1 To oCol.Count
vArray(i - 1) = oCol(i)
Next
For i = LBound(vArray) To UBound(vArray)
Debug.Print vArray(i)
Next
End Sub
いかがでしょうか。もともとCollectionに行列の概念はないのでシンプルに配列に変換したい場合には、このサンプルのように単純にCollectionのメンバー数から配列を定義して単純なループ格納で配列への変換は成立するでしょう。
このサンプルではその後にデバッグ出力しています。結果は以下のとおりです。
「Collectionをただ配列に変換したい」というだけであればこれだけでいいと思います。
これが「Collectionを２次元配列に変換したい」となった場合には大変なわけです。
- Collectionを２次元配列に変換したい
- ２次元配列に変換した後、EXCELのシートに貼り付けたい
これらの要件を満たすためには、Collectionのメンバー群に行と列の概念を割り当てなくてはならなくなるからです。
まず１次元配列ではEXCELのシートへ貼り付けることは、単一セル毎に値を貼りつけていくのであれば可能ですが、「一括貼りつけ」を実現しようとする場合には２次元配列でデータを作成しないとEXCELのシートへ貼りつけることは仕様上実現することはできません。
わざわざセルの値をVBAで処理するロジックを検討するほどならば、最後は「一括貼りつけ」でパスっと終わらせたいと考える方が自然なのでないでしょうか。
そうなるとやはり配列は必然的に２次元配列への変換が必要になってくることになります。
そうなった場合、２パターンあります。
- Collectionから２次元配列へ直接変換する
- Collectionから１次元配列へ変換後、２次元配列に変換する
本来、Collectionはメンバーを動的に追加していくことが可能なオブジェクトであり、メンバー追加の容易さから動的配列の代替手段して扱われることもしばしばあります。
ただやはり行とか列の概念はあくまで配列の２次元的な考え方なわけで。
Collectionからの変換はまず１次元配列に変換した上で、その配列を２次元に変換する方法の方が処理を明確に切り分けられるため、考え方としてはそちらが自然なのではないかなと思っています。
ただし今回は「Collectionと2次元配列の相互変換」がテーマなのでCollectionを直接変換する方向で話を進めていきたいと思います。
前提としてCollectionに行と列の構成でデータを解釈できるか否かで変換方法が変わってきます。
例えば上で挙げた「Collectionを１次元配列に変換するサンプル」のように何も考慮されていないCollectionを２次元配列に変換したい場合、Collectionのメンバーを２次元（行と列）で切り分けていく必要があります。
イメージは以下のような感じですね。
イメージで見ても１次元配列の変換の方が容易であることがお分かりかと思います。
Collectionのメンバーに対してインデックスを数え、２次元配列の定義を算出して配列に値を格納していく流れになります。
EXCELのセル範囲のデータからCollectionを作成した場合は、Collectionのメンバー数とセル範囲の列数を割り算することで行数を算出することができます。
このようにCollectionに行列の関係性を読解できる情報がない場合には、用途に合致する２次元配列の定義を算出し、Collectionのメンバーを格納していくことになります。
'************************************************
'* Collectionから２次元配列への変換
'************************************************
Function CollectionToArray(ByVal oCol As Collection, ByVal intSplit As Integer) As Variant
Dim intIndex As Integer
Dim intCount As Integer
Dim intRow As Integer
Dim i As Integer
Dim j As Integer
Dim v As Variant
'Collectionのメンバー数
intCount = oCol.Count
'メンバー数が「0」ならば処理抜け
If intCount = 0 Then Exit Function
'Collectionのメンバー数と列数を除算（切り捨て）し、行数を算出
intRow = Int(intCount / intSplit)
'Collectionのメンバー数と列数を除算のあまりを算出し、行数を微調整
intRow = intRow + IIf(intCount Mod intSplit = 0, 0, 1)
'格納用の２次元配列を定義
ReDim v(1 To intRow, 1 To intSplit)
intIndex = 1
'２次元配列をループ
For i = LBound(v, 1) To UBound(v, 1)
For j = LBound(v, 2) To UBound(v, 2)
'カウンター変数がメンバー数に到達するまで
If intIndex <= intCount Then
'２次元配列に値を格納する
v(i, j) = oCol(intIndex)
'カウンター変数をインクリメント
intIndex = intIndex + 1
End If
Next
Next
'２次元配列を戻り値に設定する
CollectionToArray = v
End Function
Collectionのメンバーを等間隔で分割していくことで行と列の関係を見出すやり方です。
差分があった場合は最終行で発生するイメージになります。
これもひとつのやり方です。二次元配列としてしっかり定義できているわけですから。
ロジック作成者が実現したい処理がこの配列で要件を満たしていれば問題解決かもしれません。
２次元配列化はできているんですが、いざEXCELへ貼りつけたとしましょう。
シート上のA列やB列などの各セルの値は想定通りの値が設定されているでしょうか。
先ほどひらがな５０音を例にしたので、今度はカタカナ５０音でいきますか。
５０音のカタカナをコレクションにメンバー追加した上で、先ほどの２次元配列への変換ロジックを実行してみたいと思います。
Sub CollectionToArray_Sample()
Dim oCol As New Collection
Dim v As Variant
With oCol
.Add "ア": .Add "イ": .Add "ウ": .Add "エ": .Add "オ"
.Add "カ": .Add "キ": .Add "ク": .Add "ケ": .Add "コ"
.Add "サ": .Add "シ": .Add "ス": .Add "セ": .Add "ソ"
.Add "タ": .Add "チ": .Add "ツ": .Add "テ": .Add "ト"
.Add "ナ": .Add "ニ": .Add "ヌ": .Add "ネ": .Add "ノ"
.Add "ハ": .Add "ヒ": .Add "フ": .Add "ヘ": .Add "ホ"
.Add "マ": .Add "ミ": .Add "ム": .Add "メ": .Add "モ"
.Add "ヤ": .Add "ユ": .Add "ヨ"
.Add "ラ": .Add "リ": .Add "ル": .Add "レ": .Add "ロ"
.Add "ワ": .Add "ヲ": .Add "ン"
End With
v = CollectionToArray(oCol, 5)
Application.ActiveSheet.Range("A1").Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub
記述としてはこのような感じになります。Collectionへメンバー追加はやたら縦長になるのでマルチステートメントで記述しています。
２次元配列へ変換後はA1セルを基準に２次元配列を貼りつけた処理となっていますが、処理は以下のとおりです。
２次元配列としてはしっかり貼りつけられているのですが、並びとしてはいかがでしょう。
「ヤ、ユ、ヨ」の後ろに「ラ」が始まってしまっているのが確認できます。
配列の要素に対してびっしり埋めていく処理となるため、動作はこれで正しいのですが想定の並びの上では崩れてしまっていますよね。
もちろん空文字をメンバーに追加していくことで問題を解消していくことは可能ですが、あくまで５０音の例です。
仕事で大量のデータで似たような局面に遭遇した時など、あらゆる想定で空文字を都度補完していくことはかなり厳しいことになり現実的ではないでしょう。
そうなってくると疑似的に「行・列」とみなせる構成でCollectionを作ることでこの点を解決させることが出来ます。
CollectionToArray（行列対応版）
'************************************************
'* Collectionから２次元配列への変換（行列対応版）
'************************************************
Function CollectionToArray(ByVal oCol As Collection) As Variant
Dim intCol As Integer
Dim intRow As Integer
Dim v As Variant
Dim i As Integer
Dim j As Integer
'行数を取得（メインCollectionのメンバー数）
intRow = oCol.Count
'列数を取得（入れ子のCollectionの最大メンバー数）
For Each v In oCol
intCol = IIf(v.Count > intCol, v.Count, intCol)
Next
'戻り値となる二次元配列を定義
ReDim arrCell(1 To intRow, 1 To intCol)
'Collectionをループ
For i = 1 To oCol.Count
'入れ子のCollectionをループ
For j = 1 To oCol(i).Count
'二次元配列にCollectionのメンバーを
arrCell(i, j) = oCol(i)(j)
Next
Next
'２次元配列を戻り値に設定する
CollectionToArray = arrCell
End Function
ロジックの補足
これは「ArrayToCollection（行列対応版）」にて解説したロジックで戻り値となったCollectionを二次元配列に変換するロジックになります。
入れ子構造になったCollectionになりますが、行数と列数を容易に算出し、２次元配列を定義することができます。
その後は定義済みの２次元配列へ値を格納していく流れになります。
この「ArrayToCollection（行列対応版）」と「CollectionToArray（行列対応版）」のメリットは、Collectionに格納後、メンバー追加・削除などでinputとoutputのレイアウトが変更された場合でも２次元の入れ子構造自体が崩れなければ、ほぼほぼ機能してくれる点です。
もちろん粗を探そうと思えば指摘できる点はあると思いますが。
例えば行単位の合算値、平均値をメンバー追加など、inputでは含まれていない情報をCollection内で算出し、outputレイアウトとして「CollectionToArray（行列対応版）」に引数に設定する。
「CollectionToArray（行列対応版）」のロジック内でCollection内の入れ子構造から行列構造を解釈して２次元配列として戻してくれます。
そもそも合算値、平均値くらい２次元配列内で解決できそうな話ですが、、、（汗）
まとめ
最後に今回のまとめになりますが、以下のようなデータがあったとします。
この５０音のカタカナをひらがなに変換するロジックを解説することでまとめにさせて頂きます。
「ArrayToCollection（行列対応版）」と「CollectionToArray（行列対応版）」を使用します。
Sub Katakana_Convert_Hiragana()
Dim oCol As Collection
Dim oCol_Column As Collection
Dim v As Variant
Dim i As Integer
Dim j As Integer
'セル範囲の値を２次元配列として出力
v = ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Value
'２次元配列をCollectionへ変換
Set oCol = ArrayToCollection(v)
'メインCollectionをループ
For i = 1 To oCol.Count
'差し替え用入れ子Collectionを生成
Set oCol_Column = New Collection
'もともとの入れ子のCollectionをループ
For j = 1 To oCol(i).Count
'カタカナ→ひらがな変換しながら差し替え用Collectionへメンバー追加
oCol_Column.Add IIf(Len(oCol(i)(j)) > 0, oCol(i)(j) & "→" & StrConv(oCol(i)(j), vbHiragana), oCol(i)(j))
Next
'もともとの入れ子Collectionを削除
oCol.Remove i
'差し替え用入れ子Collectionを追加
Select Case True
Case i <= oCol.Count
oCol.Add oCol_Column, , i
Case Else
oCol.Add oCol_Column
End Select
Next
'Collectionを２次元配列へ変換
v = CollectionToArray(oCol)
'２次元配列をセル範囲の値として出力
Application.ActiveSheet.Range("G1").Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub
ここでの処理はおおまかに以下のとおりです。
- セル範囲の値を２次元配列に格納
- 「ArrayToCollection（行列対応版）」にてCollection化
- Collectionをループ処理
- 差し替え用の入れ子Collectionを生成
- ひらがな変換した値を差し替え用の入れ子Collectionへメンバー追加
- もともとの入れ子Collectionを削除
- 差し替え用の入れ子Collectionをインデックス指定でメンバー追加
- 「CollectionToArray（行列対応版）」にて２次元配列化
- セル範囲の値に２次元配列を反映
言葉にするとこんな感じですが、何か分かりにくいですかね（汗）
今回のひらがな変換サンプルではCollectionの仕様（値の編集ができない）を網羅した形で作成してみました。
Collectionの値の更新は仕様上できないため、削除＋追加という流れになりますので、追加時に追加する位置などを指定して追加していくことで差し替えを進めることが出来ます。
ちなみに結果は以下のとおり。
厳密に「ArrayToCollection（行列対応版）」と「CollectionToArray（行列対応版）」だけの箇所を部分抽出すれば以下のとおりです。
Sub Array_Collection_Convert()
Dim oCol As Collection
Dim v As Variant
'セル範囲の値を２次元配列として出力
v = ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Value
'２次元配列をCollectionへ変換
Set oCol = ArrayToCollection(v)
'<< ここでCollectionで何かやりたい処理を記述 >>
'Collectionを２次元配列へ変換
v = CollectionToArray(oCol)
'２次元配列をセル範囲の値として出力
Application.ActiveSheet.Range("G1").Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub
Collectionと２次元配列の相互変換ロジックが確立されていれば、ロジックを追記する記述箇所も限定的になるので、シンプルで可読性も見込めます。
いかがだったでしょうか。
- EXCELのワークシート上のデータが対象である
- ワークシート関数では実現するには限界がありVBAを使う必要がある
- VBAでCollectionを使いたい
- セルの範囲を指定してデータ抽出した場合、二次元配列になってしまう
- なんとか二次元配列からCollectionに変換しなければならない
- 実行したい処理は完了したが、結果はセルに張り付けたい
- 今度は逆にCollectionから二次元配列に変換しなければならない
- 二次元配列をセルに張り付ける
なかなか合致する悩みをお持ちの方々は少ないかもしれませんが（汗）
多少なり参考になれば幸いです。
