1 / 12
第7章 配列変数とその宣言
変数の基本的な使い方は第4 章で登場しました。変数は処理の途中の値を一時的に保存して，プログ ラム内で，他の処理を行いたいときに使ってきました。１つの変数には一時的には１つのデータだけ保 存できます。 この章では，同じ目的で利用する複数の変数をまとめて扱うことのできるデータ構造の基本的な使い 方について学びます。VBA ではそのようなデータ構造を「配列（Array）」と呼びます。まず，配列と は何かを理解するために，変数を使って多数の文字列の中からある特定の文字列が何番目にあるかを探 すマクロを作成し，その後に同じ問題について配列を使ってマクロを書き直します。 基本課題１
ワークシートに参考図（表１ 学生点数表）のように多数の学生の名前が順に並んでいる表があり， その表から特定の学生名（例えば，田中）が何番目にあるかを答えるマクロを，Sub プロシージャとし て名前「Sub 学生名検索 1()」で作成します。 ＜表１ 学生点数表＞ 探したい名前をB1 セルに入力し，答えの番号を含め「#番目です」と C1 セルに出力するマクロは以 下のようになります。データ数は任意とし，仮に最大値をN=100 としています。qname を探したい文 字列，sname を学生名とし，Do While～Loop 構文で「セルからデータを１つずつ入力し，条件 qname<>sname を判定するという反復処理を記述しています。qname に等しい sname が見つかった とき，反復を終了します。そのときの変数「i」の値が答えになります。一方，この構文では，qname に等しい sname が見つからない（qname<>sname）場合は反復が終了 しない，つまりこのプログラムが暴走する危険性があるので，カウンタ「i」に条件 i>N を与えて，12 行目のIf～Then 構文と Exit Do 構文で強制終了しています。
反復のループから抜けるステートメント
Do While～Loop 構文 → Exit Do 他のDo ～Loop 構文 → Exit Do For～Next 構文 → Exit For 1 2 3 4 Sub 学生名検索 1() Worksheets("Sheet1").Activate Dim sname, qname As String Dim i, N, ans As Integer
2 / 12 5 6 7 8 9 10 11 12 13 14 15 16 Range("C1").ClearContents qname = Range("B1") N = 100 ' 最大のデータ数（ループの強制終了） i = 0
Do While sname <> qname i = i + 1
sname = Range("B" & (i + 2)) If i > N Then Exit Do
Loop ans = i
Range("C1") = ans & "番目" End Sub
注：14 から 15 行目は「Range("C1") = i & "番目"」と書けば１つの文にまとまり，変数 ans の使用と宣 言が不要になる。 上のプログラムのように，「セルからデータを１つずつ入力し，条件判定する」という反復処理の書 き方では，セルからデータを１つずつ入力する処理が遅いため効率的ではありません。大量のデータを 処理する場合には，まず外部記憶からデータの入力処理を行い，それらをコンピュータのメモリに格納 しておき，次に目的の処理を行い，すべてが終了したらデータを外部記憶に戻す，というように処理を 分けて行います。 では次に，N 人の学生名が N 個の変数 sna1～snaN に格納されているとき，特定の名前の番号を探 すマクロプログラムを考えて見ます。大きなN ではコードが長くなるので N=8 としておきます。僅か 8 人でも長いコードになりますが，次に説明する配列を利用すると解決します。条件分岐に Select Case 構文を使って，マクロは次のSub プロシージャ「学生名検索 2()」のようになります。この問題では他 の条件分岐の構文を使うとコードはより長く，かつ見通しが悪くなります。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub 学生名検索 2() Worksheets("Sheet1").Activate
Dim sna1, sna2, sna3, sna4, sna5, sna6, sna7, _ sna8, qname As String
Dim i, ans As Integer
sna1 = "小林": sna2 = "斉藤": sna3 = "佐々木": sna4 = "佐藤": sna5 = "鈴木": sna6 = "高橋": sna7 = "田中": sna8 = "長谷川" Range("C1").ClearContents
qname = Range("B1") ‘ 検索したい名前 Select Case qname
Case sna1 ans = 1 Case sna2: ans = 2 Case sna3: ans = 3 Case sna4: ans = 4 Case sna5: ans = 5 Case sna6: ans = 6
3 / 12 18 19 20 21 22 23
Case sna7: ans = 7 Case sna8: ans = 8 Case Else: ans = 0 End Select
Range("C1") = ans & "番目" End Sub 注：4 行目は 3 行目の継続行，6 行目と 7 行目は４つの文から成り，13 行～20 行はそれぞれ２つの文 から成る。20 行目は見つからない場合の答えを「0 番目」と表示するために用意される。
配列とは
配列とは，同じ種類の複数のデータをまとめて格納できるよう一連の記憶領域を確保する特別なデー タ構造のことであり，個々のデータの値の他に，データの個数（要素数），配列名およびデータ型を指 定して利用します。個々のデータ（要素）を配列要素．．．．といいます。データ型は普通の変数と同じ種類の ものを利用できます。 配列の宣言の一般的な書き方は以下のとおりです。 （a）Dim 配列名(要素数 - 1) As データ型 （b）Dim 配列名(下限値 To 上限値) As データ型 配列名の規則は変数のそれと同じであり，配列は普通の変数と殆ど同じですが，少しだけ違います。 通常は（a）の書式で宣言し，配列要素の個数を配列名のカッコの中に指定します。また，普通の変数 は変数宣言を省略することが可能ですが，Option Explicit 文の有無によらず，配列は必ず宣言してから でないと使用できません。 配列要素は配列名（例えば A とする）とインデックス番号（要素番号．．．．）で，配列名(インデックス番 号)，つまり「A(1)」のように指定します。VBA でのインデックス番号は基本的には 0 番目から始まる ので，A(1)は配列 A の 2 番目の要素を意味します。したがって，「Dim A(10) As Integer」と宣言した場 合，A(0)から A(10)まで 11 個の要素を整数型で利用すると宣言することになります。（b）の書式では要素番号の下限値と上限値を指定します。要素数は「上限値－下限値＋１」になり ます。例えば，「Dim A(0 To 10) As Integer」と書くと，書式（a）の「Dim A(10) As Integer」と同じ意味 になります。
基本課題２
ここからは実際に配列を使ってみましょう。まず，以下のプログラムを見てください。配列 season を「Dim season(4) As String」で宣言し，文字列型の配列要素「season(0)，season(1)，season(2)， season(3)，season(4)」の領域を確保しています。6～9 行目で 0 番目から 3 番目までの配列要素に「春， 夏，秋，冬」の文字を代入します。ここまでが配列宣言とデータ入力であり，準備段階です。 1 2 3 Sub 配列の例 1() Sheets("Sheet2").Activate Cells.ClearContents
4 / 12 4 5 6 7 8 9 10 11 12 13 14
Dim season(4) As String Dim i As Integer season(0) = "春" season(1) = "夏" season(2) = "秋" season(3) = "冬" For i = 0 To 3 Cells(2, i + 1) = season(i) Next i End Sub このプログラムを実行すると，11～13 行目の For～Next 構文により，A2~D2 セルに春，夏，秋，冬 と入力されます。 配列要素は普通の変数と全く同じように使えます。上の例のサブプロシージャの season(0) = "春" Cells(2, i + 1) = season(i)
は配列season の 0 番目の要素に，春，という文字列を代入したり，season の i 番目の要素の値を Cells で指定したセルオブジェクトに代入しています。普通の変数に代入したり，参照したりするときの使い 方と全く同じように，配列は式の右辺と左辺で使えます。 配列の要素を0 番目ではなく，1 番目から始めるようにするには（b）の書式で宣言します。例えば， 配列要素がssn(1)，ssn(2)，ssn(3)，ssn(4)，ssn(5)からなる Single 型の配列 ssn を宣言するには Dim ssn(1 To 5) As Single とします。配列名ssn の後ろのカッコの中に To というキーワードを使って，要素番号の最初と最後を 指定します。 Variant 型と Array 関数 一般的な関数の利用については第8 章で扱いますが，Array 関数を使うと，配列として宣言していな いVariant 型の変数に，配列データをまとめて格納することができます。このような Variant 変数は， 配列とまったく同じように扱われ，要素番号を指定して個々の配列要素を参照することが可能です。 ただし，Array 関数を使用して作成した配列の要素番号は必ず 0 から始まります。 また，次の「配列の例 2()」の場合で言うと「season(1) = ”夏”」のように，Variant 型の変数に代入 文でデータを与えると，エラーになります。 次の「配列の例 2()」は，上の「配列の例 1()」と同じ内容を記述しています。まず，4 行目で変数 season を Variant 型の変数として宣言し，5 行目で Array 関数を使用して，seasn(0)～season(3)に Array 関数の引数の順に値「"春", "夏", "秋", "冬"」を配列要素として格納しています。このようにArray 関数 を利用すると，数十個程度のデータであれば数行でまとめて格納できるのでコードがかなりコンパクト になります。
5 / 12 1 2 3 4 5 6 7 8 9 10 Sub 配列の例 2() Sheets("Sheet2").Activate Cells.ClearContents Dim season As Variant Dim i As Integer season = Array("春", "夏", "秋", "冬") For i = 0 To 3 Cells(2, i + 1) = season(i) Next i End Sub 無効なインデックス番号 配列を使うときに注意しなければいけないことがあります。それは，存在しない配列要素をうっかり 使ってしまうことです。配列の場合は，配列要素のデータの型と要素番号の両方についてエラーチェッ クが行われます。例えば，以下の例を見てください。 1 2 3 4 5 6 7 Sub 配列の例 3 注意() Dim a(9) As Single
a(1) = 0.12 a(10) = 1.2
Range("A1") = a(1) + a(10) End Sub このサブプロシージャを実行 するとエラーが出て，図のメ ッセージが表示されます。理 由は配列 a の宣言文では要素 数10 にしている（つまり a の 配列要素は a(0)~a(9）だけ） のに，a(10)に 1.2 を代入する という文があります。そのた め，5 行目で「インデックスが 有効範囲にありません」というエラーがでます。 「デバッグ」ボタンを押すとエラーの箇所を確認できます。VB エディタの画面で実行時エラーが発 生した行に背景色（標準設定の時は黄色）が付きます。
基本課題３
配列の説明と基本課題２の理解を基にして，基本課題１の「Sub 学生名検索 2()」を配列と Array 関 数を使って書き直してみましょう。データ数は10 個に増やしてあります。「Sub 学生名検索 1()」と較 べると，反復の中でデータを１つずつ変数に格納する代わりに，ここではまとめて配列に格納してある ので，条件判定の考え方はまったく同じでよいことが分かります。ただし，sname(i)の要素番号 i は 06 / 12
から始まるので，見つかったときのデータの番号（A 列の No）は i+1 が答えになります。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub 学生名検索 3() ‘ ＜Array 関数の利用＞ Worksheets("Sheet1").Activate
Dim sname As Variant
sname = Array("小林", "斉藤", "佐々木", "佐藤", "鈴木", "高橋", _ "田中", "長谷川", "山本", "吉田")
Dim qname As String Dim i, N, ans As Integer Range("C1").ClearContents
qname = Range("B1") ' 検索したい名前
N = 100 ' 最大のデータ数（ループの強制終了） i = 0
Do While sname(i) <> qname i = i + 1
If i > N Then Exit Do Loop
ans = i + 1
Range("C1") = ans & "番目" End Sub 大量のデータを処理する場合には，格納する順番を間違えたり継続行が増えるなど，Array 関数は使 いにくくなります。そのような場合は，準備段階として外部記憶からデータの入力処理を行い，それら をコンピュータのメモリに格納しておき，次に目的の処理を行います。 Excel の VBA マクロでは，外部記憶として，ワークシートに記入された表形式のデータを処理の対 象にします。まず，「表１ 学生点数表」のデータを配列に格納する部分のコードについて説明します。 表1 では表の一部だけが表示されています。基本課題１や３のようにレコード数を適当な大きな数に設 定する方法ではなく，実際のレコード数を調べるにはどうしたらよいでしょうか。 表の最終セルの行番号を調べる Excel のマウス操作で表の最終セルを調べる機能があります。表の任意のセル（例えば B2 とする） を選択し，[Ctrl]キー＋「↓」キーを押すと，表内で下方向に B 列の最終セルにジャンプし，最終セル が選択される。ここで言う最終セルとは，これ以降は空白セルが続くことを意味する。同様に，[Ctrl] キー＋「↑」キーを押すと表の上，[Ctrl]キー＋「→」キーを押すと表の右，[Ctrl]キー＋「←」キーを 押すと表の左，にある最終セルにジャンプする。これらの操作に対応したコードは以下の通りです。 Range("B2").Select Selection.End(xlDown).Select 表の下端へジャンプ Selection.End(xlUp).Select 表の上端へジャンプ Selection.End(xlToRight).Select 表の右端へジャンプ Selection.End(xlToLeft).Select 表の左端へジャンプ
このEnd は，End モードとも呼ばれるもので，Range オブジェクトのプロパティで，Range オブジ ェクトを返します。上のオブジェクト式は第3 章の「対象.命令」の文型です。
7 / 12 表の範囲を調べて，上下左右の端のセルのアドレスを知りたい（取得する）場合は，以下のように， ジャンプして選択したセルのEnd モードに「Row」または「Column」を付けます。 Selection.End(xlUp).Row 表の上端の行番号を取得 Selection.End(xlDown).Row 表の下端の行番号を取得 Selection.End(xlToLeft).Column 表の左端の列番号を取得 Selection.End(xlToRight).Column 表の右端の列番号を取得 この，「Row」と「Column」は，Range オブジェクトのプロパティで，その行位置と列位置を番号で 返します。上のオブジェクト式は「対象.プロパティ」の文型です。 例題として，「表１ 学生点数表」の最終セルの行番号を調べ，そのセルの A 列と B 列のデータ値を メッセージボックスに表示する Sub プロシージャを示します。MsgBox の文字列式にある「vbCrLf」 は，改行を表す VBA の定数で，この定数と文字列連結演算子を組み合わせることによって２つのデー タを2 行に表示します。この結果，最終セルの行番号は 22 と判明します。 1 2 3 4 5 6 7 Sub 表の大きさ() Worksheets("Sheet1").Activate Dim nr As Integer ' 最終セルの行番号 Range("B2").Select nr = Selection.End(xlDown).Row
MsgBox "No=" & nr & vbCrLf & Cells(nr, 1) & "," & Cells(nr, 2) End Sub 注：「Cr」＋「Lf」は Shift_JIS 文字コードで書かれた Windows のテキストデータの行末と改行を表す 特殊な記号。ASCII 文字コードで表すと（16 進表記で）「0D」＋「0A」となる。 以上ですべての準備ができたので，ここで，「学生名検索3()」を書き直し，ワークシートの表から大 量のデータを配列に入力する処理をその後の処理と分けて，基本課題１と同じ問題を解決するマクロを 作成します。マクロ名を「学生名検索4()」とします。 1 2 3 4 5 6 7 8 9 10 11 Sub 学生名検索 4() ' ＜表からデータを配列に一括入力＞ Worksheets("Sheet1").Activate
Dim qname As String Dim sname(100) As String Dim i, N, ans As Integer Range("C1").ClearContents qname = Range("B1") ' 検索したい名前 Range("B2").Select N = Selection.End(xlDown).Row - 2 ''N=最大のデータ数 For i = 1 To N
8 / 12 12 13 14 15 16 17 18 19 20 Next i For i = 1 To N
If sname(i) = qname Then ans = i
Exit For End If Next i
Range("C1") = ans & "番目" End Sub 今回はデータ数N が 9 行目で与えられるので，10～12 行目で文字列型配列 sname(i)に学生名をすべ て格納できます。また，条件判定は最大N 回行えばよいことが分かっているので，if～Then 構文の中 で「sname(i) = qname」という条件判定が成立したときの配列番号「i」が答えになります。このとき For～Next ループを続ける必要はないので，ループを途中で終了します。For～Next ループから途中 で抜けるには，上の16 行のように Exit For 文を使用します。
発展課題 1
次に配列を使ったプログラムをもう一つ見ましょう。配列変数を使うことにより，ワークシート上に ある大量のデータを組織的にまとめてプログラムに読み込んだり，逆にワークシートのセルに書きだす ことができます． 第7 章の Excel 教材 chap07array_sample.xlsm（CEAS/Sakai から）をダウンロードして開きまし ょう。Module1 には以下に示しているマクロプログラムが入力されています。ワークシート Sheet1 の B4:B15 には 1~12 月の電気使用量を入力してあります。さてこのサブプロシージャの「xxx」部分を修 正してから実行してみましょう。実行に際しては，事前に電気料金を知りたい月の数字をD8 セルに入 れておいてください。実行するとD8 セルに入力されてる月の電気料金がメッセージボックスに表示さ れます（下図）。プログラムの中の重要な部分にはコメントを書いてあります。9 / 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Sub 電気料金照会() Dim kihon As Single
Dim tanka1, tanka2, tanka3 As xxx
Dim m As Single
kihon = 1302 '基本料金：北電，契約 40A の場合 tanka1 = 18.27
tanka2 = 23.68 tanka3 = 25.37
Dim shiyo(1 To 12) As Single Dim i As Integer For i = 1 To 12 shiyo(i) = Cells(xxx, xxx) Next xxx m = Range("D8") Dim ryokin As Integer
If shiyo(m) <= xxx Then
ryokin = kihon + shiyo(m) * tanka1 ElseIf shiyo(m) <= xxx Then
ryokin = kihon + 120 * tanka1 + (shiyo(m) - 120) * tanka2 Else
ryokin = kihon + 120 * tanka1 + (280 - 120) * tanka2 + (shiyo(m) - 280) * tanka3
End If
MsgBox m & "月の料金は" & xxx & "円です。" End Sub
発展課題２
発展課題１と同じ教材を使います。ワークシートSheet2 には 100 人分の学生番号と氏名が入力して ある。E6 セルに「氏名を知りたい人の学生番号」を入力して実行すると，以下のようにメッセージボ ックスにその学生番号の名前が表示されるプログラムを作りなさい。
10 / 12
以下のことを行うようにコードを書いていけば良い。 0. （データの入ってる Sheet1 をアクティベート）
1. 配列のサイズ（配列の要素の数）が 100 の String 型（文字を入れるから，この型じゃないと ダメ）の配列を宣言する。配列名はshimei としましょう
2. For Next を使って配列の各要素 shimei(1)~shime(100)に B2~B101 の氏名を代入する。Cells を使うこと 3. 氏名を知りたい人の学生番号を入れるための Integer 型（学生番号は整数なので，この型）の 変数を宣言する。変数名はid としましょう。 4. 上で宣言した変数 id に E6 セルに入ってる学生番号を代入。 5. MsgBox を使って shime 配列の id 番目の配列要素を表示。
静的配列と動的配列
静的配列．．．．とは，あらかじめ要素数を指定して宣言された配列で，マクロの実行中に要素数を変更する ことはできません。この章のマクロではこれまではすべて静的配列を使っています。 基本課題３の「Sub 学生名検索 4()」では，せっかく事前にデータ数 N が分からなくても表の大きさ からN の値を取得できるようになっているのに，配列の宣言ではDim sname(100) As String
と最大のデータ数の予測サイズ100 以上を書く必要があります。この点が気になりませんか。 動的配．．．列．とは，要素数を指定せずに Dim 文で宣言しておいて，マクロの実行中に要素数を指定して 再び ReDim ステートメントで宣言しながら使える配列です。再宣言は何度でも許されます。なお， ReDim 文を使うと，その時点で動的配列のすべての配列要素の内容はクリアされます。 次の例の4 行目は sname を文字列型の動的配列として宣言しています。9 行目で N の値が確定する ので，10 行目で ReDim 文を使って要素数を指定して再宣言しています。 1 2 Sub 動的配列の例() Worksheets("Sheet1").Activate
11 / 12 3 4 5 8 9 10 11 12 13
Dim qname As String
Dim sname() As String '’ 動的配列の宣言 Dim i, N, ans As Integer
以下，途中省略 Range("B2").Select
N = Selection.End(xlDown).Row – 2 ''N=最大のデータ数 ReDim sname(N) '’ 動的配列の再宣言
For i = 1 To N
sname(i) = Range("B" & (i + 2)) Next i 以下，途中省略は「学生名検索 4()」と同じ End Sub
配列の利用
配列は多数の同じようなデータを一まとめにするときに用います。反復処理を組織的に記述できる以 外にも，データの検索をより効率的に行うとか，データを大小順に並べ替えるとか，文章中の単語の出 現頻度を数えるとか，暗号で利用される大きな数の素数判定，などの問題を確実に解決する（その処理 手順をアルゴリ．．．．ズム．．という）には配列の利用が必須になります。 ここでは第 6 章の基本課題４「整列（ソート）」のマクロプログラムを配列を使って書き直してみま す。整列の対象となるデータを入れる配列はdata とします。データ数 n に応じて扱えるように，配列 data は動的配列として宣言しています。 データの準備：整列（ソート）のワークシート「sorting」の 1 行目から 4 行目のデータをこの章の基 本課題を扱っているファイルの新規ワークシート（Sheet3 とします）にコピーしておく。 第6 章では挿入法を理解してもらうために途中結果を，順次，セルに書き出しましたが，ここでは整 列後の結果だけを5 行目に出力しています。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub ソート挿入法 3() Dim n As Integer: n = 20 Dim data() As Integer ReDim data(n)Dim i, j, sentinel As Integer Range("C5:C23").ClearComments For j = 1 To n ' 整列前のデータを 4 行目から入力 data(j) = Cells(4, j + 2) Next j For i = 2 To n sentinel = data(i) data(0) = sentinel ' 番兵を列外に置いておく j = i - 1
12 / 12 16 17 18 19 20 21 22 23 24 25 26
Do While (sentinel < data(j))
data(j + 1) = data(j) ' 右へずらす j = j - 1 Loop data(j + 1) = sentinel Next i For j = 1 To n ' 整列後のデータを 5 行目に出力 Cells(5, j + 2) = data(j) Next j End Sub 整列法の考え方（第6 章の 11 ページの解説図）を記述しているコードは 12 行目から 21 行目までで す。ここの部分にはセルの参照やセルへの代入など現れていない，つまり挿入法に関するコードとはこ のようになるということが良く分かると思います。
