エクセルVBA｜配列を利用したプログラミングで多くのデータを処理する

VBAにおける配列は、複数の値を一つの変数に格納することができる便利な機能です。配列を使用することで、大量のデータを効率的に操作ができます。本記事では、VBAにおける配列の基本的な使い方や操作方法について解説していきます。

エクセルVBAの配列は複数の値を格納できる便利な機能

VBAでマクロをつくりはじめていくと、それぞれの変数名を宣言して、数値や文字列などの値を代入して処理に利用し、マクロを作っていくわけですが、たくさんの変数をあつかうマクロにおいて、それぞれの変数を一つずつ宣言するのは効率的なプログラミングとは言えません。

今回は、ExcelVBAで大量のデータをあつかいたいときに便利な「配列」について解説していきたいと思います。具体的には、配列の種類や宣言方法、値の代入、出力についてサンプルコードつきで紹介しますので、ご自身の環境にあわせてカスタマイズすることで、ゼロからコードを書く手間も省くことができるかもしれません。

なお、ExcelVBA初心者の人で、今までマクロをつくるときに配列をつかった経験がない人もいるかと思いますので、こちらの記事を何度も読み直して配列のつかいかたを身につけていただけると嬉しいです。

エクセルVBAの配列の種類について

エクセルVBAであつかう配列に3つの種類があります。
Excelのワークシートが二次元配列なので、VBAでは二次元配列をつかえるようにするととても効率的なマクロが作成できます。

エクセルVBAの一次元配列

横一列の変数のブロック(かたまり)をイメージしてもらうと理解しやすいかと思います。
画像の箱ブロックが配列であり、それぞれはインデックス(住所)をもっていて値の代入ができます。一次元配列は縦長ではなく、横長に連なる変数ブロックですのでおぼえておきます。

エクセルVBAの二次元配列

つづいて、二次元配列ですが、エクセルのワークシートでなじみがある縦(行)と横(列)の変数のブロック(かたまり)をイメージしてもらうと理解しやすいかと思います。

エクセルVBAの多次元配列

さらに3次元の場合は、縦(行)と横(列)と奥行の変数のブロック(かたまり)です。エクセルのマクロをVBAでつくることにおいて、三次元以降の配列をつかうことは少ないため、二次元配列まで理解できれば困ることはないでしょう。

ここではそういったものも存在することを知っておいてもらえれば良いです。

さて、三次元までで、行・列・奥行のイメージ図として紹介しましたが、このイメージ図を参考に、四次元配列をイメージできる人はいるでしょうか。

四次元以上になると、あたまの中でイメージをつくること非常に難しくなりますが、以下の画像のように考えていただければ、四次元以降の多次元にも対応が可能なイメージになるかと思います。

しかし、一般的にExcelVBAでマクロをあつかう上で、4次元以上の配列をつかうことはあまり考えられないため、4次元以降の配列についてはイメージ図だけの紹介にとどめたいと思います。

もっとも小さい3個の箱が2セット。この2セット×2つ。さらにこのセットが2つ・・・。みたいなイメージなんですが、これをそれぞれの次元の数で算出すると、[2]*[2]*[2]*[3]の24個の箱をもった配列です。

むずかしいと感じた人は深掘りする必要はありません。さきほども述べましたが、エクセルのワークシート上で三次元以降の配列をつかうことは、すごくまれなケースであると言えるのでそういったものが存在する程度の知識でも十分です。

エクセルVBAの多次元配列の次元数はいくつまで

Microsoftのウェブページにも記載がありますが、次元数は60まで宣言が可能です。実際にVBEで配列の次元数をいくつまで宣言できるかを試してみたところ、確かに60までは、それ以下の配列と同様に宣言が可能で、61個目の次元数を書くと構文エラーが発生してしまいました。

次元が多すぎます

配列の次元の数の上限は 60 です。 このエラーの原因と解決策を以下に示します。

次元の数が 60 を超える配列を宣言しようとした。 次元の数を減らします。

配列の宣言は制限を超えていないが、その配列を実際に作成するためのメモリが不足している。 使用できるメモリを増やすか、次元の数を減らします。 配列が Variant 型の配列または Variant に含まれている配列である場合は、配列の要素のデータ型を使用して宣言し直すことによって、同じ次元数の配列を作成できる可能性があります。 たとえば、配列に含まれているのが整数だけである場合は、Integer 型の配列として宣言すると、各要素が Variant である場合に比べて使用メモリが少なくなります。

Microsoft

エクセルVBAで配列を宣言する方法

エクセルVBAで配列をつかう場合は変数と同じように「宣言」が必要になります。また、配列は前項で紹介した次元数のちがいでプログラムの書きかたが異なりますが、その他に静的配列と動的配列といった種類があります。

