Excel VBAの配列とは？基本概念からプログラム方法まで徹底解説

VBA（Visual Basic for Applications）は、長年にわたりMicrosoft Officeスイートに搭載されてきたプログラミング言語です。本格的なVBアプリケーションほどの機能はありませんが、Office製品同士を連携させたり、日常業務を自動化したりする柔軟性をユーザーに提供してくれます。

VBAで最も強力なツールのひとつが「配列」です。配列を使えば、セル範囲のデータ全体をたった1つの変数に読み込むことができ、そのデータに対してさまざまな操作や計算を自由に行えるようになります。

では、VBA配列とはいったい何なのでしょうか？　この記事ではその疑問にお答えしながら、自分のVBAスクリプトで配列を活用する方法を詳しく解説します。

VBA配列とは何か？

ExcelでVBA配列を使うこと自体はとても簡単ですが、「配列」という概念自体は、初めて触れる人には少し複雑に感じられるかもしれません。

配列は「仕切りのある箱」だとイメージすると分かりやすいでしょう。1次元配列は仕切りが一列に並んだ箱、2次元配列は縦横に仕切りが並んだ箱です。この「箱」の各マスには、好きな順序でデータを格納することができます。

VBAスクリプトの冒頭で、この「箱」を定義しておく必要があります。1組のデータを保持できる配列（1次元配列）を作成するには、次のように記述します。

Dim arrMyArray(1 To 6) As String

プログラムの後半では、括弧内の番号を指定するだけで、配列の任意のマスにデータを格納できます。

arrMyArray(1) = "Ryan Dube"

2次元配列を作成する場合は、次のようになります。

Dim arrMyArray(1 To 6, 1 To 2) As Integer

最初の数字が行、2番目の数字が列を表します。つまり上記の配列は、6行×2列の範囲のデータを保持できるということです。要素へのデータ読み込みは次のように行います。

arrMyArray(1, 2) = 3

これで、B1セルに相当する位置に値「3」が格納されます。

配列には、文字列・ブール型・整数・浮動小数点数など、通常の変数と同じようにあらゆる型のデータを格納できます。

さらに、括弧内の番号には変数を使うことも可能です。実際、Forループで配列の全要素を順番に処理しながら、シートのセルデータを配列へ読み込むのが定番のテクニックです。具体的なやり方は後ほど紹介します。

ExcelでVBA配列をプログラムする方法

ここでは、スプレッドシートの情報を多次元配列に読み込む簡単なプログラム例を見てみましょう。

例として、商品販売のシートから「担当者名」「商品名」「売上合計」の3つのデータを抽出するケースを考えてみます。

注意したいのは、VBAで行や列を参照するときは、左上を起点として1から数え始めるという点です。したがって、担当者名列は3列目、商品名列は4列目、合計列は7列目となります。

11行分のデータからこの3つの列を読み込むには、次のようなスクリプトを記述します。

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer
Dim targetCol As Variant

targetCol = Array(3, 4, 7)

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i - 1, j) = Cells(i, targetCol(j - 1)).Value
   Next j
Next i

ヘッダー行をスキップするため、最初のForループの行番号は1ではなく2から始めています。そのため、Cellsでセルの値を配列に読み込む際は、配列の行番号から1を引く必要があります。また、読み込み対象の列が3列目・4列目・7列目と飛び飛びになっているため、列番号を配列targetColにまとめておくと、コードがすっきりして管理しやすくなります。

VBAスクリプトを挿入する場所

ExcelでVBAコードを記述するには、VBAエディタを使用します。「開発」タブを開き、リボンの「コントロール」グループにある「コードの表示」を選択しましょう。

メニューに「開発」タブが表示されていない場合は、まず追加が必要です。「ファイル」→「オプション」を選択して、Excelのオプション画面を開きます。

「コマンドの選択」ドロップダウンを「すべてのコマンド」に変更し、左側の一覧から「開発」を選択して「追加」ボタンをクリックし、右側のペインに移動させます。チェックボックスをオンにして「OK」をクリックすれば設定完了です。

コードエディターが開いたら、左ペインでデータが存在するシートが選択されていることを確認してください。続いて、左側のドロップダウンで「Worksheet」を、右側で「Activate」を選択すると、Worksheet_Activate()という新しいサブルーチンが自動的に作成されます。

このプロシージャは、そのワークシートがアクティブになったタイミングで実行されます。作成されたサブルーチン内のスクリプト領域に、先ほどのコードを貼り付けましょう。

このスクリプトは12行分のデータを順に処理し、3列目から担当者名、4列目から商品名、7列目から売上合計を読み込みます。

2つのForループが完了した時点で、2次元配列arrMyArrayには、元のシートから指定したすべてのデータが格納されている状態になります。

Excel VBAでの配列操作

ここで、すべての売上金額に5%の税額を加算し、その結果を新しいシートに書き出したいとしましょう。

これは、最初のループの後に別のForループを追加し、結果を新しいシートに書き出す命令を記述するだけで実現できます。

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

これで配列全体がSheet2に「書き出され」、4列目には売上合計に5%を掛けた税額が追加されます。

実行結果のシートは以下のようになります。

このように、ExcelのVBA配列は非常に便利で、ほかのExcelテクニックと同じくらい汎用性の高い機能です。

上記の例はごく単純な使用例にすぎません。より大きな配列を作成すれば、並べ替えや平均計算をはじめ、さまざまな処理を格納したデータに対して実行できます。

さらに工夫すれば、異なる2つのシートからそれぞれセル範囲を配列に読み込み、両方の配列の要素間で計算を行うことさえ可能です。

活用の幅は、あなたの想像力次第で無限に広がります。