この記事では、VBAでの動的２次元配列変数の使用方法を解説します。
配列の宣言方法から、配列変数の要素数取得に利用するUBound関数まで解説していきます。
VBA関連解説
配列変数について
VBAでリストデータを扱う時には、配列変数の使用が欠かせません。
配列変数を活用すると、ワークシート上のリストデータやユーザーフォームのリストボックス・コンボボックスのデータの取り扱いが容易になります。また、VBAの実行速度も上がりますので、配列変数を理解し使用していきましょう。
静的配列変数
静的配列変数とは、変数宣言時に次元数と各次元の要素数を確定させた配列変数です。
次元数・要素数を後から設定する動的配列変数と対比して、静的配列変数と呼びます。
変数の定義方法
通常の変数
変数名
１次元配列変数
変数名(1次元の要素数)
２次元配列変数
変数名(1次元の要素数､2次元の要素数)
配列変数は、変数名の後に( )を付けて、次元の要素数をカンマで区切って定義します。
要素数は０から始まる数値ですので、注意して下さい。
一般的には、２次元までの理解で充分だと思います。
イメージとしては、２次元の変数をワークシート上のリストとすると、３次元はリストが複数シートにあるワークブック、４次元は複数のワークブックがあるフォルダ...の様な捉え方もできます。
立体的な考え方ではなく、階層構造的な考え方ですが、VBAで配列変数を扱うにはこの様な理解もありかと思います。
変数の宣言
通常の変数の宣言
Dim 変数名 As データ型
静的１次元配列変数の宣言
Dim 変数名(1次元の要素数) As データ型
静的２次元配列の宣言
Dim 変数名(1次元の要素数､2次元の要素数) As データ型
Private やPublic でも宣言できます。
データ型は、Variant型がお勧めです。
配列変数に取り込みたいリストには文字列や数値・日付などが含まれている場合が多く、宣言時のデータ型に悩むことがあります。
その様な時は、Variant型で宣言しましょう。明確な型指定が必要な場合は、変数からデータを取り出す時に型変換する手もあります。
データ格納イメージ
通常の変数
Dim 変数名 As Variant
イメージ図
１個のデータが格納できます。
１次元配列変数
Dim 変数名(2) As Variant
イメージ図（インデックス番号）
３個のデータが格納できます。
インデックス番号が０から始まることに注意して下さい。
２次元配列変数
Dim 変数名(2,3) As Variant
イメージ図（インデックス番号）
12個のデータが格納できます。
インデックス番号が(0,0)から始まることに注意して下さい。
配列変数の個々のデータにはインデックス番号が振られています。
インデックス番号は、主にデータの識別に使われ、０から始まる数値で、カウンター変数を使いループさせることができます。
データ取り込み
ワークシート上のセルから、データを取り込む方法を例示していきます。
インデックス番号をカウンター変数を使いループさせる方法も示しました。こちらの方が実践的です。
取り込み用サンプル
通常の変数
Dim 変数名 As Variant 変数名 = Cells(2,1).Value
取り込みデータ
１個のデータが取り込まれます。
１次元配列変数
Dim 変数名(2) As Variant 変数名(0) = Cells(2,1).Value 変数名(1) = Cells(3,1).Value 変数名(2) = Cells(4,1).Value
＊For 〜 Next で取り込み
Dim 変数名(2) As Variant Dim i As Long For i = 0 To 2 変数名(i) = Cells(i+2,1).Value Next
取り込みデータ
３個のデータが取り込まれます。
For 〜 Next を使用し、カウンター= i で行データ(レコード)をループさせ、列データ(フィールド)を取り込んでいきます。
２次元配列変数
Dim 変数名(2,3) As Variant 変数名(0,0) = Cells(2,1).Value 変数名(0,1) = Cells(2,2).Value 変数名(0,2) = Cells(2,3).Value 変数名(0,3) = Cells(2,4).Value 変数名(1,0) = Cells(3,1).Value 変数名(1,1) = Cells(3,2).Value 変数名(1,2) = Cells(3,3).Value 変数名(1,3) = Cells(3,4).Value 変数名(2,0) = Cells(4,1).Value 変数名(2,1) = Cells(4,2).Value 変数名(2,2) = Cells(4,3).Value 変数名(2,3) = Cells(4,4).Value
＊For 〜 Next で取り込み
Dim 変数名(2,3) As Variant Dim i As Long , j As Long For i = 0 To 2 For j = 0 To 3 変数名(i,j) = Cells(i+2,j+1).Value Next Next
取り込みデータ
12個のデータが取り込まれます。
For 〜 Next をネストし、カウンター= i で行データ(レコード)をループさせ、カウンター= j で各該当行(レコード)の列データ(フィールド)をループし、取り込んでいきます。
データ書き込み
VBA実行時には、セルへの書き込みが速度低下の要因となります。
そこで、配列変数を用いてデータを１つのかたまりとして扱うと、速度低下もなく、可読性も上がるので活用しましょう。
配列変数のセルへの書き込み
Dim 変数名(2,3) As Variant 'セル範囲指定での書き込み Range("A1:C4").Value = 変数名 'セル範囲拡張での書き込み Range("A1").Resize(3,4).Value = 変数名
セルへの書き込みは、配列変数のデータサイズのセル範囲を指定するか、書き込みの起点となるセル位置からResize(１次元のデータ数、2次元のデータ数)でセル範囲を拡張させて指定し、配列データを一括で書き込みます。
オブジェクトへの書き込み
Dim 変数名(2,3) As Variant 'コンボボックスへの書き込み Combobox1.List = 変数名 'リストボックスへの書き込み Listbox1.List = 変数名
ユーザーフォームのオブジェクトへも、配列データを一括で書き込めます。
データ消去
配列の初期化
Erase 変数名
Eraseで、内部データを消去できます。
動的配列変数
動的配列変数とは、要素数確定後に次元数と各次元の要素数を設定する配列変数です。
変数宣言時に次元数と各次元の要素数を確定させる静的配列変数と対比して、動的配列変数と呼びます。
変数の宣言と要素数の確定
動的１次元配列の宣言と要素数の確定
Dim 変数名( ) As データ型 '1次元の要素数 =Ａに確定 ReDim 変数名(Ａ) As データ型
動的２次元配列の宣言と要素数の確定
Dim 変数名( ) As データ型 '1次元の要素数 =Ａ、2次元の要素数 = Bに確定 ReDim 変数名(Ａ､Ｂ) As データ型
宣言時は( )内を空にして配列変数を宣言します。
なんらかの処理で格納するデータの要素数が確定した後、ReDimで配列変数の要素数を設定します。
ReDimは繰り返し宣言できますが、宣言毎に内部データが消去されますので注意が必要です。
ReDim宣言時のデータ型指定は、Dim宣言時のデータ型と同一でないとエラーがおきます。データ型は動的に変更することはできません。
また、ReDim Preserveで要素数を追加できますが、追加できない次元があったり、可読性が低下するので、あまり使用しません。プロシージャの途中で要素数を追加したくなった場合は、ReDimまで戻って要素数を変更するといいでしょう。
UBound、LBound関数
動的配列変数は要素数が変動するので、外部からは設定された要素数が分かり難くなります。
そこで、UBound、LBound関数を使うと配列変数の次元毎の要素数が取得できるので活用しましょう。
UBound、LBound関数
UBound (変数名,次元数) LBound (変数名,次元数)
UBound関数は、設定した次元数のインデックス番号の最大値を返します。次元数は省略可能で、省略したときは、次元数に１次元が設定されます。
LBound関数は、設定した次元数のインデックス番号の最小値を返します。次元数は省略可能で、省略したときは、次元数に１次元が設定されます。
また、LBound関数はあまり使用しません。
配列変数は初期値で使うと、次元の最小要素数は０となりますので、殆どの場合、配列のインデックス番号の最小値は０となるからです。
この記事では、要素範囲の指定( Option Base 1、1 To 3 )などは可読性が低下するので解説に含めていませんが、要素範囲の指定をした場合にはLBound関数が必要となってきます。
UBound関数を使用した、データ取り込み
１次元配列変数
Dim 変数名( ) As Variant Dim i As Long '1次元の要素数 =Ａに確定 ReDim 変数名(Ａ) As Variant For i = 0 To UBound (変数名) 変数名(i) = Cells(i+1,2).Value Next
２次元配列変数
Dim 変数名( ) As Variant Dim i As Long , j As Long '1次元の要素数 =Ａ、2次元の要素数 = Bに確定 ReDim 変数名(Ａ､Ｂ) As Variant For i = 0 To UBound (変数名,1) For j = 0 To UBound (変数名,2) 変数名(i,j) = Cells(i+2,j+1).Value Next Next
配列変数の要素数が変動しても、AやBの要素数を意識せずに、データ取り込み可能です。また、インデックス番号の最小値を０として、LBoundは0としています。
UBound関数を使用した、データ書き込み
１次元配列変数のデータ数
UBound (変数名) + 1
2次元配列変数のデータ数
'1次元のデータ数 UBound (変数名,１) + 1 '2次元のデータ数 UBound (変数名,2) + 1
UBoundを使うと、配列変数に格納されているデータ数が取得可能です。
要素数が０から始まるので、最後に+１しています。また、インデックス番号の最小値を０として、LBoundは省略しています。
配列変数のセルへの書き込み
Dim 変数名( ) As Variant '1次元の要素数 =Ａ、2次元の要素数 = Bに確定 ReDim 変数名(Ａ､Ｂ) As Variant 'セル範囲拡張での書き込み Range("A1").Resize(UBound (変数名,１) + 1 , UBound (変数名,2) + 1 ).Value = 変数名
セルへの書き込みは、Resize(１次元のデータ数、2次元のデータ数)でセル範囲を拡張させて指定する時に、UBound関数を使うと範囲指定が簡略化されます。AやBの要素数を意識せずに、データ書き込み可能です。
VBAは配列変数を理解すると、データの取り扱いが格段に容易になります。頭の中でテーブル構造を想像しながらコードを書いてみると理解が進んでいきます。
また、大概の配列変数の解説記事は内容が盛りだくさんなので、色々な技巧を使用したくなります。ですが、あまり複雑なコードにしてしまうと、配列変数を理解している人でも読み取り難くなりますので、シンプルなコードを心掛けましょう。
概要解説に戻る
www.minizaiko.com
