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

　VBAにおける配列は、複数の値を一つの変数に格納することができる便利な機能です。

配列を使用するメリットは、大量のデータを効率的に処理ができることとコードの簡略化ができる点です。
特にExcelのセル範囲との相性がよく、ワークシート上のデータをあつかう処理において大きなパフォーマンスを発揮します。

本記事では、VBAにおける配列の基本的な使い方や操作方法について解説していきます。

Contents

配列には複数の値が格納できる

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

　ExcelVBAでは、エクセルのワークシートに書かれた無数のデータを変数としてあつかう場合も珍しくありません。配列はExcelのワークシートとの相性がよく、まとめてデータを管理するときに有効な働きをするものです。

こちらの記事では配列についての解説をしていきます。
具体的には、配列の種類や宣言方法、値の代入、取り出しや出力についてサンプルコードつきで紹介しています。本記事を参考にご自身の環境にあわせてカスタマイズすることで、ゼロからコードを書く手間も省くことができるかもしれません。
なお、ExcelVBA初心者の人で、今までマクロをつくるときに配列をつかった経験がない人もいるかと思いますので、こちらの記事を何度も読み直して配列のつかいかたを身につけていただけると嬉しいです。

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

　エクセルVBAであつかう配列には”次元“というものがあり、配列をあつかう上ではこの次元を知っておく必要があります。

配列における”次元”とは、簡単に言えばデータを整理するために使う「方向」や「軸」の数です。
Excelのワークシートが”行“と”列“の二次元配列なので、VBAでは二次元配列をつかえるようにするととても効率的なマクロが作成できます。

次からは、それぞれの次元の配列について説明していきます。

一次元配列

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

一次元配列は縦長ではなく、横長に連なる変数ブロックですのでおぼえておくと良いでしょう。

配列1(0 To 4)で宣言した配列のイメージ

変数を箱に例えるならつなげた箱のようなものだね。

二次元配列

　二次元配列ですが、エクセルのワークシートをイメージするとわかりやすいかと思います。
以下にイメージ図を掲載しますが、縦(行)と横(列)の軸を持った変数のブロック(かたまり)だとおぼえておくと良いでしょう。

配列2(0 To 2, 0 To 1)で宣言した配列のイメージ

行と列の2軸だと考えるとワークシートのようなものだね。

多次元配列

　三次元の場合は、縦(行)と横(列)と奥行の変数のブロック(かたまり)です。

エクセルのマクロをVBAでつくる上で三次元以上の次元をもつ配列をつかうことは少ないため、基本的には二次元配列まで理解できれば困ることはないでしょう。

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

配列3(0 To 2, 0 To 1, 0 To 1)で宣言した配列のイメージ

奥行が増えたことで立体になるイメージだね。

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

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

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

配列4(0 To 2, 0 To 1, 0 To 1, 0 To 1)で宣言した配列のイメージ

　もっとも小さい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、1、2であり、静的配列宣言2のインデックスは1、2、3となります。
変数名	インデックス	代入した値
静的配列宣言1	0	A
	1	B
	2	C
静的配列宣言2	1	AA
	2	BB
	3	CC

VBEのローカルウィンドウの画像

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“の文字列を出力しています。

実行結果は次のとおりです。

実行結果

A
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次元のA
2次元のB
2次元のC
2次元のD
2次元のE
2次元のF
2次元のG
2次元のH
2次元のI

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

代入時と同じく、それぞれの次元でインデックスを指定するんだね。

動的配列を宣言する方法

　これまでは要素数が変動しない静的配列の基本的な使い方を紹介してきました。

今度は、配列の要素(値)の数がきまっていないときなどに用いることができる動的配列について説明します。
動的配列は、どれだけ要素数をつかうかが決まっていない場合や、途中で変化するような処理が必要な場面で有効に使うことができます。

静的配列のときと同じく、まずは宣言方法から見ていきましょう。

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

  

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

VBAの動的配列で配列の再定義をせずにインデックスを指定して値を代入しようとした場合のエラー画面

静的配列のときと同じようにしても値の代入ができないね。

動的配列は宣言するだけでは使えない

　直前に書いたサンプルマクロのとおり、動的配列はただ宣言するだけでは値を代入することができません。

それでは、動的配列をつかうためにはどうすれば良いか？ですが・・・
結論を言ってしまえば動的配列も値を代入する前に要素数を指定する必要があります。

要素数を指定する必要があるのであれば、静的配列と同じでは？と思われるかもしれませんが、これまにで何度もお伝えしているとおり、静的配列は宣言後に要素数を変更はできません。

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

たとえば、ある月の日数を要素数とする配列の場合、2023年1月は31日まであることから配列の大きさとして31個の箱が必要になりますが、同じ配列でも2023年2月の場合は、配列の大きさは28個になります。

また、さらに細かい部分まで言及すれば、2月の場合は、うるう年を考えておく必要があるため、毎年必ず28個の大きさとも限らないでしょう。

動的配列は処理に必要な要素数の分だけ柔軟にデータの増減に対応できるメリットがあり、最適な要素数の配列をつかうことでマクロで使用するメモリを必要最低限におさえることができます。

メモリの消費量の削減ときいてもピンとこない人も多いと思いますが、カンタンに言えば箱だけ大きくてスカスカのお弁当にはムダが多く、中身の量に合ったお弁当箱を用意することができると考えればイメージしやすいと思います。

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

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

動的配列を再定義する

　前項で動的配列をつかうためにも要素数を宣言する必要があることをお伝えしました。
動的配列で要素数を宣言することを再定義と言います。

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

  

Option Explicit

Sub 動的配列を再定義()

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

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

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

End Sub

09行目

宣言した動的配列宣言1にRedimをつけて要素数を指定しています。
こちらでは要素数として” 0 “としているため、配列の箱の大きさは1つだけです。

インデックスは0からなので要素数1つの配列になるよ。

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

ReDim 配列名(数値)

※配列名は事前の宣言した配列名を指定し、Redimステートメントをつかって再定義します。

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

ReDim 動的配列宣言1(0)

※()内の添え字によって要素数を指定しています。

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

もっと手軽に多くのデータをあつかう方法もある

　多くのデータをあつかう方法としてこの記事で紹介した配列のほかにも、コレクション(Collection)オブジェクトをつかう方法もあります。

　コレクション(Collection)は、ブックやシートなどのオブジェクトをまとめている既存のものも存在しますが、独自で作成することもでき、データの追加や削除がとても簡単にできます。

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

＼複数のデータやオブジェクトをまとめて管理するコレクション(Collection)についてはコチラ／

VBA｜コレクション(Collection)でオブジェクトをまとめて処理をする

VBAのコレクション解説ブログ：効果的なデータ管理と処理を実現するVBAのコレクションについて、基本から応用まで詳しく解説します。VBA初心者にもわかりやすい、画像やサンプルコードつきで役立つ情報満載！

動的配列に値を代入する

　動的配列を利用するには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つを宣言しています。

取得したい月
その月の最終日
その月の日数

こちらの変数をつかって指定した月の日数分の大きさ(要素数)の配列にしていきます。

17行目、20行目、23行目

宣言した3つの変数にそれぞれの値を代入しています。

詳しい内容については以下の表をご覧ください。

宣言した変数と用途

変数名	代入する値
取得したい月	2023/2/1
その月の最終日	DateSerial(Year(取得したい月), Month(取得したい月) + 1, 0)
その月の日数	Format(その月の最終日, “d”)

27行目

Redimステートメントをつかって、動的配列の大きさ(要素数)を再定義しています。
ここでは、変数のその月の日数をインデックスとして利用しています。

31行目

くり返し文をつかって動的配列に値を代入しています。
2023年2月の日数分なので大きさ(要素数)は28なので、動的配列宣言1のインデックスの1から28までに値を代入しています。

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

例えば、取得したい月の値が”2023/1/1“であれば、日数が31なので要素数は31です。
つまり取得したい月の値が”2023/2/1“であれば、日数が28となることから要素数も28です。

※動的配列でも、静的配列と同じくインデックスは0からですので、サンプルコードでは配列を再定義したときに添え字でインデックスを1からに指定しています。

左の画像が2023/1/1、右の画像2023/2/1を指定した場合のローカルウィンドウを見比べてみると、要素数のちがいがわかります。

動的配列を再定義するときの注意点

　動的配列を再定義するときは注意点があります。
それは、配列を再定義すると直前までに代入されていた値は保持されない点です。

以下のマクロで確認してみます。

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

  

Option Explicit

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

09行目

Redimステートメントをつかって、動的配列宣言1を再定義しています。
再定義した配列の大きさ(要素数)は1つです。

18行目

Redimステートメントをつかって、動的配列宣言1を改めて再定義しています。
再定義した配列の大きさ(要素数)は2つです。

24行目・25行目

配列である動的配列宣言1の値を出力します。
インデックス0番に代入された値と1番に代入された値をイミディエイトウィンドウに出力します。

こちらのマクロを実行途中でとめて、動的配列の要素(値)を確認した結果は以下です。

実行結果

　注目してほしい点は、ローカルウィンドウの赤色の下線の部分と、イミディエイトウィンドウの赤枠の中です。

ローカルウィンドウで、1回目の配列の再定義の後に代入した値(“ひとつめの値“)が”Empty“値となっていることがわかります。

また、イミディエイトウィンドウにも”ひとつめの値”が出力されていないことから、配列である動的配列宣言1のインデックス0番に代入した値が消失したことがわかります。

つまり、Redimステートメントで配列を再定義をすると、それ以前に代入された値は保持されないことを覚えておきましょう。

配列を再定義すると代入した値が消えてしまうんだね。

動的配列の要素を保持した状態で再定義する

　配列をRedimステートメントで再定義したときは、直前までに代入していた値が保持されず消えてしまうことを説明しました。
代入した値を保持しながら動的配列を再定義したいときは、以下のように書きます。

ReDim Preserve 配列名(数値)

Preserveは「保存する」という意味だよ。

実際のマクロで動的配列を再定義する部分のコードを書きかえて確認してみます。

Preserveをつかって動的配列を再定義するサンプルマクロ

  

Option Explicit

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

18行目

Redimステートメントをつかって、動的配列宣言1を改めて再定義しています。直前のサンプルマクロとちがう点として、 Preserve をつけた状態で再定義しています。なお、配列の大きさ(要素数)は2つです。

それでは、VBEの画面で実行結果を確認してみます。

実行結果

Preserveキーワードをつけていないサンプルマクロの実行結果と比較すると動的配列宣言1のインデックス0番が保持されていることがわかります。

このように、配列を再定義するときにそれまでに代入した値を保持したい場合は、Preserveキーワードをつける必要があることを覚えておきましょう。

しかし、Preserveキーワードの使用時にも制約があるため、その点については次から説明します。

動的配列を”Preserve“をつけて再定義するときの注意点

動的配列の再定義で大きさの変更ができるのは、インデックスの上限値のみ

　次のサンプルマクロでは、宣言した配列をPreserveキーワードをつけての大きさ(要素数)を再定義しています。

Preserveで動的配列の添え字の上限を変更するサンプルマクロ

  

Option Explicit

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で動的配列の添え字の下限を変更しようとするサンプルマクロ

  

Option Explicit

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で動的配列の添え字のさいごの次元を変更するサンプルマクロ

  

Option Explicit

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で動的配列の添え字のさいご以外の次元を変更しようとするサンプルマクロ

  

Option Explicit

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

動的配列の要素数をあらわす添え字はさいご以外の次元数の変更はできない。(エラーが発生する)

さいごの次元だけ変えられるってことだね。

　動的配列を再定義するときの注意点以下の2点を抑えておくと良いでしょう。

“Preserve”キーワードをつけないとそれ以前の値は消失する
インデックスは上限値のみ変更可能で下限値を変更しようとするとエラーが発生する
インデックスはさいごの次元のみ変更可能でそれ以前の次元を変更しようとするとエラーが発生する

ワークシートのセル範囲の値から配列をつくるマクロ

　今度はエクセルのワークシートのセル範囲から、配列を作成してみたいと思います。

まずは、以下の画像をご覧ください。

A1セルからC5セルまでの範囲にそれぞれ文字列を記入しています。
こちらのセル範囲の値を配列にするコードを書いてみます。

セル範囲から配列をつくるサンプルマクロ

  

Option Explicit

Sub セル範囲から配列()

 '変数の宣言
 Dim セル範囲から配列 As Variant

 セル範囲から配列 = ThisWorkbook.Worksheets("配列出力").Range("A1:C5")

End Sub

06行目

変数を宣言しています。ここではセル範囲から配列という名前にしています。
宣言時のポイントとして、”()“や”(1 To 3)“などインデックスの大きさは書かないようにします。

また、データ型はRange型などでも可能ですが、使い勝手の良い”Variant(万能)“型を指定しています。

08行目

ワークシートのセル範囲を変数に代入しているコードになります。
変数への代入式なので、変数名＝(イコール)　代入する値で書きます。

変数のデータ型の種類やそれぞれの型についてなどは、別の記事で紹介していますので、そちらを記事をご覧ください。

エクセルVBA｜変数の宣言と値やオブジェクトを代入する基本の使い方

エクセルVBAにて変数の宣言と使い方について初心者にもわかりやすく解説しています。これからVBAを覚えていきたい人に向けて変数とは何か、変数を扱う時のコードの書き方や値の代入、変数を使った計算や文字列結合の書き方など基本的な部分に焦点を当てています。

以下の画像にて、ローカルウィンドウで配列の要素(値)を確認してみましょう。

サンプルマクロを実行した後のローカルウィンドウ

　こちらのセル範囲から配列のインデックスは0からではなく、1から始まっています。
セル範囲を配列として代入した場合はこのような動きになります。

配列の宣言において下限値の指定をしなければインデックスは0からとなりますので、セル範囲から配列を代入するときと下限値が異なっている点についてはおさえておきましょう。

セル範囲の値を配列にして別の範囲にセルに出力する

　さきほどセル範囲の値からつくった配列を出力してみます。

ワークシートのＡ1セルからＣ5セルの範囲の値から配列をつくりましたので、その配列の値をA7セルからC11セルの範囲に出力します。

セル範囲からつくった配列を出力するサンプルマクロ

  

Option Explicit

Sub セル範囲から配列()

　'変数の宣言
　Dim セル範囲から配列 As Variant

　'セル範囲の値を配列に代入する
　セル範囲から配列 = ThisWorkbook.Worksheets("配列出力").Range("A1:C5")

　'配列(A1セルからC5セルの値)を他のセル範囲に出力する
　ThisWorkbook.Worksheets("配列出力").Range("A7:C11").value = セル範囲から配列

End Sub

12行目

配列の値をワークシートに書き出してみます。
セル範囲から配列に代入されている値をワークシートのA7セルからC11セルに書き出します。

セル範囲から値を取得して配列をつくりましたが、その配列を指定のセル範囲に出力した結果の画像は以下のとおりです。

　薄い青色で着色した範囲(A7セルからC11セル)に、配列の値が出力されました。
配列の要素(値)を一括で出力する方法は、大量のデータを書きだすマクロにおいて、プログラムの実行速度を向上させる効果が高いので、使えるようにしておきたい方法です。

　配列の使用例として、以前の記事でVLookup関数と同じはたらきをするサンプルコードで紹介している記事もありますので、配列の使ったマクロを開発をするときはコチラの記事もご覧になってみてください。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか？

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか？
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「１日速習講座」がおすすめです。

詳しくはこちら

さらに配列を使いこなすテクニック

　ここからはエクセルVBAで配列をつかったマクロをつくる上で覚えておくと良いものをいくつか紹介します。
配列をさらに使いこなすために便利な関数とテクニックになりますので配列とあわせて覚えておきましょう。

配列をつかうときに知っていると便利なものを紹介するよ。

インデックスの下限を取得できるLBound関数

　LBound関数は配列のインデックスの下限値を返す関数です。
返す値はインデックスをあらわす数値となります。

たとえば、配列(2)という宣言をした静的配列でLBound関数を使った場合は0が返ります。
また、配列(1 To 2)でという宣言をした静的配列の場合は1が返ります。

LBound関数の構文は以下のとおりです。

LBound(配列名,[,次元数])

配列名はインデックスの下限を返したい配列の名前を記入する。[]内の次元数は任意です。

LBound関数で配列の添え字の下限値を取得するサンプルマクロ

  

Option Explicit

Sub 配列テクニック()

　Dim サンプル配列(9)
　Dim 下限 As Long

　下限 = LBound(サンプル配列)
　Debug.Print 下限

End Sub

08行目

LBound関数をつかってサンプル配列のインデックスの下限値を取り出し、変数の下限に代入します。

　このマクロを実行した結果、イミディエイトウィンドウに0が返ってきます。

配列のサンプル配列(9)には、0～9の10コの要素数があります。

LBound関数はインデックスの下限値である数値を返します。つまりサンプル配列を宣言するときにインデックスを(1 to 10)のようにすると、LBound関数で返ってくる値は1です。

インデックスの上限を取得できるUBound関数

UBound関数は配列のインデックスの上限値を返す関数です。
返す値はインデックスをあらわす数値となります。

たとえば、配列(2)という宣言をした静的配列でUBound関数を使った場合は2が返ります。
また、配列(1 To 2)で宣言をした静的配列の場合でも2が返ります。

UBound関数の構文は以下のとおりです。

UBound(配列名,[,次元数])

配列名はインデックスの下限を返したい配列の名前を記入する。[]内の次元数は任意です。

UBound関数で配列の添え字の上限値を取得するサンプルマクロ

  

Option Explicit

Sub 配列テクニック()

　Dim サンプル配列(9)
　Dim 下限 As Long

　上限 = UBound(サンプル配列)
　Debug.Print 上限

End Sub

08行目

UBound関数をつかってサンプル配列のインデックスの上限値を取り出し、変数の上限に代入します。

　このマクロを実行した結果、イミディエイトウィンドウに9が返ってきます。
配列のサンプル配列(9)には、0～9の10コの要素数があり、インデックスは9が上限となります。

　UBound関数はインデックスの上限である数値を返す関数です。
仮に、サンプル配列を宣言するときにインデックスを(1 to 10)のように指定すると、UBound関数で返ってくる値は10です。

LBound・UBound関数をつかって要素数をもとめる

　これまでに説明したとおり、LBound関数はインデックスの下限値を返し、UBound関数は配列のインデックスの上限値を返します。これらを使うことで配列の大きさ(要素数)を求めることもできます。

以下のようなマクロで配列の要素数を求めることができます。

要素数 = UBound(配列名) – LBound(配列名) + 1

配列のインデックスが0からはじまるため、1を加算することで要素数が求められる。

サンプルコードで、サンプル配列(9)の要素数が求められるか確認してみます。

L・UBound関数で配列の要素数を取得するサンプルマクロ

  

Option Explicit

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文をつかって配列の要素の数だけ値を出力する処理のサンプルコード

配列の要素数だけ繰り返すサンプルマクロ

  

Option Explicit

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関数でインデックスの上限値を返し、変数 i が下限値から下限値から上限値までくり返すマクロです。

このサンプルマクロの場合、サンプル配列(4)のインデックスの下限値は0、上限値は4なので、カウンタ変数である i が0から4になるまでの合計5回くり返します。

配列の要素数だけくり返す処理(ForEach文)

ForEach文をつかって配列の要素の数だけ値を出力する処理のサンプルコード

配列の要素数だけ繰り返すサンプルマクロ

  

Option Explicit

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文では配列の要素数分のくり返し処理をします。
くり返し処理のための変数として宣言した Var にサンプル配列の1つずつの要素が代入されていきすべての要素の数だけ処理をくり返していきます。

ForEachステートメントを含めた繰り返し文については、別の記事で詳しく解説しています。
以下のリンクをクリックすることで該当する記事にジャンプできます。

エクセルVBA｜For nextやDo Loopで繰り返し処理マクロをつくる

プログラミングでは必須となるくり返し処理ですが、VBAでマクロを作るときも例外ではありません。くり返し処理を実行するマクロが利用できれば、驚くほど作業効率があがる可能性があります。この記事では、VBAのくり返し処理の実装方法を紹介しています。

Eraseステートメントで配列の初期化をする

　Eraseステートメントをつかうと、配列に代入された値は初期化されます。

Eraseステートメントを実行すると、配列に含まれる要素が削除されて初期値に戻ります。

配列を初期化するための構文

Erase 配列名

　以下のサンプルコードでは、サンプル配列に5つの文字列を代入して、くり返し処理であるFor Each文で出力しています。

そのあと、Eraseステートメントをつかってサンプル配列を初期化します。
Eraseステートメントの直後にStopステートメントで処理を中断させます。

中断した時点でのローカルウィンドウを確認してみましょう。

Eraseステートメントをつかったサンプルマクロ

  

Option Explicit

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関数をつかったサンプルマクロ

  

Option Explicit

Sub 配列テクニック4()

 Dim サンプル配列() As Variant
 Dim var As Variant

 サンプル配列() = Array("ぜろ", "いち", "に", "さん", "し")

 For Each var In サンプル配列
     Debug.Print var
 Next var

End Sub

08行目

Array関数で引数に書いた文字列を配列にしています。
こちらのサンプルマクロでは、”ぜろ”、”いち”、”に”、”さｎ”、”し”の5つの文字列をサンプル配列に代入しています。

Filter関数で指定した要素を配列内から取り出す

　Filter関数は、配列内の要素を検索して条件に一致した要素を取り出すことができます。
二次元配列や完全一致の要素を抽出するのであれば、Forループで処理する方法ですがFilter関数は部分一致の場合に利用できます。

Filter関数の構文

Filter(抽出もとの配列,検索文字列,True or False,0 or 1)

　指定した配列の要素に対して抽出が可能です。
抽出もとの配列と条件文字列の引数は必須で記入する必要があり、第3引数以降は任意です。

3つめの引数はブール型(True or False)で指定ができます。
条件文字列を含む要素を抽出する場合はTrueを指定、含まない要素を抽出する場合はFalseを指定します。

最後の引数は比較モードを指定でき、0の場合はバイナリ比較で、1の場合は大文字と小文字を区別しないテキストモードでの比較です。

Filter関数をつかったサンプルマクロ

  

Option Explicit

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

　このマクロを実行すると、検索文字列に”チュー“を指定したため、抽出後の配列に”ピカチュー“と”ライチュー“が代入されます。
一方、引数にFalseを指定した場合は、”ピカチュー“と”ライチュー“以外(“チュー”を含まない)の要素で抽出後の配列がつくられます。

Filter関数で第3引数をTrueに指定したときの結果

“チュー”を含む文字列の”ピカチュー”と”ライチュー”が抽出後の配列として取り出されました。

Filter関数で第3引数をFalseに指定したときの結果

“チュー”を含まない文字列が要素の抽出後の配列として取り出されました。

技術評論社

パーフェクトExcel VBA (PERFECT SERIES)

Amazonで見る楽天市場で見る

配列の利用方法まとめ

　ここまで、エクセルVBAにおける配列の基本の使いかたについて説明してきました。

配列は変数のかたまりのようなもので、たくさんの値をあつかうことができる非常に便利なものです。
また、マクロの処理速度を向上させることにも使えるので、たくさんのデータをあつかった処理をするときに活躍してくれるでしょう。

さいごに最低限おさえておきたいポイントを箇条書きするので、おさらいしておきましょう。

配列の種類について
- 静的配列は大きさが固定された変数のかたまり
- 動的配列は大きさが変更できる変数のかたまり
配列の次元数について
- 配列の次元数は最大60まで、それ以上だとエラーが発生する
- エクセルのワークシートをあつかう処理なら、二次元配列までつかえれば問題なし
配列をつかったマクロでおぼえておきたいこと
- セル範囲や、Array関数で配列もつくれる
- 配列の要素インデックスは指定がなければ0から、セル範囲の値を代入した配列のインデックスは1から始まる
- インデックスの下限値はLBound関数、上限値はUBound関数で取得できる
- 配列内の要素の数だけ繰り返す処理はFor文で実現できる