Excel VBAで配列を使えたら脱初級！表の値をまるごと変数に格納する

ExcelVBAの初級教本では解説されていないことも多い「配列」のテクニック。

表計算ソフトであるExcelでは大変有用です。

概要
1. 配列とは
2. 変数にExcel表を放り込むと配列になる
  1. 表の配列化はまとめて1手
  2. 配列の値を取り出すときは1つずつ
実践
1. 概要の内容
2. 概要の内容：Cells構文を使った書き方
配列の値を全て出力する
応用：表の大きさに合わせて配列範囲を可変する

概要

配列とは

配列は英語でarray（アレイ）と呼びます。

誤解を恐れずザックリと言ってしまえば、配列とはExcelの表のようなものです。

下記はExcelシートで1行×3列の表ですが、1行×3列の配列と見ることもできます。

同様に下記は3行×4列の表であり、配列でもあります。

Excelシート上では表のまま利用できますが、配列に変換すればExcelVBAでの演算・出力などに利用できます。

初級の教本だと、「配列」を扱わないこともあります。しかし、「配列」は表計算ソフトであるExcelにピッタリな技術であり、早く知っていた方が実務で役立ちます。

変数にExcel表を放り込むと配列になる

変数には複数の値が格納できます。

表の配列化はまとめて1手

どれだけ表の範囲が大きくても、配列に放り込むのは1手で終わります。

バリアント型（Variant型：後述）の変数に、表の値を代入するだけで、配列として利用できます。

変数というと、一般的には下記のような式を思い浮かべるかと思います。これはExcelVBAでも使うことができ、変数xに数値3を代入することができます。

x=3

しかし、ExcelVBAの変数に代入できる値は1つだけではありません。下記のようにすれば、ExcelシートのA1からB5までの範囲の値を、そのままの形でxへ代入することができます。これが「配列」です。

x=Range(“A1:B5”)

この1行だけで、シートA1:B5の値（表）がxに格納されます。例えばシートに下のような値が入力されているとき、5行×2列で10個の値がそのままの形で配列としてxに格納されます。

ちなみに、バリアント型（Variant型）とは、変数のデータ型（数字、文字、時間など）をVBA側で自動判定してくれるデータ型です。ExcelVBAのコード上で、変数の型を定義しない場合は、自動的にバリアント型に設定されます。

つまり、初めのうちは「変数の型を定義しなくても、変数に表を放り込めば、勝手に配列になる」と考えておけばOKです。

もし、正しく配列変数を定義したい場合は、下記のように書きます。変数（ここではx）の後ろに半角カッコ()を付けて、バリアント型を定義します。

Dim x() As Variant

配列の値を取り出すときは1つずつ

配列に入れるのは1手で済みましたが、配列の値を取り出すときは1セルずつ処理する必要があります。

例えば、先ほどの配列x（下記）を利用するとします。

x=Range(“A1:B5”)

セルA1:B5の範囲には、次の値が入力されているとします。

このときの配列xは、元々シートにあった表と同じように、次のような構造になっています。

配列xについて、行番号（左の数字）と列番号（右の数字）を指定することで、表の中の値を1つずつ取り出すことができます。

例えば、下記のように書きます。

Range(“D1”) = x(1, 2)

ExcelシートのD1セルに、配列xの「1行目かつ2列目」の値が入力されます。これは、元々ExcelシートのB1セル（1行目の2列目）にあった数字となります。

ちなみに、ExcelVBAでは「アクティブセル」を意識する必要がありません。ここまでに書いたように、セル位置を指定すれば変数入力も変数出力も可能です。

もちろんExcelVBAで「アクティブセル」を用いることもできますが、ほとんどの場合において、処理速度が落ちます。そのため、わざわざ「アクティブセル」をExcelVBAで使うことは少ないと思います。

実践

概要の内容

概要で紹介した内容と同じコードです。本来は、シート名なども指定すべきですが、汎用性を重視して、ざっくり書いています。

Sub ArrayInputAndOutputSingle1()

ThisWorkbook.Activate

'シートの値を消去
ActiveSheet.Cells.Clear

'値を入力する
For Ver = 1 To 5
    For Hor = 1 To 2
        Inp = Ver * 10 + Hor
        Cells(Ver, Hor) = Inp
    Next Hor
Next Ver

'配列xへ格納
x = Range("A1:B5")

'配列xから1つ取り出し
Range("D1") = x(1, 2)

End Sub

下記はセルの初期値を自動入力しています。

'値を入力する
For Ver = 1 To 5
    For Hor = 1 To 2
        Inp = Ver * 10 + Hor
        Cells(Ver, Hor) = Inp
    Next Hor
Next Ver

その後は、概要と同じ内容です。セルA1:B5を配列xへ格納して、配列x（1,2）の値をセルD1へ入力しています。

'配列xへ格納
x = Range("A1:B5")

'配列xから1つ取り出し
Range("D1") = x(1, 2)

概要の内容：Cells構文を使った書き方

少し違った書き方をしてみます。実行結果は概要の内容と同じです。

Sub ArrayInputAndOutputSingle2()

ThisWorkbook.Activate

