VBAにおける配列は、複数の値を一つの変数に格納することができる便利な機能です。
配列を使用するメリットは、大量のデータを効率的に処理ができることとコードの簡略化ができる点です。
特にExcelのセル範囲との相性がよく、ワークシート上のデータをあつかう処理において大きなパフォーマンスを発揮します。
本記事では、VBAにおける配列の基本的な使い方や操作方法について解説していきます。
配列には複数の値が格納できる
VBAでマクロを作る上で欠かせないものの1つに変数があります。
変数は数値や文字列など動的な値を代入して処理に利用するものですが、たくさんの変数をあつかう処理において、それぞれの変数を一つずつ宣言して管理するのは効率的なプログラミングとは言えません。
ExcelVBAでは、エクセルのワークシートに書かれた無数のデータを変数としてあつかう場合も珍しくありません。配列はExcelのワークシートとの相性がよく、まとめてデータを管理するときに有効な働きをするものです。
こちらの記事では配列についての解説をしていきます。
具体的には、配列の種類や宣言方法、値の代入、取り出しや出力についてサンプルコードつきで紹介しています。本記事を参考にご自身の環境にあわせてカスタマイズすることで、ゼロからコードを書く手間も省くことができるかもしれません。
なお、ExcelVBA初心者の人で、今までマクロをつくるときに配列をつかった経験がない人もいるかと思いますので、こちらの記事を何度も読み直して配列のつかいかたを身につけていただけると嬉しいです。
エクセルVBAの配列の種類について
エクセルVBAであつかう配列には”次元“というものがあり、配列をあつかう上ではこの次元を知っておく必要があります。
配列における”次元”とは、簡単に言えばデータを整理するために使う「方向」や「軸」の数です。
Excelのワークシートが”行“と”列“の二次元配列なので、VBAでは二次元配列をつかえるようにするととても効率的なマクロが作成できます。
次からは、それぞれの次元の配列について説明していきます。
一次元配列
横一列の変数のブロック(かたまり)をイメージしてもらうと理解しやすいかと思います。
画像の箱ブロックが配列であり、それぞれはインデックス(住所)をもっていて値の代入ができます。
一次元配列は縦長ではなく、横長に連なる変数ブロックですのでおぼえておくと良いでしょう。
変数を箱に例えるならつなげた箱のようなものだね。
二次元配列
二次元配列ですが、エクセルのワークシートをイメージするとわかりやすいかと思います。
以下にイメージ図を掲載しますが、縦(行)と横(列)の軸を持った変数のブロック(かたまり)だとおぼえておくと良いでしょう。
行と列の2軸だと考えるとワークシートのようなものだね。
多次元配列
三次元の場合は、縦(行)と横(列)と奥行の変数のブロック(かたまり)です。
エクセルのマクロをVBAでつくる上で三次元以上の次元をもつ配列をつかうことは少ないため、基本的には二次元配列まで理解できれば困ることはないでしょう。
ここではそういったものも存在することを知っておいてもらえれば良いです。
奥行が増えたことで立体になるイメージだね。
さて、三次元までで、行・列・奥行のイメージ図として紹介しましたが、このイメージ図を参考に、四次元配列をイメージできる人はいるでしょうか。
四次元以上になると、あたまの中でイメージをつくること非常に難しくなりますが、以下の画像のように考えていただければ、四次元以降の多次元にも対応が可能なイメージになるかと思います。
しかし、一般的にExcelVBAでマクロをあつかう上で、四次元以上の配列をつかうことはあまり考えられないため、四次元以降の配列についてはイメージ図だけの紹介にとどめたいと思います。
もっとも小さい3個の箱が2セット。
この2セット×2つ。さらにこのセットが2つ・・・。みたいなイメージなんですが、これをそれぞれの次元の数で算出すると、[2]*[2]*[2]*[3]の24個の箱をもった配列になります。
むずかしいと感じた人は深掘りする必要はありません。
さきほども述べましたが、エクセルのワークシート上で三次元以上の配列をつかうことは、すごく稀なケースであると言えるのでそういったものが存在する程度の知識でも十分です。
多次元配列の次元数はいくつまで指定できるのか
Microsoftのウェブページにも記載がありますが、次元数は60まで宣言が可能です。
実際にVBEで配列の次元数をいくつまで宣言できるかを試してみたところ、60次元までの配列は宣言可能でした。
また61個目の次元数を書くと構文エラーが発生してしまいました。
次元が多すぎます
配列の次元の数の上限は 60 です。 このエラーの原因と解決策を以下に示します。
次元の数が 60 を超える配列を宣言しようとした。 次元の数を減らします。
配列の宣言は制限を超えていないが、その配列を実際に作成するためのメモリが不足している。 使用できるメモリを増やすか、次元の数を減らします。 配列が Variant 型の配列または Variant に含まれている配列である場合は、配列の要素のデータ型を使用して宣言し直すことによって、同じ次元数の配列を作成できる可能性があります。 たとえば、配列に含まれているのが整数だけである場合は、Integer 型の配列として宣言すると、各要素が Variant である場合に比べて使用メモリが少なくなります。
Microsoft
オンラインスクールで現役エンジニアのサポートがあるテックアカデミーがおすすめ。
スキマ時間に学べて仕事も保証。必ず副業、始められます。まずは無料でプログラミング体験
エクセルVBAで配列を宣言する方法
エクセルVBAで配列をつかう場合は変数と同じように”宣言”が必要になります。
また、配列は前項で紹介した次元数のちがいでコードの書きかたが異なりますが、他にも静的配列と動的配列といった種類があります。
静的配列について
静的配列とは、あつかう要素(値)の数がきまっていて、その数が増えたり、減ったりしないことが前提の場合につかうものです。
たとえば、信号の色を配列に代入する場合、信号の色は青、黄、赤の3つです。この場合は、宣言する配列で準備する要素を代入する箱の数は3つで良いです。あらかじめ要素(値)の数がわかっていて、何らかの処理を実行しても要素の数が変わる心配がないのであれば静的配列を宣言すると良いでしょう。
動的配列について
一方、マクロを実行する状況や、実行途中で配列の要素(値)の数を増減する場合は動的配列を使います。
例えば、日々の売上件数などで事前に正確な要素数が分からない場合や、処理中にデータ量が変わる場合に特に威力を発揮します。
つぎは、静的配列と動的配列の宣言方法のちがいを紹介します。
静的配列を宣言する
配列の宣言方法は変数と同じようにDimを使います。
以下のサンプルマクロで”静的配列”を2つ宣言してみます。
静的配列を宣言するサンプルマクロ
Option Explicit
Sub 静的配列()
'一次元配列の宣言
Dim 静的配列宣言1(2)
Dim 静的配列宣言2(1 To 3)
End Sub
06行目
変数と同じようにDimキーワードを使って静的配列宣言1という名前の配列を宣言します。
ここでは、3つの要素をもてる配列にしたいので、インデックスに(2)を指定しています。
07行目
こちらもDimキーワードを使って静的配列宣言2という名前の配列を宣言します。
6行目の静的配列宣言1と同じように3つの要素をもった配列にしますが、インデックスに(1 To 3)を指定しています。
これで配列のインデックスが1から3の3つとなります。
この静的配列宣言1と静的配列宣言2の配列は、どちらも3つの要素を格納できる配列です。
静的配列宣言1と静的配列宣言2のちがいは、宣言時のインデックスの指定方法です。
配列のインデックスは、0 からはじまる仕様であるため、特に指定がない場合は、静的配列宣言1の箱の数は、0 、1 、2 の3つです。
一方、静的配列宣言2の箱の数は、インデックスに1 To 3を指定しているため、インデックスは 1 、2 、3 の3つとインデックスが異なります。
インデックスの書きかたでサイズが変わるよ。
二次元の静的配列の宣言
二次元配列の場合は、インデックスをカンマで区切って2つの数値を記入します。
二次元の静的配列の宣言するサンプルマクロ
Option Explicit
Sub 静的配列()
'二次元配列の宣言
Dim 静的二次元配列宣言1(2, 2) '宣言方法(1)
Dim 静的二次元配列宣言2(1 To 3, 1 To 3) '宣言方法(2)
End Sub
06行目
静的二次元配列宣言1という名前の配列を宣言します。
ここでは、1次元目に3つの要素と2次元目に3つの要素を持つ配列にしたいので、インデックスに(2,2)を指定しています。
07行目
静的二次元配列宣言2という名前の配列を宣言します。
こちらも先ほどと同じように9つの要素を持つ配列ですが、インデックスに(1 To 3, 1 To 3)を指定しています。
静的二次元配列宣言1と、静的二次元配列宣言2は、どちらも3行と3列の計9個の箱をもつ配列です。
静的二次元配列1はインデックスの指定がないので、インデックスは0からはじまり、静的二次元配列宣言2はインデックスは1からはじまります。
それぞれの配列の様子は以下の表のようになります。
静的二次元配列宣言1の場合
| インデックス | インデックス | インデックス |
|---|---|---|
| 0,0 | 0,1 | 0,2 |
| 1,0 | 1,1 | 1,2 |
| 2,0 | 2,1 | 2,2 |
静的二次元配列宣言2の場合
| インデックス | インデックス | インデックス |
|---|---|---|
| 1,1 | 1,2 | 1,3 |
| 2,1 | 2,2 | 2,3 |
| 3,1 | 3,2 | 3,3 |
インデックスはそれぞれの次元で指定することが可能だよ。
静的配列に値を代入する
前項で配列の宣言方法を紹介しましたが、配列を宣言しただけでは何も値が入っていない状態ですので3つの箱をもった配列が存在するのみ。といった状態となっています。
配列は変数のかたまり(ブロック)のようなものなので、数値や文字列などの値を入れて使います。
さきほど宣言した静的配列に値を代入し、指定のとおりに代入されたことを確認してみます。
配列に値を代入するサンプルマクロ
Option Explicit
Sub 静的配列宣言()
'インデックス0から2までの3つの配列を宣言し、値を代入する
Dim 静的配列宣言1(2)
静的配列宣言1(0) = "A"
静的配列宣言1(1) = "B"
静的配列宣言1(2) = "C"
'インデックス1から3までの3つの配列を宣言し、値を代入する
Dim 静的配列宣言2(1 To 3)
静的配列宣言2(1) = "AA"
静的配列宣言2(2) = "BB"
静的配列宣言2(3) = "CC"
End Sub
07行目から09行目
配列名：静的二次元配列宣言1に値を代入しています。
配列のどこに値を代入するかをインデックスを指定します。
13行目から15行目
配列名：静的二次元配列宣言2に値を代入しています。
配列のどこに値を代入するかをインデックスを指定します。
静的配列宣言1と、静的配列宣言2に値を代入した状態を表にすると以下のようになります。
| 変数名 | インデックス | 代入した値 |
|---|---|---|
| 静的配列宣言1 | 0 | A |
| 1 | B | |
| 2 | C | |
| 静的配列宣言2 | 1 | AA |
| 2 | BB | |
| 3 | CC |
VBE画面のローカルウィンドウでそれぞれの配列に値が代入されたことが表示されています。
静的二次元配列に値を代入
二次元配列に値を代入する場合は、それぞれの次元のインデックスを指定することで値を代入することができます。
以下のサンプルマクロをご覧ください。
配列に値を代入するサンプルマクロ
Option Explicit
Sub 静的配列宣言()
'二次元配列の宣言
Dim 静的二次元配列宣言1(2, 2)
'二次元配列に値を代入する
静的二次元配列宣言1(0, 0) = "2次元のA"
静的二次元配列宣言1(1, 0) = "2次元のB"
静的二次元配列宣言1(2, 0) = "2次元のC"
静的二次元配列宣言1(0, 1) = "2次元のD"
静的二次元配列宣言1(1, 1) = "2次元のE"
静的二次元配列宣言1(2, 1) = "2次元のF"
静的二次元配列宣言1(0, 2) = "2次元のG"
静的二次元配列宣言1(1, 2) = "2次元のH"
静的二次元配列宣言1(2, 2) = "2次元のI"
End Sub
06行目
静的二次元配列宣言1という名前の配列を宣言します。
09行目から17行目
静的二次元配列宣言1に値を代入します。
こちらの配列は二次元ですので、それぞれの次元のインデックスで値を代入する場所を指定して、＝(イコール)で代入する値を書きます。
上のマクロを実行すると、3行×3列の二次元配列の静的二次元配列宣言1に値を代入していきます。
値を代入した状態は以下の表でご確認ください。
配列の内容を表にしたイメージ
| 行／列 インデックス |
0 | 1 | 2 |
|---|---|---|---|
| 0 | 2次元のA (0,0) |
2次元のD (0,1) |
2次元のG (0,2) |
| 1 | 2次元のB (1,0) |
2次元のE (1,1) |
2次元のH (1,2) |
| 2 | 2次元のC (2,0) |
2次元のF (2,1) |
2次元のI (2,2) |
VBE画面での配列のようす
 |
 |