静的配列とは、マクロであつかう要素(値)の数がきまっていて、その数が増えたり、減ったりしないことが前提の場合につかうものです。
たとえば、信号の色を配列に代入する場合、信号の色は青、黄、赤の3つです。この場合は、宣言する配列で準備する要素を代入する箱の数は3つで良いです。あらかじめ要素(値)の数がわかっていて、何らかの処理を実行しても要素の数が変わる心配がないのであれば静的配列を宣言すると良いでしょう。

一方、マクロを実行する状況や、実行途中で配列の要素(値)の数を増減したい場合は、動的配列を使います。

ここでは、静的配列と動的配列の宣言方法のちがいを紹介します。

エクセルVBAで静的配列を宣言する

配列の宣言方法は変数と同じようにDimを使います。以下に静的配列を2つ宣言してみます。

静的配列を宣言するサンプルマクロ

Sub 静的配列()

'一次元配列の宣言
Dim 静的配列宣言1(2)
Dim 静的配列宣言2(1 To 3)

End Sub

この静的配列宣言1と静的配列宣言2の配列は、同じ3つの箱をもった配列です。

静的配列宣言1と静的配列宣言2のちがいは、宣言時の添え字に指定方法です。
配列のインデックスは、原則0番からはじまる仕様であるため、特に指定がない場合は、静的配列宣言1の箱の数は、0番、1番、２番の3つです。
一方、静的配列宣言2の箱の数は、添え字に1 To 3を指定しているため、インデックスは1番、2番、３番の3つとインデックスが異なります。

静的二次元配列の宣言

静的配列(二次元)を宣言するサンプルマクロ

Sub 静的配列()

　'二次元配列の宣言
　Dim 静的二次元配列宣言1(2, 2)　'宣言方法(1)
　Dim 静的二次元配列宣言2(1 To 3, 1 To 3)　'宣言方法(2)

End Sub

二次元配列の場合は、添え字をカンマで区切り、2つの数値を記入します。静的二次元配列宣言1では、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

エクセルVBAで静的配列に値を代入する

前項で配列の宣言方法を紹介しましたが、配列を宣言しただけでは何も値が入っていない状態ですので、3つの箱をもった配列が存在するのみ。といった状態となっています。配列は変数のかたまり(ブロック)のようなものなので、数値や文字列などの値を入れて使います。

さきほど宣言した静的配列に値を代入し、指定のとおりに代入されたことを確認してみます。

配列に値を代入するサンプルマクロ

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

静的配列宣言1(2)と、静的配列宣言2(1 To 3)に以下のような値を代入してみます。

静的配列宣言1のインデックスは0、１、２であり、静的配列宣言2のインデックスは1、２、３となります。

変数名	インデックス	代入した値
静的配列宣言1	0	A
	1	B
	2	C
静的配列宣言2	1	AA
	2	BB
	3	CC

VBE画面のローカルウィンドウでそれぞれの配列に値が代入されたことが表示されています。

静的二次元配列に値を代入

二次元配列に値を代入する場合は、以下のようにかきます。

配列に値を代入するサンプルマクロ

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

3行×3列の二次元配列の静的二次元配列宣言1に値を代入していきます。以下の表が配列だとイメージし、それぞれの箱の中に値が入っているとイメージするとわかりやすいと思います。

配列の内容を表にしたイメージ

左上のインデックスが(0,0)、右下のインデックスは(2,2)となります。

行／列 インデックス	0	1	2
0	2次元のA	2次元のD	2次元のG
1	2次元のB	2次元のE	2次元のH
2	2次元のC	2次元のF	2次元のI

VBE画面での配列のようす

エクセルVBAで静的配列の値を参照または出力する

前項までで配列の宣言、宣言した配列に値を代入する方法まで紹介しました。
ここでは、宣言し値を代入した配列の値を参照したり、出力する方法について説明します。

配列に代入した値を出力するサンプルマクロ

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

静的配列宣言1(0)、静的配列宣言1(1)、静的配列宣言1(2)の要素を、Debug.Printをつかってイミディエイトウィンドウに表示してみます。

実行結果

A
B
C

イミディエイトウィンドウに代入した値が出力されました。

静的二次元配列の値の参照や出力

二次元配列の値を参照したり、出力したりする方法のサンプルは以下です。

配列に代入した値を出力するサンプルマクロ

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

二次元配列の要素(値)を、Debug.Printをつかってイミディエイトウィンドウに表示してみます。

実行結果

2次元のA
2次元のB
2次元のC
2次元のD
2次元のE
2次元のF
2次元のG
2次元のH
2次元のI

静的二次元配列宣言1(2, 2)は要素(値)数が9つですので、すべての要素(値)を出力してみました。

エクセルVBAで動的配列を宣言する方法

今度は、動的配列の宣言方法について紹介します。動的配列は配列の要素(値)の数がきまっていないときなどに使い、要素数を増減させることができます。

