Excel VBA 入門 その6-6 : 配列とは何かについて学びましょう - 便利なツールで事務処理をスピーディーに!

Excel VBA 入門 その6-6 : 配列とは何かについて学びましょう

StockSnap / Pixabay


皆さまこんにちは!

このサイトの管理者のHide(ヒデ)と申します。

今回は、Excel-VBAで利用すると便利な配列という考え方についてお伝えしたいと思います。

これまでのブログで変数という考え方をお伝えしてきましたが、配列はこの変数の考え方を拡張したものになります。配列について難しく感じる方もおられますが、これは配列が難しいというよりは、配列をどのような場面で利用してよいか分からないから難しく感じるということかなとも思います。

それでは、配列とはどのようなもので、どのような場面で利用すると便利か見ていきましょう。

スポンサーリンク
Simplicityのレクタングル広告(大)

1. 配列とは

1-1. 配列の概要について学びましょう

配列とはこれまでのブログでお伝えしてきた変数と同じなのですが、変数に番号を付けて管理できるようにしたものです。配列は、配列変数と呼ぶ事もあります。通常の変数には1つの値しか格納できませんが、配列は「1つの変数名」と「利用する最大番号」を組み合わせて宣言することで、複数の値を格納できるようになります。このように変数に番号を付けられることから、順番や順位に対応するデータを管理するのに相性が良いと言えます。

それでは、まず、配列の宣言方法についてご紹介したいと思います。

<配列の宣言の例>
Dim NumArr(5) As Long

配列の宣言ですが、上記のようになっていて

「Dim」+「半角スペース」+「自分で決めた配列の名前」+「(」+「利用する最大番号」+「)」+「半角スペース」+「As」+「半角スペース」+「型名」

のように記述します。

まず、今回の配列の宣言ですが、NumArrの部分が配列名で、()の中の数字「5」が配列の最大利用番号となります。この数字の部分ですが、以前のブログでお伝えした「定数(名)」で指定することも可能です。この配列で指定する番号の部分を添え字(そえじ)ということがありますので覚えておいてください。

定数について解説したブログはこちら

では、このように配列を宣言することで、どのような状態になるのでしょうか。

これは、パソコンのメモリ上のお話になりますが、

NumArr(0) , NumArr(1) , NumArr(2) , NumArr(3) , NumArr(4) , NumArr(5)

というLong型の整数を格納できる場所が6つ確保されます。この個々の配列変数を「要素」と呼ぶことがあります。注目すべき点は配列の添え字が「0」から利用できるということです。実は、プログラミングの世界では「1」スタートではなく「0」スタートが多いのです。今回ご紹介した配列ですが、一般的に1次元配列と呼ばれます。2次元,3次元配列もありますが、本ブログでは最も基本的な1次元配列を使って配列の使い方や特徴を解説しています。

プログラミングのする方や開発現場の考え方にもよりますが、添え字が「0」の配列を必ず使うようにする場合もあれば、あえて利用しないこともあります。

添え字が「0」の配列を利用しないケースとしては、例えば、文字を格納できるString型の配列を用意して「マラソン大会でゴールした順に氏名を格納する場合」、添え字の「1」番目から順にデータを格納した方が、添え字と順位が一致するので分かりやすいというケースがあります。

もしこのケースで添え字の「0」から利用する場合、配列に格納する際に添え字の部分を「順位-1」という計算をしながら格納する必要があります。逆に画面に順位を表示したりするときは「添え字+1」の計算を考慮しなければならないこともあります。

次に、型名についてですが、上記の宣言で指定されているLongは整数を格納する型でしたね。今回の例では型としてLongを指定していますが、String等、他の型を指定することも可能です。この点は通常の変数と全く変わりません。型名が何かという点については下記のブログもご覧ください。

型名について解説したブログはこちら

補足その1:
dim,as,型名の部分は半角で入力します。また小文字で入力しても問題ありません。自動的にDim,Asのように先頭が大文字に変換されます。

補足その2:
配列名は変数と同様には一般的には半角の英数文字を用います。配列名には予約語(※1)が利用できない,先頭を数字にできない等、若干制限はありますが、自由に決めることができます。もし配列名が文法的に間違っている場合はエラーとなります。今回NumArrという配列名にしていますが、配列のことを英語でArrayと呼ぶことから、それを短縮した「Arr」等の名前を配列名の最後に付ける慣習あります。

※1について:予約語とはExcel-VBAの文法で利用される「Sub」や「If」などの既に役割が決まっている用語のことです。

補足その3:
今回、配列の宣言時に最大利用番号として「5」を指定していますが、このようにあらかじめ配列の利用範囲を指定して宣言した配列を静的配列と呼びます。これは、配列を利用するに当たって格納したい項目数がほぼ決まっている場合の宣言方法です。ただ、プログラム実行時点で項目数が分かっていないのに大体の最大利用番号を指定して宣言し、静的配列を使うと利用しない添え字の配列が発生したり、逆に不足してエラーを発生させる要因にもなります。これに対してプログラム実行中に必要な分だけ利用範囲を増加させながら利用する配列を動的配列と呼びます。動的配列については後述します。この静的配列と動的配列の特徴を理解してどちらを使った方がよいかの判断材料にしましょう。

1-2. 配列で利用できる便利な関数を学びましょう

さきほど「Dim NumArr(5) As Long」という配列の宣言の例をお伝えしました。この時、配列の添え字の部分の数字において「利用可能な最小番号」と「利用可能な最大番号」を教えてくれる関数があります。それがLBoundとUBoundという関数です。関数というと難しく聞こえますが使い方は簡単ですので、下記プログラム(Test1)で使い方を見ていきましょう。

Sub Test1()

Dim NumArr(5) As Long

Debug.Print "利用可能な最小番号=" & _
LBound(NumArr)

Debug.Print "利用可能な最大番号=" & _
UBound(NumArr)

End Sub

このプログラムを実行すると、イミディエイトウィンドウに下図のように表示されます。

