最近初めてマクロを組みましたが、意外とデータを自在に扱うのが意外と厄介でした。
そこでExcelのマクロ(VBA)を利用するとき、便利なデータ構造の操作をまとめました。
(環境)windows10 EXCEL2013
配列とリストの基本
配列
配列の生成
利用前に配列の大きさを指定しておく必ず必要があります。
さらに定数で指定しておく必要があります。
ただし、宣言は0から開始して最大の添え字を指定します。
以下の場合は2なので0,1,2の大きさ3の配列が宣言されます。
Sub list()
Dim list(2) As Integer
list(0) = 0
list(1) = 100
list(2) = 200
MsgBox list(1)
End Sub
また、Cを定数として下のようにも書けます。
Sub list()
Const C = 2
Dim list(C) As Integer
list(0) = 0
list(1) = 100
list(2) = 200
MsgBox list(1)
End Sub
ただし、Cを変数として宣言するとエラーになります。
配列の動的な生成
とはいえいつも定数で指定するのはメモリの無駄遣いですし、何かと決まった大きさで利用したいことが多々あります。
そこで変数でも配列を宣言できる方法があります。
Sub list()
Dim c As Integer
c = 2
Dim list() As Integer
ReDim list(c)
list(0) = 0
list(1) = 100
list(2) = 200
MsgBox list(1)
End Sub
途中でサイズを変更することも可能です。
Redimをすると改めて新しく宣言され、もともとの中身が消されてしまいますが、Preserveを付けることで前の内容を引き継いでくれます。
Sub list()
Dim c As Integer
c = 3
Dim list() As Integer
ReDim list(c)
list(0) = 0
list(1) = 100
list(2) = 200
ReDim Preserve list(c + 1)
list(3) = 300
MsgBox list(1)
End Sub
任意のオブジェクトの格納
StringやIntegerといった基本データ型のオブジェクトは上の方法で問題ありません。
Excelの場合はセルやシートやブックを配列に格納したい場合もあると思います。以下がその方法です。
Sub list()
Dim list(3) As Range
Set list(0) = Cells(1, 1)
Set list(1) = Cells(1, 2)
Set list(1) = Cells(1, 3)
MsgBox list(1).Value
End Sub
各要素ごとにSetされます。javaなどで配列の要素ごとにnewするのと同じようです。
もちろんDim list(3) As RangeをDim list(3) As Objectなどと広く宣言しておくことも可能です。
配列のサイズ取得
UBound([配列])、LBound([配列])関数を利用します。
UBoundは引数の配列のもっとも大きいインデックスを返却し、
LBoundは引数の配列のもっとも小さいインデックスを返却します。
Sub list()
Dim list(2) As Range
Set list(0) = Cells(1, 1)
Set list(1) = Cells(1, 2)
Set list(2) = Cells(1, 3)
Dim size as Integer
size = UBound(list) - LBound(list) + 1
MsgBox size
End Sub
配列のイテレータ
Sub list()
Dim list(2) As Range
Set list(0) = Cells(1, 1)
Set list(1) = Cells(1, 2)
Set list(2) = Cells(1, 3)
Dim ce
For Each ce In list
MsgBox ce.Value
Next
End Sub
多次元配列
多次元の宣言はlist(3,3)の様に2番目の配列の大きさを宣言することで生成できます。
Sub list()
Dim list(3, 3) As Range
Dim i, j As Integer
For i = 0 To 2
For j = 0 To 2
Set list(i, j) = Cells(i + 1, j + 1)
Next
Next
MsgBox list(1, 1).Value
End Sub
多次元配列のサイズ取得
1次元と同じUBound([配列],[次元])とLBound([配列],[次元])を利用できます。
1次元の時は第2引数を省略しましたが、次元を入れることで解決できます。
Sub list()
Dim list(3, 5) As Range
Dim i, j As Integer
For i = 0 To 2
For j = 0 To 4
Set list(i, j) = Cells(i + 1, j + 1)
Next
Next
MsgBox UBound(list, 1) '1次元目の添え字 -> 3
MsgBox UBound(list, 2) '2次元目の添え字 -> 5
End Sub
リスト
配列は予め大きさがわかっていないと宣言できないので不便です。
そこであとからどんどん要素を追加できるものとしてリストがあります。
リストの利用は.NETFrameworkがインストールされ、有効化されている必要があります。
Microsoft ArrayList クラスのリファレンスはこちらです。
リストの生成
新規リスト生成はCreateObject("System.Collections.ArrayList")を呼び出すことで作ることができます。
Sub list()
Dim arr As Object
Set arr = CreateObject("System.Collections.ArrayList")
arr.Add ("要素を追加")
MsgBox arr(0)
End Sub
リストの大きさ
プロパティCountを利用するとすぐ取得できます。
Sub list()
Dim arr As Object
Set arr = CreateObject("System.Collections.ArrayList")
arr.Add ("要素を追加0")
arr.Add ("要素を追加1")
arr.Add ("要素を追加2")
MsgBox arr.Count
End Sub
任意のオブジェクトの追加
基本的にオブジェクトならなんでも追加できるので特殊なことは不要です。
Sub list()
Dim arr As Object
Set arr = CreateObject("System.Collections.ArrayList")
arr.Add (Cells(1, 1))
MsgBox arr(0).Value
End Sub
リストのイテレータ
For~Eachステートメントを利用できます。
配列でもリストでも同じように使えます。
Sub list()
Dim list As Object
Set list = CreateObject("System.Collections.ArrayList")
list.Add 1
list.Add 2
list.Add 3
Dim i
For Each i In list
MsgBox i
Next
End Sub
連想配列
連想配列とはkey-valueで決まる。配列でkeyからvalueを取り出すような構造です。
keyは一意に決まっている必要があります。エクセルの場合,カラム名をkeyとしてvalueを取り出すことができるので大変便利です。
Microsoft Dictionary クラスのリファレンスはこちらです。
連想配列の生成
新規連想配列の生成はCreateObject("Scripting.Dictionary")を呼び出します。
Sub list()
Dim hash As Object
Set hash = CreateObject("Scripting.Dictionary")
Set hash("A1") = Cells(1, 1)
MsgBox hash("A1").Value
End Sub
連想配列の大きさ
リストと同じようにCountで簡単に取得します。
Sub list()
Dim hash As Object
Set hash = CreateObject("Scripting.Dictionary")
Set hash("A1") = Cells(1, 1)
MsgBox hash.Count
End Sub
Keyの取得
連想配列が持つKeyの取得を行います。Keys()メソッドを利用します。
Keyの配列を返却します。
Sub list()
Dim hash As Object
Set hash = CreateObject("Scripting.Dictionary")
Set hash("A1") = Cells(1, 1)
Set hash("A2") = Cells(1, 2)
Set hash("A3") = Cells(1, 3)
MsgBox hash.Keys()(2)
End Sub
連想配列のリスト
リストの中に連想配列を入れるのは少し工夫が必要です。
リストを一時的にAddしてあとでインスタンスを追加します。
この構造は表を格納したいときに非常に便利です。
Sub list()
Dim arr As Object
Set arr = CreateObject("System.Collections.ArrayList")
Dim hash As Object
Set hash = CreateObject("Scripting.Dictionary")
Set hash("A1") = Cells(1, 1)
arr.Add ("") '一時的に何かObjectを入れる。
Set arr(arr.Count - 1) = hash
MsgBox arr(0)("A1").Value
End Sub