動的配列を宣言するサンプルマクロ

Sub 動的配列()

'動的配列の宣言
Dim 動的配列宣言1()

End Sub

どれだけの要素数になるのかわからないので、動的配列では()の中は何も書かない点が静的配列とのちがいです。この状態で動的配列宣言1に値を代入するとどうなるか実行してみましょう。

動的配列に値の代入を試みるサンプルマクロ

Sub 動的配列()

'動的配列の宣言
Dim 動的配列宣言1()

動的配列宣言1() = 1

End Sub

宣言した動的配列宣言1という名前の配列に値を代入するコードを書き実行してみます。

インデックスを指定して代入しようしても別のエラーが発生します。

動的配列に値の代入を試みるサンプルマクロ

Sub 動的配列()

'動的配列の宣言
Dim 動的配列宣言1()

動的配列宣言1(0) = 1

End Sub

それでは、動的配列をつかうためにはどうすれば良いかですが、
実は動的配列においても、値を代入する前に要素数を指定する必要があります。

それでは静的配列と同じでは？と思われるかもしれませんが、静的配列は宣言後に要素数を変更はできません。
事前に代入したい要素数がわかっていて、その数が変わらないものであれば静的配列で問題ないですが、マクロを作成していれば、対象となる要素数が変動するものをあつかうことのほうが圧倒的に多いのです。

たとえば、ある月の日数を要素数とする場合、2023年1月は31日まであることから配列の要素数は31個の箱が必要だと分かります。しかし、これと同じことを翌月の2023年2月で処理した場合は、要素数が28個になります。

また、2月の場合は、うるう年を考えておく必要があるため、毎年28個の要素数とは限らないでしょう。

動的配列をつかうことで、こういったデータの増減に対応できるメリットがあり、最適な要素数の配列をつかうことでマクロで使用するメモリを必要最低限におさえることができます。メモリの消費量の削減ときいてもピンとこない人も多いと思いますが、カンタンに言えば箱だけ大きくてスカスカのお弁当にはムダが多く、中身の量に合ったお弁当箱を用意することができると考えればいいです。

ただし、さきほども説明したように動的配列をあつかう場合も、静的配列と同じく事前に要素数を宣言する必要があることも事実です。

では、動的配列で要素数を指定して、実際にあつかうための方法について次項より紹介していきます。

エクセルVBAの動的配列を再定義する

動的配列をつかうためには再定義が必要になります。
「再定義」と難しい表現をしていますが、宣言した動的配列の要素数を添え字で指定しているものです。

動的配列を再定義するサンプルマクロ

Sub 動的配列を再定義()

'動的配列の宣言
Dim 動的配列宣言1()

'動的配列の再定義(※要素数は1つ)
ReDim 動的配列宣言1(0)

'再定義した動的配列に要素を代入する
動的配列宣言1(0) = 1

End Sub

動的配列の再定義のかきかた

ReDim 配列名(数値)

サンプルコードのマクロだと以下の部分です。

Redimをつかって動的配列を再定義したことで要素数が1つの配列(動的配列宣言1)がつくられました。この動的配列宣言1のインデックス0番に数値の1が代入されていることがローカルウィンドウで確認できました。

要素数の把握や管理が面倒くさいなら、コレクション(Collection)オブジェクトをつかってデータをまとめる方法もあります。コレクション(Collection)は、ブックやシートなどのオブジェクトをまとめている既存のものも存在しますが、独自で作成することもでき、データの追加や削除がとても簡単にできます。

コレクション(Collection)の使いかたについては、以下のリンクをクリックすると記事を読むことができます。

動的配列に値を代入する

動的配列に値を代入するサンプルマクロ

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

こちらのマクロでは、配列の要素数を求めるために月の日数を算出しています。
変数の取得したい月に入力した年月日から、その月の最終日を算出し、動的配列の要素数として指定しています。

例えば、取得したい月の値が”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をつかって動的配列を再定義するサンプルマクロ

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 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"

'動的配列の再定義(※要素数はさいごの次元のみ変更可能。再定義により二次元配列は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セル)に、配列の値が出力されました。配列の要素(値)を一括で出力する方法は、大量のデータを書きだすマクロにおいて、プログラムの実行速度を向上させる効果が高いので、使えるようにしておきたい方法です。

実際のマクロで使用しているコードも別の記事で紹介していますので、実務などで利用する場合は、こちらの記事を参考にマクロの開発をすすめることができると思います。

エクセル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)の要素数は、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 Sub

ForNext文では、繰り返す回数を指定してその数だけ繰り返し処理をします。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 Sub

Eraseステートメントが実行される前の状態のローカルウィンドウ

サンプル配列のそれぞれのインデックスに文字列が代入されていることがわかります。処理をすすめて、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 Sub

Filter関数で指定した内容に一致した配列内の要素を取り出す

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文で実現できる