'シートの値を消去
ActiveSheet.Cells.Clear

'値を入力する
For Ver = 1 To 5
    For Hor = 1 To 2
        Inp = Ver * 10 + Hor
        Cells(Ver, Hor) = Inp
    Next Hor
Next Ver

'配列xへ格納
x = Range(Cells(1, 1), Cells(5, 2))

'配列xから1つ取り出し
Cells(1, 4) = x(1, 2)

End Sub

下記部分の書き方が異なりますが、実行内容は同じです。

'配列xへ格納
x = Range(Cells(1, 1), Cells(5, 2))

'配列xから1つ取り出し
Cells(1, 4) = x(1, 2)

Cells構文を使うことによって、セル位置を（行番号,列番号）で指定できるようになります。

配列も（行番号,列番号）で扱うため、Cells構文を使った方がセル位置を指定しやすいです。

また、Cells構文を用いることでループ文が使いやすくなります。

配列の値を全て出力する

For ~ Next等のループ文を使えば、配列xの値を全て書き出すことも可能です。

Sub ArrayInputAndOutputFull()

ThisWorkbook.Activate

'シートの値を消去
ActiveSheet.Cells.Clear

'値を入力する
For Ver = 1 To 5
    For Hor = 1 To 2
        Inp = Ver * 10 + Hor
        Cells(Ver, Hor) = Inp
    Next Hor
Next Ver

'配列xへ格納
x = Range(Cells(1, 1), Cells(5, 2))

'配列xを全て出力
CurX = 1
CurY = 4

For RowLoc = 1 To 5
    For ColLoc = 1 To 2
        Cells(CurX, CurY) = x(RowLoc, ColLoc)
        CurY = CurY + 1
    Next ColLoc
    CurX = CurX + 1
    CurY = 4
Next RowLoc

End Sub

下記が実行結果です。Cells（1, 4）＝セルD1を左上にして、元と同じ形で配列を書き出します。

配列を出力するにあたり、4つの変数を使っています。

CurX：書き込む先のセル行番号
CurY：書き込む先のセル列番号
RowLoc：参照する配列の行番号
ColLoc：参照する配列の列番号

下記部分で、書き込むセルの初期位置（セルD1）を変数で指定しています。CurXが行番号、CurYが列番号です。Cur=Current（現在）の意味です。

CurX = 1
CurY = 4

下記部分が、実際にループで配列xを出力している部分です。配列xから値を1つずつ取り出して、シートに出力しています。

For RowLoc = 1 To 5
    For ColLoc = 1 To 2
        Cells(CurX, CurY) = x(RowLoc, ColLoc)
        CurY = CurY + 1
    Next ColLoc
    CurX = CurX + 1
    CurY = 4
Next RowLoc

For RowLoc ～ Nextは行方向のループ。For ColLoc ～ Nextは列方向のループです。

配列側の値は、RowLoc（行番号）とColLoc（列番号）の2つの変数で指定しています。RowLocはRow Location（行位置）、ColLocはColumn Location（列位置）の意味で変数を決めてます。

値の出力は列ループで行っています（下記1行目）。ループが進む前に、出力するセルの列位置に1を足してずらします（下記2行目）。

Cells(CurX, CurY) = x(RowLoc, ColLoc)
CurY = CurY + 1

列のループが終わったら、次の行ループに移ります。その前に下記部分で、出力するセル位置を調整しています（行番号は1つ下へ、列番号は元の位置に戻す）。

CurX = CurX + 1
CurY = 4

応用：表の大きさに合わせて配列範囲を可変する

今回は基礎ということで、変数xへ配列を代入するとき、5行×2列の範囲で固定しました。

しかし実務においては、配列化したいセル範囲はデータに応じて可変させたいことがほとんどだと思います。

例えば下記のように、基準となるセル（下記ではセルA1）を決めて、行の最大値・列の最大値を取得することで、セル範囲を可変させて取得することができます。

MaxRow = Range("A1").End(xlDown).Row
MaxColumn = Range("A1").End(xlToRight).Column
NewDataList = Range(Cells(1, 1), Cells(MaxRow, MaxColumn))

実際にコード全体を書くと下記のようになります。

Sub ArrayInputAndOutputFullAdjust()

ThisWorkbook.Activate

'シートの値を消去
ActiveSheet.Cells.Clear

'値を入力する
For Ver = 1 To 5
    For Hor = 1 To 2
        Inp = Ver * 10 + Hor
        Cells(Ver, Hor) = Inp
    Next Hor
Next Ver

'データ終わりの行・列を調べて、配列NewDataListに入れる
MaxRow = Range("A1").End(xlDown).Row
MaxColumn = Range("A1").End(xlToRight).Column
NewDataList = Range(Cells(1, 1), Cells(MaxRow, MaxColumn))


'配列NewDataListを全て出力する
'開始はセルD1
CurX = 1
CurY = 4

For RowLoc = 1 To MaxRow
    For ColLoc = 1 To MaxColumn
        Cells(CurX, CurY) = NewDataList(RowLoc, ColLoc)
        CurY = CurY + 1
    Next ColLoc
    CurX = CurX + 1
    CurY = 4
Next RowLoc

End Sub