エクセルVBAで静的配列の値を参照または出力する
前項までで配列の宣言、宣言した配列に値を代入する方法まで紹介しました。
ここでは、宣言し値を代入した配列の値を出力する方法について説明します。
配列に代入した値を出力するサンプルマクロ
Option Explicit
Sub 静的配列宣言()
'一次元配列の宣言
Dim 静的配列宣言1(2)
'一次元配列に値を代入する
静的配列宣言1(0) = "A"
静的配列宣言1(1) = "B"
静的配列宣言1(2) = "C"
'一次元配列の値を参照・出力する(デバッグプリント)
Debug.Print 静的配列宣言1(0)
Debug.Print 静的配列宣言1(1)
Debug.Print 静的配列宣言1(2)
End Sub
14行目から16行目
Debug.Printをつかって静的配列宣言1に代入されている値をイミディエイトウィンドウ表示します。
静的配列宣言1のインデックスを使って代入された”A“から”C“の文字列を出力しています。
実行結果は次のとおりです。
実行結果
B
C
イミディエイトウィンドウに代入した値が出力されました。
インデックスを指定すると目的の値が取り出せるよ。
静的二次元配列の値の参照や出力
二次元配列の値を出力する場合は以下のように書きます。
配列に代入した値を出力するサンプルマクロ
Option Explicit
Sub 静的配列宣言()
'二次元配列の宣言
Dim 静的二次元配列宣言1(2, 2)
'二次元配列に値を代入する
静的二次元配列宣言1(0, 0) = "2次元のA"
静的二次元配列宣言1(1, 0) = "2次元のB"
静的二次元配列宣言1(2, 0) = "2次元のC"
静的二次元配列宣言1(0, 1) = "2次元のD"
静的二次元配列宣言1(1, 1) = "2次元のE"
静的二次元配列宣言1(2, 1) = "2次元のF"
静的二次元配列宣言1(0, 2) = "2次元のG"
静的二次元配列宣言1(1, 2) = "2次元のH"
静的二次元配列宣言1(2, 2) = "2次元のI"
'二次元配列に値を参照・出力する
Debug.Print 静的二次元配列宣言1(0, 0)
Debug.Print 静的二次元配列宣言1(1, 0)
Debug.Print 静的二次元配列宣言1(2, 0)
Debug.Print 静的二次元配列宣言1(0, 1)
Debug.Print 静的二次元配列宣言1(1, 1)
Debug.Print 静的二次元配列宣言1(2, 1)
Debug.Print 静的二次元配列宣言1(0, 2)
Debug.Print 静的二次元配列宣言1(1, 2)
Debug.Print 静的二次元配列宣言1(2, 2)
End Sub
20行目から28行目
Debug.Printをつかって静的二次元配列宣言1に代入されている値をイミディエイトウィンドウ表示します。
静的二次元配列宣言1のインデックスを使って代入された”2次元のA“から”2次元のI“の文字列を出力しています。
実行結果は次のとおりです。
実行結果
2次元のB
2次元のC
2次元のD
2次元のE
2次元のF
2次元のG
2次元のH
2次元のI
静的二次元配列宣言1(2, 2)は要素(値)数が9つですので、すべての要素(値)を出力してみました。
代入時と同じく、それぞれの次元でインデックスを指定するんだね。
エクセルVBAで動的配列を宣言する方法
これまでは要素数が変動しない静的配列の基本的な使い方を紹介してきました。
今度は、配列の要素(値)の数がきまっていないときなどに用いることができる動的配列について説明します。
動的配列は、どれだけ要素数をつかうかが決まっていない場合や、途中で変化するような処理が必要な場面で有効に使うことができます。
静的配列のときと同じく、まずは宣言方法から見ていきましょう。
動的配列を宣言するサンプルマクロ
Option Explicit
Sub 動的配列()
'動的配列の宣言
Dim 動的配列宣言1()
End Sub
06行目
動的配列宣言1を宣言しています。
動的配列の宣言では要素数を指定せずに “()” だけを書きます。
どれだけの要素数になるのかわからないので、コードのなかで動的配列の宣言で()の中に何も書きません。
これが静的配列とのちがいです。
この状態で動的配列宣言1に値を代入するとどうなるか実行してみましょう。
動的配列に値の代入を試みるサンプルマクロ
Option Explicit
Sub 動的配列()
'動的配列の宣言
Dim 動的配列宣言1()
動的配列宣言1() = 1
End Sub
宣言した動的配列宣言1に値である数字の” 1 “を代入するコードを書き実行してみます。
インデックスを指定して代入しようしても別のエラーが発生します。
動的配列に値の代入を試みるサンプルマクロ
Option Explicit
Sub 動的配列()
'動的配列の宣言
Dim 動的配列宣言1()
動的配列宣言1(0) = 1
End Sub
静的配列のときと同じようにしても値の代入ができないね。
動的配列は宣言するだけでは使えない
直前に書いたサンプルマクロのとおり、動的配列はただ宣言するだけでは値を代入することができません。
それでは、動的配列をつかうためにはどうすれば良いか？ですが・・・
結論を言ってしまえば動的配列も値を代入する前に要素数を指定する必要があります。
要素数を指定する必要があるのであれば、静的配列と同じでは？と思われるかもしれませんが、これまにで何度もお伝えしているとおり、静的配列は宣言後に要素数を変更はできません。
事前に代入したい要素数がわかっていて、その数が変わらないものであれば静的配列で問題ないですが、マクロを作成していれば、対象となる要素数が変動するものをあつかうことのほうが圧倒的に多いです。
たとえば、ある月の日数を要素数とする配列の場合、2023年1月は31日まであることから配列の大きさとして31個の箱が必要になりますが、同じ配列でも2023年2月の場合は、配列の大きさは28個になります。
また、さらに細かい部分まで言及すれば、2月の場合は、うるう年を考えておく必要があるため、毎年必ず28個の大きさとも限らないでしょう。
動的配列は処理に必要な要素数の分だけ柔軟にデータの増減に対応できるメリットがあり、最適な要素数の配列をつかうことでマクロで使用するメモリを必要最低限におさえることができます。
メモリの消費量の削減ときいてもピンとこない人も多いと思いますが、カンタンに言えば箱だけ大きくてスカスカのお弁当にはムダが多く、中身の量に合ったお弁当箱を用意することができると考えればイメージしやすいと思います。
ただし、さきほども説明したように動的配列をあつかう場合も、静的配列と同じく事前に要素数を宣言する必要があることも事実です。
では、動的配列で要素数を指定して、実際にあつかうための方法について次項より紹介していきます。
エクセルVBAの動的配列を再定義する
前項で動的配列をつかうためにも要素数を宣言する必要があることをお伝えしました。
動的配列で要素数を宣言することを再定義と言います。
動的配列を再定義するサンプルマクロ
Option Explicit
Sub 動的配列を再定義()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は1つ)
ReDim 動的配列宣言1(0)
'再定義した動的配列に要素を代入する
動的配列宣言1(0) = 1
End Sub
09行目
宣言した動的配列宣言1にRedimをつけて要素数を指定しています。
こちらでは要素数として” 0 “としているため、配列の箱の大きさは1つだけです。
インデックスは0からなので要素数1つの配列になるよ。
動的配列の再定義のかきかた
ReDim 配列名(数値)
サンプルコードのマクロだと以下の部分です。
ReDim 動的配列宣言1(0)
Redimをつかって動的配列を再定義したことで要素数が1つの配列 動的配列宣言1 がつくられました。
この 動的配列宣言1 のインデックス0に数値の1が代入されていることがローカルウィンドウで確認できました。
もっと手軽に多くのデータをあつかう方法もある
多くのデータをあつかう方法としてこの記事で紹介した配列のほかにも、コレクション(Collection)オブジェクトをつかう方法もあります。
コレクション(Collection)は、ブックやシートなどのオブジェクトをまとめている既存のものも存在しますが、独自で作成することもでき、データの追加や削除がとても簡単にできます。
コレクション(Collection)の使いかたについては、以下のリンクをクリックすると記事を読むことができます。
動的配列に値を代入する
動的配列を利用するにはRedimをつかって再定義する必要があることを説明しました。
次のサンプルマクロは動的配列に指定した月の日にちの数を代入した後、ワークシートに出力する処理をします。
具体的には、2023年2月の日にちの数だけの配列の大きさを再定義して、値の代入と代入されたワークシートに書き出す動きをします。
動的配列に値を代入するサンプルマクロ
Option Explicit
Sub 動的配列()
'動的配列の宣言
Dim 動的配列宣言1()
'日数を算出するための変数を宣言する
Dim 取得したい月 As Date
Dim その月の最終日 As Date
Dim その月の日数 As Long
'繰り返し処理用変数を宣言する
Dim i As Long
'日数を取得したい年月を変数に代入する
取得したい月 = "2023/2/1"
'翌月１日の前日を取得 → 2023/2/28
その月の最終日 = DateSerial(Year(取得したい月), Month(取得したい月) + 1, 0)
'末日の日のみを取得 → 28
その月の日数 = Format(その月の最終日, "d")
'動的配列の再定義(インデックスを1から28に指定する)
ReDim 動的配列宣言1(1 To その月の日数)
'ForNext文をつかって配列に値を代入する
For i = 1 To その月の日数
動的配列宣言1(i) = i
Next i
'配列の中身をワークシートにすべて出力する
ThisWorkbook.Worksheets("月日出力").Cells(1, 1) = 取得したい月
For i = 1 To その月の日数
ThisWorkbook.Worksheets("月日出力").Cells(i + 1, 1) = i
Next i
End Sub
09行目から11行目
変数には次の3つを宣言しています。
取得したい月、その月の最終日、その月の日数
そして、それぞれの変数には以下の表のように値を代入しています。
| 変数名 | 代入する値 |
|---|---|
| 取得したい月 | 2023/2/1 |
| その月の最終日 | DateSerial(Year(取得したい月), Month(取得したい月) + 1, 0) |
| その月の日数 | Format(その月の最終日, “d”) |
こちらのマクロでは、配列の要素数を求めるためにDateSerial関数をつかって月の日数を算出しています。
変数の取得したい月に入力した年月日から、その月の最終日を算出し、動的配列の要素数として指定しています。
例えば、取得したい月の値が”2023/1/1”であれば、日数が31なので要素数は31です。
つまり取得したい月の値が”2023/2/1”であれば、日数が28となることから要素数も28です。
※動的配列でも、静的配列と同じくインデックスは0からですので、サンプルコードでは配列を再定義したときに添え字でインデックスを1からに指定しています。
左の画像が2023/1/1、右の画像2023/2/1を指定した場合のローカルウィンドウを見比べてみると、要素数のちがいがわかります。
エクセルVBAの動的配列を再定義するときの注意点
動的配列を再定義するときは注意点があります。それは、配列を再定義するとそれまでに配列に代入されていた値がリセットされる点です。以下のマクロで確認してみます。
動的配列を再定義するサンプルマクロ
Sub 動的配列を再定義する()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は1つ)
ReDim 動的配列宣言1(0)
'再定義した動的配列に要素を代入する
動的配列宣言1(0) = "ひとつめの値"
'動的配列に要素をイミディエイトウィンドウに表示する
Debug.Print 動的配列宣言1(0)
'さらに動的配列の再定義(※要素数は2つ)
ReDim 動的配列宣言1(1)
'再定義した動的配列に要素を代入する
動的配列宣言1(1) = "ふたつめの値"
'動的配列に要素をイミディエイトウィンドウに表示する
Debug.Print 動的配列宣言1(0)
Debug.Print 動的配列宣言1(1)
End Sub
こちらのマクロを実行途中でとめて、動的配列の要素(値)を確認した結果は以下です。
注目してほしい点は、ローカルウィンドウの赤色の下線の部分と、イミディエイトウィンドウの赤枠の中です。ローカルウィンドウで、1回目の配列の再定義の後に代入した値(”ひとつめの値”)が”Empty”値となっていることがわかります。また、イミディエイトウィンドウにも”ひとつめの値”が出力されていないことから、配列である動的配列宣言1のインデックス0番に代入した値が消失したことがわかります。
要素を保持した状態で配列を再定義をする
それまでに代入した値を保持しながら動的配列を再定義したときは、以下のように書きます。
ReDim Preserve 配列名(数値)
Preserveは「保存する」という意味だよ。
実際のマクロで動的配列を再定義する部分のコードを書きかえて確認してみます。
Preserveをつかって動的配列を再定義するサンプルマクロ
Sub 動的配列を再定義する()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は1つ)
ReDim 動的配列宣言1(0)
'再定義した動的配列に要素を代入する
動的配列宣言1(0) = "ひとつめの値"
'動的配列に要素をイミディエイトウィンドウに表示する
Debug.Print 動的配列宣言1(0)
'さらに動的配列の再定義(※要素数は2つ)
ReDim Preserve 動的配列宣言1(1)
'再定義した動的配列に要素を代入する
動的配列宣言1(1) = "ふたつめの値"
'動的配列に要素をイミディエイトウィンドウに表示する
Debug.Print 動的配列宣言1(0)
Debug.Print 動的配列宣言1(1)
End Sub
ReDim Preserveで動的配列を再定義した場合のVBE画面
今度は動的配列宣言1のインデックス0番が保持されていることがわかります。
ただし、Preserveキーワードをつかった場合の制約もあるため、以下に記載します。
動的配列に「Preserve」をつかって再定義するときの注意点
動的配列の再定義で変更できるのは、添え字の上限のみ
Preserveで動的配列の添え字の上限を変更するサンプルマクロ
Sub 動的配列を再定義()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は2つ)
ReDim 動的配列宣言1(1 To 2)
'再定義した動的配列に要素を代入する
動的配列宣言1(1) = 1
動的配列宣言1(2) = 2
'動的配列の再定義(※要素数の上限を2→3に変更)
ReDim Preserve 動的配列宣言1(1 To 3)
End Sub最大値だけ変えられるってことだね。
Preserveで動的配列の添え字の下限を変更しようとするサンプルマクロ
Sub 動的配列を再定義()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は2つ)
ReDim 動的配列宣言1(1 To 2)
'再定義した動的配列に要素を代入する
動的配列宣言1(1) = 1
動的配列宣言1(2) = 2
'動的配列の再定義(※要素数の下限を1→0に変更)
ReDim Preserve 動的配列宣言1(0 To 2)
End Sub動的配列の再定義で変更できるのは、さいごの次元数のみ
Preserveで動的配列の添え字のさいごの次元を変更するサンプルマクロ
Sub 動的配列を再定義()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は二次元配列の2行2列)
ReDim 動的配列宣言1(1 To 2, 1 To 2)
'再定義した動的配列に要素を代入する
動的配列宣言1(1, 1) = "1,1"
動的配列宣言1(1, 2) = "1,2"
動的配列宣言1(2, 1) = "2,1"
動的配列宣言1(2, 2) = "2,2"
'動的配列の再定義(※要素数はさいごの次元のみ変更可能。再定義により二次元配列は2行3列になる)
ReDim Preserve 動的配列宣言1(1 To 2, 1 To 3)
End SubPreserveで動的配列の添え字のさいご以外の次元を変更しようとするサンプルマクロ
Sub 動的配列を再定義()
'動的配列の宣言
Dim 動的配列宣言1()
'動的配列の再定義(※要素数は二次元配列の2行2列)
ReDim 動的配列宣言1(1 To 2, 1 To 2)
'再定義した動的配列に要素を代入する
動的配列宣言1(1, 1) = "1,1"
動的配列宣言1(1, 2) = "1,2"
動的配列宣言1(2, 1) = "2,1"
動的配列宣言1(2, 2) = "2,2"
'動的配列の再定義(※要素数はさいごの次元のみ変更可能。再定義により二次元配列は3行2列にしようとする)
ReDim Preserve 動的配列宣言1(1 To 3, 1 To 2)
End Subさいごの次元だけ変えられるってことだね。
エクセルのセル範囲の値から配列をつくるマクロ
今度はエクセルのワークシートのセル範囲から、配列を作成してみたいと思います。
まずは、以下の画像をご覧ください。
A1セルからC5セルまでの範囲に、それぞれ文字列を記入しています。こちらのセル範囲の値を配列にするコードを書いてみます。
セル範囲から配列をつくるサンプルマクロ
Sub セル範囲から配列()
'変数の宣言
Dim セル範囲から配列 As Variant
セル範囲から配列 = ThisWorkbook.Worksheets("配列出力").Range("A1:C5")
End Sub書きかたのポイントですが、宣言時は通常の変数と同じように書きます。静的配列や動的配列のときのように()をつけたり、(1 to 5,1 to 3)のように添え字は書かないで宣言します。またデータ型はVariant(万能)型にしておきましょう。
変数のデータ型の種類やそれぞれの型についてなどは、別の記事で紹介していますので、そちらを記事をご覧ください。
以下の画像にて、ローカルウィンドウで配列の要素(値)を確認してみましょう。
画像を見てお気づきの人もいるかもしせませんが、セル範囲の値から配列にした場合のインデックスは、0からではなく1からです。静的配列や、動的配列の宣言で、下限値の指定をしなければ下限値は0からですので、下限値が異なっている点についてはおさえておきましょう。
セル範囲の値を配列にして別の範囲にセルに出力する
さきほど、セル範囲の値からつくった配列を出力してみます。ワークシートのＡ1セルからＣ5セルの範囲の値から配列をつくりましたので、その配列の値をA7セルからC11セルの範囲に出力します。
セル範囲からつくった配列を出力するサンプルマクロ
Sub セル範囲から配列()
'変数の宣言
Dim セル範囲から配列 As Variant
'セル範囲の値を配列に代入する
セル範囲から配列 = ThisWorkbook.Worksheets("配列出力").Range("A1:C5")
'配列(A1セルからC5セルの値)を他のセル範囲に出力する
ThisWorkbook.Worksheets("配列出力").Range("A7:C11").value = セル範囲から配列
End Sub
セル範囲から値を取得して配列をつくりましたが、その配列を指定のセル範囲に出力した結果の画像は以下のとおりです。
薄い青色で着色した範囲(A7セルからC11セル)に、配列の値が出力されました。配列の要素(値)を一括で出力する方法は、大量のデータを書きだすマクロにおいて、プログラムの実行速度を向上させる効果が高いので、使えるようにしておきたい方法です。
配列の使用例として、以前の記事でVLookup関数と同じはたらきをするサンプルコードで紹介している記事もありますので、配列の使ったマクロを開発をするときはコチラの記事もご覧になってみてください。
エクセルVBAで配列をつかったマクロでおぼえておきたいこと
ここからは、エクセルVBAで配列をつかったマクロをつくる上でおぼえておくと良いテクニックについていくつか紹介します。配列の自在につかえるようになるためには、こちらで紹介するテクニックを習得しておくことをおすすめします。
要素数の下限を取得できるLBound関数
LBound関数は配列の要素インデックスの下限値を返す関数です。以下のように使います。
LBound(配列名,[,次元数])
LBound関数で配列の添え字の下限値を取得するサンプルマクロ
Sub 配列テクニック()
Dim サンプル配列(9)
Dim 下限 As Long
下限 = LBound(サンプル配列)
Debug.Print 下限
End Subこのマクロを実行した結果、イミディエイトウィンドウに0が返ってきます。
配列のサンプル配列(9)には、0～9の10コの要素数があります。LBound関数はインデックスの下限値である数値を返します。つまりサンプル配列を宣言するときに添え字を(1 to 10)のようにすると、LBound関数で返ってくる値は1です。
要素数の上限を取得できるUBound関数
UBound関数は配列の要素インデックスの上限値を返す関数です。以下のように使います。
UBound(配列名,[,次元数])
UBound関数で配列の添え字の上限値を取得するサンプルマクロ
Sub 配列テクニック()
Dim サンプル配列(9)
Dim 下限 As Long
上限 = UBound(サンプル配列)
Debug.Print 上限
End Subこのマクロを実行した結果、イミディエイトウィンドウに9が返ってきます。
配列のサンプル配列(9)には、0～9の10コの要素数があり、インデックスは9番が上限となります。
UBound関数はインデックスの上限である数値を返す関数です。
仮に、サンプル配列を宣言するときにインデックスを(1 to 10)のように指定すると、UBound関数で返ってくる値は10です。
LBound・UBound関数をつかって要素数を算出する
配列の要素数を直接かえす関数はありませんが、配列のインデックスをかえすLBoundと、UBound関数をつかって配列の要素数を求めることもできます。以下のようなマクロで配列の要素数を求めることができます。
要素数 = UBound(配列名) – LBound(配列名) + 1
サンプルコードで、サンプル配列(9)の要素数が求められるか確認してみます。
L・UBound関数で配列の要素数を取得するサンプルマクロ
Sub 配列テクニック()
Dim サンプル配列(9)
Dim 下限 As Long, 上限 As Long, 要素数 As Long
下限 = LBound(サンプル配列) '0
上限 = UBound(サンプル配列) '9
要素数 = 上限 - 下限 + 1 '9 - 0 + 1
Debug.Print 下限 '0
Debug.Print 上限 '9
Debug.Print 要素数 '10
End Subこちらのサンプルコードの場合、サンプル配列(9)の宣言で、配列にはインデックスが0から9の10コが割り当てられます。サンプル配列のインデックスの下限値が0、上限値は9です。したがって計算式を数値に置き直した場合は、以下のとおりです。
要素数 ＝ 9 – 0 + 1
これによって、サンプル配列(9)の要素数は、10コであることが求められました。Debug.Printの結果も10がかえってきます。
配列の要素の数だけ処理を繰り返すマクロのつくりかた
ForNext文で配列の要素の数だけ繰り返す(ループ)処理の書きかた
ForNext文をつかって配列の要素の数だけ値を出力する処理のサンプルコード
配列の要素数だけ繰り返すサンプルマクロ
Sub 配列の要素の数だけ繰り返す_ForNext編()
Dim サンプル配列(4) As Variant
Dim i As Long
サンプル配列(0) = "ぜろ"
サンプル配列(1) = "いち"
サンプル配列(2) = "に"
サンプル配列(3) = "さん"
サンプル配列(4) = "し"
For i = LBound(サンプル配列) To UBound(サンプル配列) '変数 i が 0 から 4 まで繰り返す
Debug.Print サンプル配列(i)
Next i
End SubForNext文では、繰り返す回数を指定してその数だけ繰り返し処理をします。LBound関数で配列のインデックスの下限値を、UBound関数でインデックスの上限値を返し、下限値から上限値まで繰り返すマクロです。
このサンプルコードの場合、サンプル配列(4)のインデックスの下限値は0、上限値は4なので、カウンタ変数であるiが0からはじまり、4になるまで合計5回繰り返します。
ForEach文で配列の要素の数だけ繰り返す(ループ)処理の書きかた
ForEach文をつかって配列の要素の数だけ値を出力する処理のサンプルコード
配列の要素数だけ繰り返すサンプルマクロ
Sub 配列の要素の数だけ繰り返す_ForEach編()
Dim サンプル配列(4) As Variant
Dim var As Variant
サンプル配列(0) = "ぜろ"
サンプル配列(1) = "いち"
サンプル配列(2) = "に"
サンプル配列(3) = "さん"
サンプル配列(4) = "し"
For Each var In サンプル配列
Debug.Print var
Next var
End Sub注意点として、配列のデータ型にあわせて、要素を代入する変数のデータ型で宣言しなければなりません。
ForEachステートメントを含めた繰り返し文については、別の記事で詳しく解説しています。
以下のリンクをクリックすることで該当する記事にジャンプできます。
Eraseステートメントで配列の初期化をする
Eraseステートメントをつかうと、配列に代入された値は初期化されます。
これによりすべての要素が初期値に戻ります。
配列を初期化するための構文
Erase 配列名
以下のサンプルコードでは、サンプル配列に5つの文字列を代入して、繰り返し処理であるFor Each文で出力しています。そのあと、Eraseステートメントをつかってサンプル配列を初期化します。Eraseステートメントの直後にStopステートメントをくわえて処理を中断してローカルウィンドウを確認してみましょう。
Eraseステートメントをつかったサンプルマクロ
Sub 配列テクニック3()
Dim サンプル配列(4) As Variant
Dim var As Variant
サンプル配列(0) = "ぜろ"
サンプル配列(1) = "いち"
サンプル配列(2) = "に"
サンプル配列(3) = "さん"
サンプル配列(4) = "し"
For Each var In サンプル配列
Debug.Print var
Next var
Erase サンプル配列
Stop
For Each var In サンプル配列
Debug.Print var
Next var
End SubEraseステートメントが実行される前の状態のローカルウィンドウ
サンプル配列のそれぞれのインデックスに文字列が代入されていることがわかります。処理をすすめて、Eraseステートメント実行後のローカルウィンドウを見てみます。
ローカルウィンドウを確認すると、全ての要素がEmpty値となり、初期化されたことがわかります。
Array関数で配列に要素を一括代入する
Array関数をつかうと、文字列や値をまとめて配列にすることができます。
Array関数の構文
配列名 = Array(要素1, 要素2, 要素3, 要素4, 要素5・・・)
Array関数をつかう場合は、動的配列で宣言する必要があります。
Array関数をつかう場合は、データ型をVariant(万能)型にする必要があります。
Array関数をつかったサンプルマクロ
Sub 配列テクニック4()
Dim サンプル配列() As Variant
Dim var As Variant
サンプル配列() = Array("ぜろ", "いち", "に", "さん", "し")
For Each var In サンプル配列
Debug.Print var
Next var
End SubFilter関数で指定した内容に一致した配列内の要素を取り出す
Filter関数は、配列内の要素を検索して条件に一致した要素を取り出すことができます。二次元配列や完全一致の要素を抽出するのであれば、Forループで処理する方法ですが、Filter関数は部分一致の場合に利用できます。
Filter関数の構文
Filter(抽出もとの配列,検索文字列,True or False,0 or 1)
指定した配列の要素に対して、抽出が可能です。抽出もとの配列と条件文字列の引数は必須で記入する必要があり、第3引数以降は任意です。
3つめの引数はブール型(True or False)で指定ができます。条件文字列を含む要素を抽出する場合はTrueを指定、含まない要素を抽出する場合はFalseを指定します。最後の引数は、比較モードを指定でき、0の場合はバイナリ比較で、1の場合は大文字と小文字を区別しないテキストモードでの比較です。
Filter関数をつかったサンプルマクロ
Sub 配列の要素の数だけ繰り返す_Filter関数()
Dim サンプル配列(10) As Variant
Dim 抽出後の配列 As Variant
サンプル配列(0) = "ピカチュー"
サンプル配列(1) = "カイリュー"
サンプル配列(2) = "ヤドラン"
サンプル配列(3) = "ピジョン"
サンプル配列(4) = "コダック"
サンプル配列(5) = "コラッタ"
サンプル配列(6) = "ズバット"
サンプル配列(7) = "ギャロップ"
サンプル配列(8) = "サンダース"
サンプル配列(9) = "メノクラゲ"
サンプル配列(10) = "ライチュー"
抽出後の配列 = Filter(サンプル配列, "チュー", True, 1)
End Subこのマクロを実行すると、検索文字列に”チュー”を指定したため、抽出後の配列に”ピカチュー”と”ライチュー”が代入されます。一方、第3引数にFalseを指定した場合は、”ピカチュー”と”ライチュー”以外(“チュー”を含まない)の要素で抽出後の配列がつくられます。
Filter関数で第3引数をTrueに指定したときのマクロの実行結果
“チュー”を含む文字列の”ピカチュー”と”ライチュー”が要素の抽出後の配列がつくられました。
Filter関数で第3引数をFalseに指定したときのマクロの実行結果
“チュー”を含まない文字列が要素の抽出後の配列がつくられました。
エクセルVBAの配列の基本の使いかたまとめ
ここまで、エクセルVBAの配列の基本のつかいかたについて説明してきました。配列は変数のかたまりのようなもので、たくさんの値をあつかうことができる非常に便利なものです。
また、マクロの処理速度を向上させることにも使えるので、たくさんのデータをあつかった処理をするときに活躍してくれるでしょう。
さいごに最低限おさえておきたいポイントを箇条書きするので、おさらいしておきましょう。
- 配列の種類について
- 静的配列は大きさが固定された変数のかたまり
- 動的配列は大きさが変更できる変数のかたまり
- 配列の次元数について
- 配列の次元数は最大60まで、それ以上だとエラーが発生する
- エクセルのワークシートをあつかう処理なら、二次元配列までつかえれば問題なし
- 配列をつかったマクロでおぼえておきたいこと
- セル範囲や、Array関数で配列もつくれる
- 配列の要素インデックスは指定がなければ0から、セル範囲の値を代入した配列のインデックスは1から始まる
- インデックスの下限値はLBound関数、上限値はUBound関数で取得できる
- 配列内の要素の数だけ繰り返す処理はFor文で実現できる
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント