※ この記事では配列やコレクションの類いを「データ構造」と呼んでいます
VBAでは複数の要素をまとめて扱う方法が少なく機能的にも貧弱なため、「迷ったらコレ」と言えるものがありません。
自分なりにどのような特徴があるのか、どんな場面で使うのかを簡単にまとめておきます。
配列
VBA・VBScriptともに使用できる基本的なデータ構造。
メモリ上にまとまった領域を確保し、その領域にデータを保管する。
SAFEARRAYという構造体でラップされているが、中身はC言語の配列に近いらしい。
メリット
- (適切に使用すれば)省メモリかつ最速。
- 型を指定できる。
- 型を指定できるため、Variantに格納できないユーザー定義型もまとめられる。
- VBAの
Join・Splitをはじめとして、MS OfficeのShapeRangeの取得・ADODB関係など、既存のさまざまなライブラリとの親和性が高い。
デメリット
- 先に必要な領域を確保する必要がある。
- 後からの大きさの変更も可能だが、記述が若干面倒であり処理としても重めとなる。
そのため、大きさの変更回数が多いと強みの速度を活かしきれない(数が少なければ誤差レベルではあるが)。
- 後からの大きさの変更も可能だが、記述が若干面倒であり処理としても重めとなる。
- 具象オブジェクト型の配列は中身の型が保証されない(→参考)。
- 中身が空(要素数が0)を表現しにくい(Windows APIが必要)。
- VBA標準機能でも一部の型は
(0 To -1)の配列を作成できる→参考。
- VBA標準機能でも一部の型は
その他
- 多くの言語と異なり、VBAでは値型のように振る舞う
(→値渡しや変数への代入でコピーが作成される)。 - For Eachで列挙可能。この時、配列の型にかかわらずループ変数には
Variant型変数しか使用できず、添え字アクセスに比べると低速となる。
For Eachであれば、多次元配列でも次元数を意識せずに列挙できるため、そういった面では有用。
使いどころ
- 予め要素数を想定できる場合。
- メリットを活かしやすいため。最大数がわかっていれば、大きめに確保してから縮める、という方法もある。
- ランダムアクセスが必要な場合。
- Excelのセル範囲への入出力時(1次元配列・2次元配列)。
- 値型を格納する場合(値の取得時にコピーが発生しないため)。
- ユーザー定義型を格納する場合。
- 格納した要素を後から書き換えたい場合。
使用方法
Array
Dim arr(0 To 2) As String
arr(0) = "a"
arr(1) = "b"
arr(2) = "c"
Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
参考:汎用処理
'配列の末尾に要素を追加する汎用処理。
'要素が数千個ぐらいなら実用範囲だが、あまりにも要素数が多い場合は他の方法を検討すること。
'引数
'refArray1D :末尾に要素を追加したい1次元配列。
'inValue :追加したい要素。
Public Sub ArrayAdd(ByRef refArray1D As Variant, inValue As Variant)
'配列を拡張する。
ReDim Preserve refArray1D(LBound(refArray1D) To UBound(refArray1D) + 1)
'末尾に要素を設定する。
'Let/Set の判定のため、まずは配列の型を確認する。
Dim elementType As VBA.VbVarType
elementType = VBA.Information.VarType(refArray1D) Xor vbArray
If elementType = vbObject Then
'オブジェクト型なら Set で OK。
Set refArray1D(UBound(refArray1D)) = inValue
ElseIf elementType = vbDataObject Then
Set refArray1D(UBound(refArray1D)) = inValue
ElseIf elementType = vbVariant Then
'Variant型なら inValue の型に合わせて Let/Set を判定。
If VBA.Information.IsObject(inValue) Then
Set refArray1D(UBound(refArray1D)) = inValue
ElseIf VBA.Information.VarType(inValue) = vbDataObject Then
Set refArray1D(UBound(refArray1D)) = inValue
Else
Let refArray1D(UBound(refArray1D)) = inValue
End If
Else
'それ以外は値型とみなす。
Let refArray1D(UBound(refArray1D)) = inValue
End If
End Sub
Sub SampleOfArrayAdd()
Dim arr() As Variant
ReDim arr(0 To 0)
arr(0) = 100
ArrayAdd arr, New VBA.Collection
ArrayAdd arr, Err
ArrayAdd arr, VBA.DateTime.Time
ArrayAdd arr, VBA.[_HiddenModule].Array(0, 1, 2)
ArrayAdd arr, VBA.Strings.Split("a b c")
Dim i As Long
For i = 1 To 10000
ArrayAdd arr, String$(10000, "a")
Next i
Stop
End Sub
VBA.Collection
VBAで使用可能なデータ構造(VBScriptでは使用不可)。
挙動などを見る限り中身は片方向連結リスト。
シーケンシャルアクセスしかしない、文字列など巨大な値型を入れない、VBScriptへの移植が必要ないのであれば一番無難と思われる。
メリット
- Addメソッドで容易に要素を追加可能。
デメリット
- 代入した要素の書き換えができない(除去→追加とする必要がある)。
- 添え字アクセス(Itemメソッド)が後ろの要素ほど低速。
(→参考：軽い気持ちでLinkedListを使ったら休出する羽目になった話 - Qiita) - VBScriptでは使用できない。
その他
- 自作クラスをFor Eachに対応させる場合の選択肢の一つ。
- PR：VBScriptでCollectionっぽく使えるクラス（中身は配列）→VBS用配列ラッパークラス。
使いどころ
- 格納したい要素の数を想定できない場合。
- オブジェクト型を格納する場合。
使用例
VBA.Collection
Dim col As VBA.Collection
Set col = New VBA.Collection
col.Add "a"
col.Add "b"
col.Add "c"
Dim iter As Variant
For Each iter In col
Debug.Print iter
Next iter
Scripting.Dictionary
キーと値をペアにして要素を格納するデータ構造。
いわゆる連想配列やハッシュテーブルと呼ばれるもの。
一般的なハッシュテーブルと異なる点としては
- キーには文字列以外も指定可能(ユーザー定義型、配列、LongLong型以外ならOK)。
- 要素を削除しない限り、追加した順番が維持される。
メリット
- 数字以外のものを使って要素にアクセスできる。
- キー・値の一覧を配列に変換可能。
デメリット
- 要素の格納に時間がかかる(他のデータ構造と比較した場合)。
- ローカルウィンドウから中身が確認できない(キーが列挙される)。
その他
-
Scripting.Dictionary自体はFor Eachに対応しているが、Implementsされた場合はその限りでは無い。 - For Each時のKeyの列挙速度は速いが、KeyからItemを取得するのに多少時間がかかる。
- 数万単位の要素数かつ、KeyとItemを両方列挙する場合は
Keys()・Items()で事前に配列化しておくと速度的に有利な場合もある(要検証)。
- 数万単位の要素数かつ、KeyとItemを両方列挙する場合は
-
VBA.Interaction.CallByName(dic, "_NewEnum", VbMethod)でIEnumVARIANTを取得できるため、自作クラスをFor Eachに対応させる場合に使うこともできる。
使いどころ
- 数字以外のものを使ってランダムアクセスしたい場合。
- 要素の参照回数が多い場合。
- 重複を排除したい場合。
使用例
Scripting.Dictionary
Dim dic As Object 'As Scripting.Dictionary
Set dic = VBA.Interaction.CreateObject("Scripting.Dictionary")
dic.Item(1) = "a"
dic.Item(2) = "b"
dic.Item(3) = "c"
Dim k As Variant
For Each k In dic
Debug.Print dic.Item(k)
Next k
ADODB.Recordset
データベースとの接続でよく使用されるオブジェクトだが、単独でも使用可能。
メモリ上における表のようなオブジェクトで、非常に多くの機能を持っている。
(200518書きかけ)
メリット
- ある程度型を指定して要素を格納できる
-
Scripting.Dictionaryを要素とするVBA.Collectionやユーザ定義型の配列に近い立ち位置
-
- 様々な形式に変換できる。
- ソートやフィルターなどの便利機能がある。
デメリット
- For Eachできない。
- 複雑な構成の要素を格納する際はフィールド名の管理が重要。
便利機能の紹介
| メンバー | 説明 |
|---|---|
Clone |
.Clone(adLockReadOnly)で読み取り専用のビューを作成可能。 |
Filter |
文字列で表現できるものに限られるが、絞り込みが可能。 |
Sort |
いくつか制約があるものの、複数のフィールドで優先順をつけて昇順・降順ソートが可能。 |
GetRows |
任意のフィールドを2次元配列に抽出可能。 |
GetString |
TSV・CSVに近い文字列へ変換。 |
Save |
ファイルに保存して永続化。 |
System.Collections.ArrayList
.NET Frameworkの ArrayList クラス (System.Collections)。
配列ベースのコレクション。
メリット
- ArrayListそのものの各種メソッド(Sortなど)を使用できる。
デメリット
- .NET Frameworkのバージョンによってはインスタンス不可。
参考：VBAから.Net Framework がつかえなくなった | RelaxTools Addin for Excel 2010/2013/2016 - .NET <=> COM のマーシャリングの関係か低速。
-
参照設定をしても入力補完が効かない
IListなど、インターフェイスの型にすることで、そのインターフェイスのメンバーについては補完される。
使いどころ
- ソートなどを手実装するのが面倒な場合。
- 私自身は各種ソート用の関数を作成しているため、使用頻度は少なめ(オブジェクト用マージソート - Qiita)。
使用例
System.Collections.ArrayList
Dim arrList As Object 'As mscorlib.ArrayList
Set arrList = VBA.CreateObject("System.Collections.ArrayList")
arrList.Add "a"
arrList.Add "b"
arrList.Add "c"
Dim iter As Variant
For Each iter In arrList
Debug.Print iter
Next iter
As_IList_IEnumerable
'参照設定：mscorlib.dll
Dim arrList As mscorlib.IList
Set arrList = VBA.CreateObject("System.Collections.ArrayList")
arrList.Add "a" '入力補完が効く
arrList.Add "b"
arrList.Add "c"
Dim iEum As mscorlib.IEnumerable
Set iEum = arrList
Dim iter As Variant
For Each iter In iEum
Debug.Print iter
Next iter
JScriptのArray
ScriptControlやこちらを使用することでVBAでもJScriptのArrayオブジェクトを使用可能。
現行JavaScriptのArrayに比べると機能は劣るが、push/pop、shift/unshift、sort当たりは使用可能。
後期のIEエンジンから取得すれば、mapなども使用できる。
メリット
- Arrayの各種メソッド(sortなど)を使用できる。
デメリット
- 入力補完が効かない。
- メンバー名は大文字小文字を区別するため、安定動作のためにはCallByNameを多用する必要がある。
使いどころ
- ソートなどを手実装するのが面倒な場合。
- JScriptの機能を使いたい場合のおまけ。
使用例
JScriptArray
'require JSFunc ( https://qiita.com/nukie_53/items/297e524bcc8e43f9b5d1 )
Dim jsArr As Object
Set jsArr = JSFunc("", "[]")(Empty)
Dim push As Object
Set push = JSFunc("a,v", "a.push(v)")
push jsArr, "a"
push jsArr, "b"
push jsArr, "c"
Dim i As Long
For i = 0 To VBA.CallByName(jsArr, "length", VbGet) - 1
Debug.Print VBA.CallByName(jsArr, CStr(i), VbGet)
Next i
Dim convetToCol As Object
Set convetToCol = JSFunc("a,c", _
"var l=a.length;" & _
"for(var i=0;i<l;i++)c.Add(a[i]);" & _
"return c", False)
Dim iter As Variant
For Each iter In convetToCol(jsArr, New VBA.Collection)
Debug.Print iter
Next iter
個人的まとめ（自分の場合）
純粋なデータ格納用途としては配列かVBA.Collection、それ以外は付随機能を使いたい場合に考慮する。
配列・VBA.Collectionの使い分けは、
値型かつ要素数がわかっている場合は配列。
オブジェクト型や要素数が不定の場合はVBA.Collection。
要素数が不定だが、ランダムアクセスが必要な場合などは、VBA.Collectionで取得してから配列に入れ直し。
参考
VBA標準機能で作成できる要素数0の配列 - Qiita
VBS用配列ラッパークラス
VBAからJScriptのfunctionオブジェクトを使用する（64bit対応） - Qiita
VBAから.Net Framework がつかえなくなった | RelaxTools Addin for Excel 2010/2013/2016