Excelマクロを自由自在に扱うデータ構造まとめ

最近初めてマクロを組みましたが、意外とデータを自在に扱うのが意外と厄介でした。
そこでExcelのマクロ(VBA)を利用するとき、便利なデータ構造の操作をまとめました。
(環境)windows10 EXCEL2013

配列とリストの基本

配列

配列の生成

利用前に配列の大きさを指定しておく必ず必要があります。
さらに定数で指定しておく必要があります。
ただし、宣言は0から開始して最大の添え字を指定します。
以下の場合は2なので0,1,2の大きさ3の配列が宣言されます。

list

Sub list()
  Dim list(2) As Integer
  list(0) = 0
  list(1) = 100
  list(2) = 200
  MsgBox list(1)
End Sub

また、Cを定数として下のようにも書けます。

list

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を変数として宣言するとエラーになります。

配列の動的な生成

とはいえいつも定数で指定するのはメモリの無駄遣いですし、何かと決まった大きさで利用したいことが多々あります。
そこで変数でも配列を宣言できる方法があります。

list

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を付けることで前の内容を引き継いでくれます。

list

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の場合はセルやシートやブックを配列に格納したい場合もあると思います。以下がその方法です。

list

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は引数の配列のもっとも小さいインデックスを返却します。

list

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

配列のイテレータ

list

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番目の配列の大きさを宣言することで生成できます。

list

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引数を省略しましたが、次元を入れることで解決できます。

list

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")を呼び出すことで作ることができます。

list

Sub list()
    Dim arr As Object
    Set arr = CreateObject("System.Collections.ArrayList")
    arr.Add ("要素を追加")
    MsgBox arr(0)
End Sub

リストの大きさ

プロパティCountを利用するとすぐ取得できます。

list

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

任意のオブジェクトの追加

基本的にオブジェクトならなんでも追加できるので特殊なことは不要です。

list

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ステートメントを利用できます。
配列でもリストでも同じように使えます。

list

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")を呼び出します。

list

Sub list()
    Dim hash As Object
    Set hash = CreateObject("Scripting.Dictionary")
    Set hash("A1") = Cells(1, 1)
    MsgBox hash("A1").Value
End Sub

連想配列の大きさ

リストと同じようにCountで簡単に取得します。

list

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の配列を返却します。

list

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してあとでインスタンスを追加します。
この構造は表を格納したいときに非常に便利です。

list

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