プログラムの解説に入る前に、1点補足があります。今回のプログラムですがいつもの書き方と少し違う点がありますね。2つある「Debug.Print」の命令の行の末尾に「_」があり、改行されてLBound関数とUBound関数が記述されています。これは、行の終わりに半角の「_」(アンダーバー)を入力することで、残りの命令を次行に記述することが可能です。これは、1行に記述するプログラムが長くなってきた場合に利用される方法ですので、ぜひ覚えておいてください。「_」の前にはスペースが必要です。また、ある文字の途中で「_」を使い、残りは次行からということはできませんのでご注意ください。今回のプログラムでは、特に2行にまたぐ必要はありませんでしたが、本ブログでは「_」による改行の説明を兼ねて使用しています。

それではプログラムの解説に入りますね。LBound関数はLBound(NumArr)のように()の中に配列の名前を書くことで、配列で利用可能な最小番号を、UBound関数はUBound(NumArr)のように()の中に配列の名前を書くことで、配列で利用可能な最大番号をそれぞれ教えてくれます。ただ、「Dim NumArr(5) As Long」と宣言した時点で、LBound関数やUBound関数を使わなくても利用可能な最小番号,最大番号はそれぞれ直感的にそれぞれ「0」と「5」とわかってしまいますね。ではどのようなケースで利用するかというと、例えば、プログラム実行中に配列の要素数が変化するような動的配列などでよく利用されます。

LBound関数やUBound関数の注意点として、下記のようなTest2というプログラムがあったとします。

Sub Test2()

Dim NumArr(5) As Long

NumArr(1)=10
NumArr(4)=20

Debug.Print "利用可能な最小番号=" & _
LBound(NumArr)

Debug.Print "利用可能な最大番号=" & _
UBound(NumArr)

End Sub

このプログラムを実行された際に表示される結果も、先ほどご紹介したTest1と同じということです。上記のプログラムで配列の1番と4番の要素を初期化しているので、値を設定している最小と最大の添え字の番号というイメージでLBound(NumArr)が「1」を,UBound(NumArr)が「4」を教えてくれそうですが、あくまでLBoundとUBound関数はメモリ上に存在している利用可能な箱の上限と下限である「0」と「5」を教えてくれることに注意しましょう。

ここまでで、配列とは何かという部分について簡単に見てきました。では、配列はどのようなケースで役に立つのか、通常の変数と何が違うのかという点について具体的な例で見ていきましょう。

2. 配列をデータの並び替えに利用する

配列の特徴を学ぶ際、他のプログラミング言語でもデータの並び替えを使った題材がよく利用されます。データの並び替えはExcelのシートの機能でも可能で、実際のプログラミングでもこの機能が利用されますが、配列を使った並び替えの動きを理解することはメリットも大きいため、あえて取り上げました。

2-1. データの並び替えの考え方

ここでは、配列の特徴をお伝えするために配列を使って数字を小さい順にに並べるプログラムを考えてみます。

例えば、9,6,1,4,7 の順にならんだ5つの数字を小さい順に並び替えることをプログラムで実現する場合、下記のような順番のついた箱に数字を入れて考えると分かりやすいことがあります。まずは数字を箱に入れてみましょう。

これで、順番のついた箱に数字が入りました。この時点で、数字の並びはバラバラですが、最終的に箱の上に書かれた順番通りに小さい順に数字が入ることを目指します。

では、ここからどのように数字を小さい順に並び替えていくかということですが、色々方法がありますが、例えば「1番目の箱」に入った数字と「2番目から5番目の箱に入った数字」を比べて、現在「1番目の箱」に入っている数字より「比べた他の番号の箱」の数字が小さいときだけ、両者の箱の中身を交換します。ポイントは、データを交換しながらも常に1番目の箱とデータを比較し続けるという点です。この操作を終えた段階で「1番目の箱」に5つの数字の中で1番小さい数字が入り、1番の箱に入る数字が確定します。

ここまでで、1番目の箱の数字は確定しましたね。そこで、今度は2番目から5番目の箱にスポットを当てます。「2番目の箱」に入った数字と「3番目から5番目の箱に入った数字」を比べて、現在「2番目の箱」に入っている数字より「比べた他の番号の箱」の数字が小さいときだけ、両者の箱の中身を交換します。ポイントは、データを交換しながらも常に2番目の箱とデータを比較し続けるという点です。この操作を終えた段階で「2番目の箱」に4つの数字の中で1番小さい数字が入り、2番の箱に入る数字が確定します。

同じような操作をこれ以降も続けていき、最終的に4番目と5番目の箱を比較して5番目の箱の中身が4番目の箱の中身より小さければ両者を交換し、数字を小さい順に並び替える操作が完了します。

補足:今回は比較対象を先頭に固定して他の項目を比較しながら進める方法をご紹介していますが、隣同士の項目を比較しながら進める方法もあります。

2-2. 配列に置き換えて考えてみる

それでは、先ほどまでのデータの並び替えの考え方を配列に置き換えて考えてみましょう。実は、先ほど出てきた順番の付いた箱がそのまま配列に置き換えられるんです。そのイメージを下図に示しました。

これはExcel-VBAで整数を扱う配列として「Dim a(5) As Long」という配列の宣言をして、各配列の要素に数字を代入(格納)した状態とお考えください。このように格納した要素をプログラムを使って並び替えていくことになります。

2-3. プログラムの記述例

ここからは、配列を使ってデータを並び替えるプログラムの記述例をお伝えします。まずはプログラムを先にご紹介しますね。現時点で意味がわからなくても大丈夫です。(※今回は説明を簡単にするために配列名も単純なものとし、定数などを使わずに記述しています。)

Sub TestArr1()

Dim a(5) As Long
Dim i As Long
Dim j As Long
Dim Temp As Long

a(1) = 9
a(2) = 6
a(3) = 1
a(4) = 4
a(5) = 7

For i = 1 To 4

    For j = i + 1 To 5

        If a(i) > a(j) Then

            Temp = a(i)
            a(i) = a(j)
            a(j) = Temp

        End If

    Next j

Next i

For i = 1 To 5

    Debug.Print i & "番目の数字=" & a(i)

Next i

End Sub

上記のプログラムについて、説明のために各行の先頭に行番号を付けたのが下記になります。※便宜上、行番号を付けていますが、実際には記述しませんのでご注意ください。

01. Sub TestArr1()
02.
03. Dim a(5) As Long
04. Dim i As Long
05. Dim j As Long
06. Dim Temp As Long
07.
08. a(1) = 9
09. a(2) = 6
10. a(3) = 1
11. a(4) = 4
12. a(5) = 7
13.
14. For i = 1 To 4
15.
16.     For j = i + 1 To 5
17.
18.         If a(i) > a(j) Then
19.
20.             Temp = a(i)
21.             a(i) = a(j)
22.             a(j) = Temp
23.
24.         End If
25.
26.     Next j
27.
28. Next i
29.
30. For i = 1 To 5
31.
32.     Debug.Print i & "番目の数字=" & a(i)
33.
34. Next i
35.
36. End Sub

それではプログラムについて解説していきますね。

<解説>
01,36行目について:
01行目はプログラムの先頭を表し、36行目はプログラムの終端を表しています。

03~06行目について:
03行目のDim a(5) As Long の記述により配列名をaとしてa(0),a(1),a(2),a(3),a(4),a(5)という整数を格納できる6つの箱(メモリ領域)が確保されます。また、宣言直後の各配列の要素の内容は「0」となります。

04,05行目はFor文で利用するためのループ変数の宣言になります。今回のプログラムではFor文が2重ループになっています。2つのFor文をコントロールするためには2つのループ変数が必要となるため、i,jという2つの変数を準備しています。2重ループの用語について初めて聞いた方は下記のブログでご紹介している九九の計算のプログラムもご覧ください。

2重ループについて解説しているブログはこちら

06行目は数値を一時的に格納するための変数です。英語で「一時的」をtemporaryということから変数名をTempとしています。プログラミングでは一時的なものを扱う変数などに「Temp~」と付けることが良く行われます。

02,07,13,15,17,19,23,25,27,29,31,33,35行目について:
この行は空白行といってプログラムを見やすくするための行となります。この行は命令ではないため何も処理は実行されません。

08~12行目について:
08~12行目で各配列の要素を初期化しています。このように記述することで配列に数値を代入できます。例えば08行目の a(1) = 9 という左辺と右辺が「=」で結ばれた記述は変数についてのブログでも出てきましたね。左辺と右辺が「=」で結ばれた式の場合、Excel-VBAでは右辺の内容が左辺の変数に代入されます。この式の場合、右辺は「9」,左辺の変数はa(1)ですから、最終的に配列変数a(1)の内容は「9」になります。(正確にはa(1)の内容が「0」から「9」に更新されます。)

※今回のプログラムではa(0)という配列要素を利用していない例となります。

14~28行目について:
少し複雑に感じるかもしれませんが、この部分で配列に格納された数値を小さい順に並べ替えています。先ほど「データの並び替えの考え方」のところでお伝えした考え方を配列を使ったプログラムで表現したのがこの部分です。数字を小さい順に並べるために、なぜこのように記述するのか、なぜFor文を使った2重ループになるのかいきなり理解するのは難しいと思います。そこで、この部分をFor文を使わずに書いてみるとその構造が少しずつ見えてきます。

まず初めにa(1)という配列要素とa(2)~a(5)までの配列要素を比較してa(1)の要素より小さい要素があれば、a(1)とその比較対象の要素を交換します。この部分をFor文を利用せずに書いてみると下記のようになります。

If a(1)>a(2) Then
    Temp=a(1)
    a(1)=a(2)
    a(2)=Temp
End If

If a(1)>a(3) Then
    Temp=a(1)
    a(1)=a(3)
    a(3)=Temp
End If

If a(1)>a(4) Then
    Temp=a(1)
    a(1)=a(4)
    a(4)=Temp
End If

If a(1)>a(5) Then
    Temp=a(1)
    a(1)=a(5)
    a(5)=Temp
End If

次にa(2)という配列要素とa(3)~a(5)までの配列要素を比較してa(2)の要素より小さい要素があれば、a(2)とその比較対象の要素を交換します。この部分をFor文を使わずに書くと下記の通りとなります。

If a(2)>a(3) Then
    Temp=a(2)
    a(2)=a(3)
    a(2)=Temp
End If

If a(2)>a(4) Then
    Temp=a(2)
    a(2)=a(4)
    a(4)=Temp
End If

If a(2)>a(5) Then
    Temp=a(2)
    a(2)=a(5)
    a(5)=Temp
End If

...

このような流れで、配列の要素同士を比較して並び替えていきます。そして一番最後に来る比較は下記のようになります。

If a(4)>a(5) Then
    Temp=a(4)
    a(4)=a(5)
    a(5)=Temp
End If

ここで、何度も繰り返し出てきたIf文についての解説になりますが、IfとEnd Ifで囲まれた部分は3行あって書き方が共通していますね。この部分でデータの交換を行っているのですが、データを交換するのになぜ3行必要なのでしょうか。例えば最初のIf文で出てきた「a(1)>a(2)」という条件が成立して両者のデータを交換する場合、下記の2行の記述だとどうなるでしょうか。

a(1)=a(2)
a(2)=a(1)

実は、上記の記述は正しいように見えて間違いなのです。最初の「a(1)=a(2)」の行の命令を実行した瞬間に配列a(1)の要素の内容がa(2)の要素の内容で上書きされてしまうのです。そのため次の行の「a(2)=a(1)」では元々のa(2)と同じ内容がa(2)に代入されるという意味のないことになってしまいます。そこでプログラミングの世界では、元々の配列変数の内容を一時的に退避するための変数を準備し、その変数に代入して保管しておくことでこのような問題が起きないようにします。そのため、a(1)という要素とa(2)という要素の内容を交換する場合は下記のように記述します。

Temp=a(1)
a(1)=a(2)
a(2)=Temp

このようにa(1)の要素の内容を一時的に変数Tempに保管することで、a(1)の要素がa(2)の内容で上書きされても、元々のa(1)の内容が変数Tempに残っているので、それをa(2)に代入すればよいわけです。

ここまでで、IF文を用いた配列のデータの並べ替えについて見てきましたが、これがなぜFor文による2重ループになるのかについて考えてみます。

まず、IF文の条件式で比較している左辺と右辺の配列aに指定された添え字の番号の動きに注目してみましょう。すると下記のような特徴があることがわかります。

左辺の添え字が「1」の時:右辺の添え字は「2」から「5」まで変化させて比較している。
左辺の添え字が「2」の時:右辺の添え字は「3」から「5」まで変化させて比較している。
左辺の添え字が「3」の時:右辺の添え字は「4」から「5」まで変化させて比較している。
左辺の添え字が「4」の時:右辺の添え字は「5」で比較している。

補足:左辺と右辺の添え字がともに「5」の場合は同じ配列を比較しても意味がないので比較不要。

この動きですが、このように考えることができます。左辺の添え字の値を一旦固定して右辺の添え字の値を1ずつ増やしながら変化させている。この動きは、以前For文のブログでお伝えした九九の計算と同じ動きなんですね。「ある数字に一旦固定しながら、もう一方の数字を均等なステップで変化させる」という動きになっていたらFor文の2重ループが使えるなと発想できるようにしましょう。

九九の計算について解説しているブログはこちら

つまり、「ある数字に一旦固定しながら」という部分は2重ループにおける外側のFor文のループ変数に該当し、「もう一方の数字を均等なステップで変化させる」という部分は内側のFor文のループ変数に該当します。後は、2つのFor文のループ変数の初期値と終了値をどうセッティングすればよいかを考えればよいわけです。

では、まず2重ループの外側のFor文のループ変数をiとして、その初期値と終了値に何をセットすればよいか考えてみましょう。このループ変数iは前述の左辺の添え字に該当します。ということは初期値は「1」,終了値は「4」にすればよいことがあります。初期値はプログラミング的に言うならば、「配列に値を格納した最小利用番号」、終了値の「4」は、プログラミング的に言うならば「配列に値を格納した最大利用番号-1」ということになります。

次に内側のFor文のループ変数をjとしてその初期値と終了値に何をセットすればよいか考えてみましょう。まず、初期値ですが、よく観察すると外側のFor文で一旦固定しているループ変数iの値より常に1つ多い数になっていることがわかります。ということは内側のFor文のループ変数jの初期値は外側のループ変数iを利用して「i+1」と表現することができます。ではループ変数jの終了値はどうかというと全て「5」で終わっていますから「5」でよさそうですね。終了値の「5」は、プログラミング的に言うならば「配列に値を格納した最大利用番号」ということになります。

ここまでで、2重ループにおける2つのFor文の骨格が決まりました。後は内側のFor文の中で下記のようにIF文を記述すればよいことになります。

If a(i)>a(j) Then
    Temp=a(i)
    a(i)=a(j)
    a(j)=Temp
End If

30~34行目について:
この部分で配列を並び替えた後の各要素を1つずつ表示しています。このFor文でもループ変数として変数iが使われていますが、並び替えでの利用が終わった後での利用なのでこの例のように使いまわしても問題ありません。実際のプログラミングでは別のループ変数を宣言しても良いです。

それではTestArr1のプログラムをVBEのコード画面に入力して動かしてみましょう。

2-4. 実際にプログラムを動かしてみましょう

2-4-1. モジュールを追加する

今回、VBEの画面で下図のように新しいモジュールを追加して、赤枠部(1番)のように名前を「Mo06_Array」としました。また、モジュールを追加した際に赤枠部(2番)のようにコード画面の先頭に「Option Explicit」という文字が挿入されていることを確認してください。

モジュールの作成方法や名前の変更方法については、下記のブログをご覧ください。

モジュールの作成方法について解説したブログはこちら
モジュール名の変更方法について解説したブログはこちら

2-4-2. プログラムを入力する

モジュールが追加できたら、TestArr1のプログラムをVBEのコード画面に入力してみましょう。入力すると下図のようになります。

2-4-3. データが交換される様子を確認する

プログラムは入力できましたでしょうか。それではプログラムを動かしてみましょう。通常、プログラムの動かす場合、動かしたいプログラムの中(このプログラムであれば、Sub TestArr1() と End Sub の間)にカーソルを置いてF5キーを押します。

この方法でもよいのですが、今回はステップ実行というプログラムを1行ずつ動かせる便利な機能を使って動かしてみたいと思います。カーソルを上記のようにプログラムの中に置いた状態でキーボードのF8キーを1回押してみてください。すると下図のように、プログラムの先頭が黄色でハイライトされました。

ここからは、TestArr1のプログラムのポイントとなる部分について1行ずつステップ実行で確認してみたいと思います。では、ここでF8キーを1回押してみましょう。すると下図のように「a(1) = 9」の行がハイライトされました。この行は配列変数a(1)に値を代入する行ですがまだ実行されていません。

ここで、VBEの画面下方にある「ローカルウィンドウ」を確認してみましょう。ローカルウィンドウの状態を見てみると下図のようになっています。ローカルウィンドウは下図の赤枠部のようにプログラムで宣言した変数の現在の内容が表示されるのでしたね。変数i,j,Tempの内容は変数の宣言直後のため「0」になっていることが分かります。また、配列変数「a」も表示されていますが、先頭に「+」マークがあるのでクリックしてみましょう。

すると、下図の赤枠部のように配列変数「a」の添え字の「0」~「5」までの各要素の内容が分かりやすく表示されます。この時点で各要素の内容は全て「0」になっていることがわかりますね。

それでは、続いて下図のように最初のFor文の開始位置がハイライトされるまでF8キーを押してみましょう。

この時点で配列変数a(1)~a(5)までに値が代入されたはずですね。ではこの点をローカルウィンドウで確認してみましょう。すると下図のように各要素の値が「0」から代入後の値に変化していることがわかります。

では、続いて下図のように2つ目の(内側の)For文の中にあるIf文の条件式がハイライトされるまでF8キーを押してみましょう。

この時点で、2つのFor文のループ変数iとjは何になっていると思いますか。If文の開始位置がハイライトされたのは今回が初めてですから、ループ変数iとjは共に初期値のはずですね。ということはループ変数iは「1」,ループ変数jは「i+1」,すなわち「2」と推定されます。では、実際にそれぞれの変数にマウスカーソルを近づけて確認してみましょう。(ローカルウィンドウを確認しても同じです。)

すると下図のように確かに変数iは「1」,変数jは「2」になっていることが分かります。

つまり、ここでは一番最初の配列変数の比較であるa(1)とa(2)の内容の比較が行われていると分かります。ではそれぞれの配列変数の要素にマウスを近づけて内容を確認してみましょう。(ローカルウィンドウを確認しても同じです。)

すると下図のようにa(1)の内容は「9」,a(2)の内容は「6」となっています。これは配列の内容がまだ初期化した時点のものであることを表しています。a(1)の内容よりもa(2)の内容の方が小さいですから、If文の条件式が成立し、a(1)とa(2)の中身を交換する必要があることが分かりますね。

では、下図のようにEnd If の部分がハイライトされるまでF8キーを押してみましょう。その際にIf文の中に記述されている3つの代入式の右辺にあるa(i),a(j),Tempの値を確認しながら進めてみましょう。

End If の行まで、プログラムを動かしてみましたが、If文の中に記述された3行において下記の処理が行われています。

Temp=9 ※「9」はa(1)の値です。
a(1)=6  ※「6」はa(2)の値です。
a(2)=9  ※「9」はTempの値です。

ということは、上記の3行の値の代入処理によりa(1)とa(2)の内容が交換されたはずですので、ローカルウィンドウで確認してみましょう。すると下図の赤枠部のように確かにa(1)とa(2)の内容が交換されたことが分かります。

これ以降も同じような流れでデータの比較と交換が行われて行きます。では、ここでF5キーを押してプログラムを最後まで動かしてみましょう。するとイミディエイトウィンドウに配列の内容が数字の小さい順に並び替えられた状態で表示されます。

ここまで、配列を使ったデータの並び替えの動きについて見てきました。続いて、配列を使ったプログラムについてもう1つ例を見てみたいと思います。

3. 配列に調査結果を格納して順番に表示する

3-1. 事前準備

今回、配列を使ったもう1つの例をご紹介しますが、その前に事前準備があります。この手順は下記のDo While文のブログでお伝えしたのと同じ内容ですので既に作業されている方は不要となります。

Do While文のブログはこちら

まず、下図のようにCドライブの直下に「VBA-Test」というフォルダを作成します。

続いて、作成した「VBA-Test」フォルダを開き、フォルダ内でマウスを右クリックすると下図のようにメニューが表示されますので「新規作成」→「テキストドキュメント」の順にクリックします。

すると下図のように「新しいテキスト ドキュメント.txt」というファイルが作成されます。これは中身が空っぽのテキストファイルですが、同じ要領で5つのテキストファイルを作成してください。

ファイルが作成できましたら、それぞれのファイル名を下図のようにリネームしましょう。

ここまでの操作でCドライブの中の「VBA-Test」フォルダに5つのテキストファイルが作成されました。

3-2. 作成するプログラム

Do While文のブログで作成したプログラムと同様に今回も「VBA-Test」フォルダの中に存在するファイル名を表示するプログラムを作成しますが、ファイル名の先頭が「2015」から始まるものに限定して表示するようにしてみましょう。それでは、今回のプログラムをご紹介します。

Sub TestArr2()

Dim TempName As String
Dim FileNameArr() As String
Dim Idx As Long
Dim i As Long

TempName = Dir("C:\VBA-Test\2015*.*")

Do While TempName <> ""

    ReDim Preserve FileNameArr(Idx)
    FileNameArr(Idx) = TempName
    TempName = Dir()
    Idx = Idx + 1

Loop

If Idx >= 1 Then

    For i = LBound(FileNameArr) To _
          UBound(FileNameArr)

        Debug.Print FileNameArr(i)

    Next i

    Erase FileNameArr
    Idx = 0

Else

    Debug.Print "該当ファイルなし。"

End If

End Sub

上記のプログラムについて、説明のために各行の先頭に行番号を付けたのが下記になります。※便宜上、行番号を付けていますが、実際には記述しませんのでご注意ください。

01. Sub TestArr2()
02.
03. Dim TempName As String
04. Dim FileNameArr() As String
05. Dim Idx As Long
06. Dim i As Long
07.
08. TempName = Dir("C:\VBA-Test\2015*.*")
09.
10. Do While TempName <> ""
11.
12.     ReDim Preserve FileNameArr(Idx)
13.     FileNameArr(Idx) = TempName
14.     TempName = Dir()
15.     Idx = Idx + 1
16.
17. Loop
18.
19. If Idx >= 1 Then
20.
21.     For i = LBound(FileNameArr) To _
22.           UBound(FileNameArr)
23.
24.         Debug.Print FileNameArr(i)
25.
26.     Next i
27.
28.     Erase FileNameArr
29.     Idx = 0

30.
31. Else
32.
33.     Debug.Print "該当ファイルなし。"
34.
35. End If
36.
37. End Sub

では、このプログラムについて解説していきます。

<解説>
01,37行目について:
01行目はプログラムの先頭を表し、37行目はプログラムの終端を表しています。

02,07,09,11,16,18,20,23,25,27,30,32,34,36行目について:
この行は空白行といってプログラムを見やすくするための行となります。先ほどのコメント行と同じで、命令ではないため何も処理は実行されません。

03~06行目
この部分はプログラムで利用する変数を宣言する部分になります。

まず、04行目を先にご説明しますと「Dim FileNameArr() As String」の部分でファイル名を格納する配列を文字を格納できるString型で宣言しています。配列名は「FileNameArr」です。ただ、今までの配列の宣言と少し違うのは()の中に数字がありませんね。実はこれが動的配列の宣言でプログラムの実行時点で配列要素がいくつ必要か不明の場合にこのように宣言して利用します。

03行目の変数TempNameは配列に格納する前に一時的にファイル名を格納しておくために用意しました。またこの変数はDo While文のブログでもお伝えしたDo While文におけるループ変数になっています。

05行目の変数Idxは配列の添え字の番号を管理するために用意しました。配列の添え字ことをインデックス(Index)と呼びますのでこれを短縮した名前にしました。動的配列を利用する場合は、利用する配列の添え字の数値が増えていくのでこのような変数を1つ用意していくと後々便利になります。

06行目の変数iはFor文のループ変数で利用するために準備しました。

05,06行目は、共に整数を扱う変数が必要なためLong型にしています。今回のプログラムは扱う数値も小さいのでInteger型でもOKです。

08行目について:
Excel-VBAでは、このように左辺と右辺が「=」で結ばれた場合、右辺から処理されるのでしたね。この式の右辺ではDir関数を使って「C:\VBA-Test」フォルダに存在するファイル名が「2015」で始まるファイル名を1つ検索しています。そしてその結果を左辺の変数TempNameに代入します。

検索するファイル名の形式を「2015*.*」としていますが、このように記述することでファイル名が「2015」で始まるファイル名を表すことができます。(「*」は0文字以上の任意の文字を表します。)

Dir関数の詳細について下記のブログに記載しておりますので、この部分が難しく感じる方は、まず下記のブログからご覧ください。

Do While文とDir関数を組み合わせたプログラムの解説はこちら

Do While文の中にもDir関数がありますが、その前の08行目にも記述している理由は、該当するファイルが複数存在する場合に備えて最終的にはDo While文を使って何回もDir関数を繰り返し実行したいのですが、そもそも該当ファイルが1つもなければ、繰り返し実行する必要もないためです。その不必要な処理を前段階でブロックするための記述が08行目と考えてよいでしょう。08行目でファイルが何も検索されなければ、つまり、TempNameに空文字が入った場合、10行目から17行目の間にはさまれた命令は1度も実行されない動きになります。

10~17行目について:
Do While文の骨格の部分になります。10行目のDo While文の開始位置で変数TempNameの内容が「""」,つまり空文字でなければ12行目~15行目の命令を実行します。ではこの12行目~15行目について解説します。

12行目は今回初めて出てきた記述になります。「Redim Preserve FileNameArr(idx)」という記述ですが、下記のような文法になっています。

Redim Preserve 配列名(利用したい最大番号)

「Redim」というのは動的配列を生成する命令になります。また「配列名(利用したい最大番号)」のように記述することで、配列の添え字が「0」~「利用したい最大番号」(※2)までメモリが確保されます。「Redim」と「配列名」にはさまれた「Preserve」(※3)の部分はこれまで記憶していた配列の内容をクリアせずに「利用したい最大番号」まで配列を拡張(※4)したい場合に指定します。もし省略すると記憶していた内容をクリアした上で配列の添え字が「0」~「利用したい最大番号」までメモリが確保されます。

※2について:「利用したい最大番号」に「0」を指定した場合、添え字の番号が「0」の要素が1つ、メモリ上に確保されます。本プログラムでは添え字に「idx」を指定していますが、12行目が初めて実行された際の「idx」の内容は「0」ですので、配列の要素が1つ確保されます。今回は配列の要素を「0」からスタートする例として作成しています。

※3について: 今回のプログラムのようにPreserveを付けて「利用したい最大番号」の部分を1つずつ増やしながら配列を拡張していくという使い方が多いです。

※4について:「利用したい最大番号」について直前より値を小さくした場合は縮小になります。

13行目の式は、右辺の変数TempNameの内容を配列FileNameArrに代入しています。初回実行時のみ、変数TempNameには08行目で格納されたファイル名が入っています。

14行目の式は、右辺でDir関数を実行して、ファイル名の先頭が「2015」から始まる別のファイルの候補があるか検索します。そしてその結果を左辺の変数TempNameに格納(上書き)します。

15行目の式は、変数Idxの内容を1つ加算する式になります。右辺で既存のIdxの値に1を加算して左辺のIdxに代入(上書き)しています。この更新された変数Idxの値が次回、改めて12行目が実行される際に「利用したい最大番号」として使用されます。

17行目が実行されると10行目のDo While文の開始位置に戻ります。変数TempNameの内容が「""」(空文字)でなければ12行目~15行目が再び実行され、「""」(空文字)であれば10行目~17行目の処理を終了して19行目に進みます。

19行目~35行目について:
この部分で、今回のプログラムの目的であるファイル名の先頭が「2015」で始まってるファイルが存在していれば表示し、存在しなければ「該当ファイルなし。」と表示する部分になります。ではこの部分について解説したいと思います。

19行目でIf文の条件式が「Idx >= 1」となっています。なぜ条件式が「Idx >= 1」となっているかといいますと、該当するファイルが1つでも存在した場合、10行目~17行目の間の命令が最低1回実行されます。つまり12行目で動的配列が作成されると同時に15行目の「Idx = Idx + 1」の式により、変数Idxの値は最低でも「1」以上の値になるためです。そのため、該当ファイルがあったかどうかが、この条件式で判断できるということになります。

21行目~26行目でFor文を利用して該当ファイル数分、繰り返し画面にファイル名を表示しています。まず、先ほどご説明した「_」でFor文の開始行が2行にまたがっていますね。

21行目~22行目で、ループ変数の初期値がLBound(FileNameArr),終了値がUBound(FileNameArr)に設定されています。この部分について解説します。

初期値のLBound(FileNameArr)について、今回は配列の添え字が「0」の要素から使っているので「0」と記述しすることも可能ですが、形式的にLBound関数を使うことが多いです。また終了値については、今回、動的配列を使っているため、固定的に指定できずUBound(FileNameArr)として配列の最後の添え字の番号を求める形をとっています。

終了値については、変数Idxを活用することも可能です。但し、終了値は「Idx」ではないことに注意しましょう。変数Idxは次回動的配列を拡張する際に利用する添え字の数値が入っているため実際、メモリ上に存在している動的配列の最大の添え字より「1」だけ余分に多いのです。そのため、変数Idxを利用する場合は、終了値を「Idx-1」とする必要があります。

24行目は、Debug.Printの命令で配列に格納されたファイル名をイミディエイトウィンドウに表示しています。

26行目の Next i はFor文の終端ですね。

続いて、28行目~29行目について解説します。28行目の「Erase FileNameArr」ですが、「Erase 配列名」と書くことで動的配列をメモリから消去(完全になくす)ことができます。静的配列の場合は整数の配列の場合は全ての要素が「0」,文字の配列の場合は全ての要素が「""」(空文字)にクリアされます。29行目では、配列の添え字に利用している変数Idxを0に戻しています。この記述はDo While文に入る前の09行目に記述してもよいですね。

実は今回のプログラムでは28行目~29行目の記述は特に不要です。ただ今後、1つのフォルダからファイルを検索するのではなく、複数のフォルダから検索するようなプログラムを作成する場合、動的配列を使いまわすことがありえます。その際にこのような形で配列やそれに付随する変数をクリアしておかないと、前回検索したフォルダのファイル名の情報が残っていて不具合の原因になることがあります。ケースバイケースですが、動的配列利用後に配列やそれに付随する変数のクリアが必要でないか必ず確認するようにしましょう。

ここでもう一度、19行目のIf文のお話に戻ります。実は、このプログラムではこのIf文が必須なのです。というのが、もし該当ファイルが存在しないのに(正確に言うと動的配列がメモリ上に存在しないのに)「LBound(FileNameArr)」,「UBound(FileNameArr)」,「Erase FileNameArr」という配列に関係する関数や命令を実行するとエラーで止まってしまうのです。そのため、このIf文で動的配列が必ず存在していることを確認した上で利用していたというのが経緯となります。

31行目のElse文が実行された時には、該当ファイルがないことになりますので、33行目でイミディエイトウィンドウに「該当ファイルなし。」と表示しています。

3-3. プログラムを入力して動かしてみる

それでは、このTestArr2のプログラムを動かしてみましょう。先ほど、下図のようにVBEの画面のMo06_Arrayというモジュールに「TestArr1」という名前のプログラムを作成しました。今回のブログではこのプログラムの上側にTestArr2のプログラムを入力していきたいと思います。ちょうど赤矢印の部分に入力しましょう。

プログラムを入力すると下図の赤枠部ようになりました。

プログラムは入力できましたでしょうか。それではプログラムを動かして行きますが、今回、プログラムを実行する際に「ブレークポイント」という新しい機能を使ってみたいと思います。これは、プログラムの実行を「指定した行で一旦停止することができる」機能です。まず、下図のようにDo While文の中にある「ReDim Preserve FileNameArr(Idx)」の行の先頭の淵をクリックしてみてください。

すると、下図のように、先頭をクリックした行全体が赤茶色でハイライトされます。今回、先頭の行をクリックして設定しましたが、該当行の任意の部分をクリックしてカーソルが点滅した状態でキーボードのF9キーを押しても設定できます。この設定によりプログラムを実行すると、この行でプログラムが一旦停止します。(この行はまだ実行されない状態で停止します。)

今回、「ReDim Preserve FileNameArr(Idx)」の行をブレークポイントを設定した理由は、Do While文の条件が成立した場合の動きに絞って動きを見たかったためです。このようにブレークポイントの機能を使うことで変数の初期化など、既に、自分自身が把握済だったり結果が分かり切っている箇所を飛ばして必要な部分にまとを絞ってプログラムを動かすことが可能です。

それでは、ここでキーボードのF5キーを1回押してみましょう。すると下図のように「ReDim Preserve FileNameArr(Idx)」の行が黄色でハイライトされました。ブレークポイントが正しく動作していることがわかりますね。

これ以降では、合わせてローカルウィンドウも参照しながら進めてみましょう。この時点でローカルウィンドウの状態は下図のようになっています。

ここで、ローカルウィンドウを確認すると、ファイル名を一時的に格納する変数TempNameの内容が「2015_Diary.txt」になっていますね。これはこのプログラムの変数宣言直後に記述されている「TempName = Dir("C:\VBA-Test\2015*.*")」の式によって該当するファイル名が1つ検索され、格納された結果になります。

もう1点、注目して頂きたいのが動的配列として宣言している「FileNameArr」の値がまだ空っぽですね。ではこの点を踏まえて、キーボードのF8キーを1回押してみましょう。

すると、下図のように「FileNameArr(Idx) = TempName」の行にハイライトが移動しました。

また、ローカルウィドウの方も見てみましょう。すると先ほどの状態と異なり、下図の赤矢印で示したように配列名FileNameArrの先頭に「+」のマークが付いています。では、この部分をクリックしてみましょう。

「+」マークをクリックした後のローカルウィンドウの状態が下図になります。ここで、赤枠で囲んだ部分に注目してください。FileNameArrの値が「""」,つまり空文字になっていることがわかります。これは、1つ前の行の「ReDim Preserve FileNameArr(Idx)」の命令(正確には「ReDim Preserve FileNameArr(0)」の命令)により、動的配列の添え字「0」の箱が1つ、メモリ上に確保されたことを示しています。

ここで現在ハイライトされている「FileNameArr(Idx) = TempName」の式の左辺と右辺の状態を確認してみましょう。マウスポインタを左辺と右辺に近づけてみてください。すると配列FileNameArr,変数Idx,変数TempNameの内容はそれぞれ下図のようになっていることがわかります。

この3つの図から左辺の配列FileNameArr(0)の内容は「""」(空文字),右辺の変数TempNameの内容は先ほどローカルウィンドウで確認した通り「2015_Diary.txt」になっています。つまり、このハイライトされた行が実行されると配列FileNameArr(0)に「2015_Diary.txt」が格納されるということが分かりますね。

ではここでF8キーを1回押してみましょう。すると下図のように「TempName = Dir()」の行がハイライトされました。

また、この時点でのローカルウィンドウを確認すると下図のようになっています。先ほどと異なり、配列FileNameArr(0)の内容が「""」(空文字)から「2015_Diary.txt」(赤枠部)に変化しました。この結果から変数TempNameの内容が動的配列FileNameArrの最初の要素に格納されたことが確認できましたね。

それではここでF8キーをもう1回押してみましょう。すると下図のように「Idx = Idx + 1」の行がハイライトされました。

この時点で1つ前の行の「TempName = Dir()」の式が実行されましたので、左辺の変数TempNameの内容を確認してみましょう。変数にマウスポインタを近づけてみると下図のように、変数TempNameの内容が当初の「2015_Diary.txt」から「2015_Memo.txt」に変化していることがわかります。これはDir関数により、フォルダから該当するファイル名が新たに1つ検索され、変数TempNameに格納されたことを示しています。

ではF8キーをもう1回押してみましょう。すると下図のように「Loop」の行がハイライトされました。

また、この時点での変数Idxの状態を確認してみましょう。変数Idxにマウスポインタを近づけると下図のように「1」になっていることがわかります。

ここまでで、フォルダ内で先頭が「2015」から始まるファイル名が1つ、配列に格納されました。同じ流れで、該当する残りのファイル名についても配列に格納されていきます。

ここからは、下図のように「Loop」の行が再度ハイライトされるまでF8キーを何度か押していってみましょう。

「Loop」の行がハイライトされたらローカルウィンドウで配列や変数の状態を確認してみましょう。

まず、配列FileNameArrの先頭の「+」キーを押してみると、下図の赤枠部(1番)のように2つめのファイル名「2015_Memo.txt」が配列に格納されていることがわかります。(正確にはFileNameArr(1)に格納されています。)

赤枠部(2番)の部分に「String(0 To 1)」という表示がありますが、このように動的配列のメモリ上で確保されている添え字の最小値と最大値が表示されます。

赤枠部(3番)の変数TempNameの内容は「""」(空文字)になっていますので、これでファイルの調査は終了してDo While文の処理を抜けることになりますね。

赤枠部(4番)の変数Idxの内容はこの時点で「2」になっています。これは、Do While文を終了した後の次の命令であるIf文の条件「Idx >= 1」を満たすことを示しています。

では、ここでプログラムを先に進める前にIf文の中にあるFor文に注目してみましょう。まず、For文の初期値と終了値である「LBound(FileNameArr)」と「UBound(FileNameArr)」の値を確認してみます。マウスポインタを近づけてみると下図のようになっています。この情報からも、配列FileNameArrの添え字の最小値が「0」,最大値が「1」であることがわかります。つまりFor文のループ変数iが「0」~「1」に変化することがわかりますね。

もう1点、For文の中に記述された命令をご覧ください。「Debug.Print FileNameArr(i)」と記述されていますが、下図の赤矢印で示したように配列の添え字が変数「i」になっていますね。このように記述することでループ変数iの内容に応じた動的配列FileNameArrの要素の内容を1つずつイミディエイトウィドウに表示することができます。

それではプログラムの続きを実行したいと思いますが、その前にもう1ヶ所、まとを絞って確認したい箇所がありますのでブレークポイントを設定してみます。下図のように「Erase FileNameArr」の行の先頭の淵をクリックしてブレークポイントを設定します。

では、ここでキーボードのF5キーを1回押してみてください。すると下図のようにブレークポイントを設定した行がハイライトされると同時に、イミディエイトウィンドウに先頭が「2015」から始まる2つのファイル名が表示されました。

それでは、ここでF8キーを1回押してみましょう。すると下図のように「Idx=0」の行がハイライトされました。

この時点で1つ前の行の命令である「Erase FileNameArr」が実行されたことになります。この命令は動的配列をメモリから消去するのでしたね。この点を確認するためにローカルウィンドウを確認してみましょう。すると下図の赤枠部のように配列FileNameArrの先頭の「+」マークが消え、先ほどまで表示されていた「String(0 To 1)」の表示も「String()」になっており、確かに動的配列が消去されたことがわかります。

では、最後にキーボードのF5キーを押してプログラムを最後まで実行しましょう。既にイミディエイトウィンドウに結果が表示されているので、画面上は何も起きずにそのままプログラムが終了します。

ここまでで、動的配列を用いたプログラムの動きを見てきました。

このプログラムのように、ある調査を行い、該当するものを1つずつ格納して、プログラムの最後で結果を1つずつ表示するという場合に配列はとても有効です。また、Do While文と動的配列はとても相性が良いので合わせて覚えておいてください。

プログラムの解説は以上となりますが、今回、ブレークポイントを2ヶ所設定しました。これを解除しておきましょう。ブレークポイントを解除するには設定した時と同じように設定した行の先頭をクリックするか、該当行でカーソルが点滅している状態でF9キーを押します。また「Ctrl」と「Shift」キーを押しながら「F9」キーを押すことで複数のブレークポイントを一括して解除することができます。

Excel-VBAのキーボードによる操作ですが、実は、VBEの画面上部にある各メニューから調べることができます。例えば今回お伝えしたブレークポイントの設定や解除のキー操作は、下図のように「デバッグ」メニューの中で確認することができます。

4. 配列を利用する際の要注意点

ここでは、配列を利用する際の注意点についてお伝えしたいと思います。先ほどのプログラムの解説でもお伝えしていますので少し復習になりますね。

注意点その1:
配列に関連した関数であるLBound,UBound,Eraseなどの関数は動的配列で利用する場合、メモリ上に最低でも1つ、配列の要素が確保されていない状態で利用するとエラーとなりプログラムが停止してしまいます。(静的配列はメモリ上に配列要素が先に確保されるので問題ありません。)そのため、動的配列では、配列に関連する関数の利用時点でメモリ上に配列の要素が確実に存在する場合を除いて、If文を用いて(※)、配列の要素の存在有無を確認するようにしましょう。

※方法はいくつかありますが、本ブログではIf文で配列の添え字を管理する変数の値をチェックする方法をお伝えしました。

注意点その2:
静的配列、動的配列もとに、一旦利用した後で内容をクリアする必要がないか、考慮しながらプログラミングしましょう。配列はプログラムの中で使いまわされることも多いため、以前の内容が残っていると不具合の原因になることがあります。変数と同様になりますが、求める結果の種類が少ないのであれば、その種類ごとに別々の配列を宣言して利用する方法もあります。

注意点その3:
配列はとても便利ですが、実際の開発現場では(これはあくまで管理者Hideの今までの経験談上ですが)、利用する要素の数が何千、何万となるようなケースでは利用されていません。というのは、配列は通常の変数と同じようにメモリを消費するため、メモリ不足によるエラーを回避するためです。そのため、配列の要素数がある程度見通せるケースで利用されています。

以上のような点に注意してください。

5. まとめ

今回、配列を使ったプログラムをいくつか見て頂きましたが、説明を簡単にするためにプログラムの中で配列に直接値を代入したり、配列を制御するためのFor文の初期値や終了値に直接数値を指定しました。この点について、実践的なプログラムにおいては、Excelのシートの情報から配列に値を代入したり、配列を制御するFor文の初期値や終了値については、定数やLBound,UBound関数を使って指定して汎用性を高めたものにします。直接数値を指定する方法だと、プログラムが変更になったときに色々な箇所を変更しなければならなくなるためです。また、配列はFor文やDo While文と、とても相性が良いので、ぜひ組み合わせて使ってみましょう。

今回のブログは以上となります。

お疲れさまでした!

Excel VBA 入門 目次ページ はこちら

【電子書籍出版のご案内】
本ブログで扱っている「Excel VBA 入門」の内容が、内容を大幅に増やした形で電子書籍(Kindle)になりました。ご興味のある方は、ぜひご覧頂ければ幸いに存じます。
Excel VBA に挫折しないためのプログラミング超入門 (基礎から応用まで)

スポンサーリンク
Simplicityのレクタングル広告(大)
Simplicityのレクタングル広告(大)

シェアする

  • このエントリーをはてなブックマークに追加

フォローする