【ExcelVBA】配列とは何か？実例つきで基礎から使い方まで解説！

あなたは、VBAで配列を使ったことがありますか？

配列を使うと処理が早いらしい
配列を使うと可読性が高まるらしい
でも、そもそも配列が何かわからない！

と思ったこともあるのではないでしょうか。

そこで今回は、インデックス（添え字）ごとにデータを指定できる配列について、基礎はもちろん、使うメリットや実例を紹介します。

Excel　VBA実戦のための技術入門レベルでは決して足りない実務に必須のスキルとは [ 沢内晴彦 ]

posted with カエレバ

Amazon

楽天市場

Yahooショッピング

VBAでの配列の使い方
1. 配列とは何か？
2. 二次元配列について
配列の最小・最大インデックスを取得できるLBound・UBound関数
配列を使う効果
1. 処理速度が速い
2. 可読性が高まる
配列の使いどころを実例で紹介
1. 任意の要素に処理を実行し配列で取得
2. 条件を満たすレコードを配列で取得
VBAで使える配列まとめ

VBAでの配列の使い方

配列とは何か？

配列とは、一連のデータをまとめて扱える機能です。また、各データ（要素）はインデックス（添え字）を持っていることも特徴です。インデックスを指定することで要素を取り出すこともできます。

各データ（要素）はインデックス（添え字）を持っている
インデックス（添え字）を指定することで各データ（要素）を取り出せる

また、配列には、インデックスの数が決まっている静的配列とインデックスの数が決まっていない動的配列があります。

静的配列：インデックスの数が決まっている
動的配列：インデックスの数が決まっていない

それぞれ解説していきます。

静的配列

宣言書式

Dim 配列([下限 to 上限])

[下限 To 上限]を省略した場合、「下限」は「0」から始まる

コード例

Dim tmpAry(1 To 3)
tmpAry(1) = "A"
tmpAry(2) = "B"
tmpAry(3) = "C"

インデックスの「下限」が「1」、「上限」が「3」の静的配列を宣言し、インデックス「1」に「A」、「2」に「B」、「3」に「C」を格納しています。

[下限 To 上限]を省略し、「Dim tmpAry」とした場合、確保される変数は下記の通り、空のVariant型変数となる

tmpAry ⇒ Empty（Variant型）

動的配列

宣言書式

Dim 配列()

「下限」「上限」を省略して宣言すると動的配列となる

コード例

Dim tmpAry()
ReDim tmpAry(1 To 2)
tmpAry(1) = "A"
tmpAry(2) = "B"

ReDim Preserve tmpAry(1 To 3)
tmpAry(3) = "C"

「ReDim」は動的配列を再宣言できるステートメントで、ここでは動的配列「tmpAry」を「下限」を「1」、「上限」を「2」として再宣言しています。

そして、インデックス「1」と「2」それぞれ「”A”」「”B”」を代入しています。

tmpAry(1) → A
tmpAry(2) → B

次に「ReDim Preserve」を使って、元の値を保持しつつ、「上限」を「3」に拡張し、拡張されたインデックス「3」に「C」を代入しています。この時「Preserve」を省略してしまうと、対象の配列が持っている値が全て初期化されてしまうので、注意しましょう。

tmpAry(1) → A
tmpAry(2) → B
tmpAry(3) → C

ReDim Preserve：対象配列の元の値を保持する
ReDim：対象配列の元の値を保持しない

二次元配列について

配列には二次元配列もあります。次のように、二つのインデックスを指定します。

宣言書式

Dim 配列([下限 to 上限], [下限 To 上限])

コード例

Dim tmpAry(1 To 2, 1 To 3)
tmpAry(1, 1) = "A"
tmpAry(1, 2) = "B"
tmpAry(1, 3) = "C"
tmpAry(2, 1) = "D"
tmpAry(2, 2) = "E"
tmpAry(2, 3) = "F"

行方向のインデックスの「下限」が「1」、「上限」が「2」、列方向のインデックスの「下限」が「1」、「上限」が「3」の静的配列を宣言しています。そして各インデックスに値を格納しています。

また、二次元配列も下記のように動的配列として使うこともできます。ただし、拡張できるのは、最終要素だけです。

コード例

Dim tmpAry
ReDim tmpAry(1 To 2, 1 To 3)
tmpAry(1, 1) = "A"
tmpAry(1, 2) = "B"
tmpAry(1, 3) = "C"
tmpAry(2, 1) = "D"
tmpAry(2, 2) = "E"
tmpAry(2, 3) = "F"

ReDim Preserve tmpAry(1 To 2, 1 To 4)
tmpAry(1, 4) = "G"
tmpAry(2, 4) = "H"

二次元配列で拡張できるのは、最終要素だけ

セル範囲を二次元配列に代入する

行と列方向にインデックスを持つ二次元配列はセル範囲のデータを保持する際にとても便利です。

コード例

Dim tmpAry
tmpAry = ActiveSheet.Range("A1:C2").Value

アクティブシート上「A1:C2」の範囲の値を配列「tmpAry」に格納しています。格納したセル範囲合わせて、行・列方向のインデックスが設定され値を保持します。この時、インデックスの下限は行・列方向とも、自動的に「1」となります。

まとめてセル範囲をコピーしたい場合や、一時的にセル範囲の内容を保持しておきたい場合などに利用すると大変便利なので、ぜひ覚えておきましょう。

配列の最小・最大インデックスを取得できるLBound・UBound関数

最小インデックスを取得するLBound関数の使い方

配列は、インデックスを省略する時、セル範囲を配列に格納する時などで、インデックスの下限の値が一定ではないこともあります。

そうした際に、便利なのが「LBound」関数です。

基本構文

LBound(対象配列, [次元数])

「次元数」を省略すると一つ目の次元が指定される

コード例

Dim tmpAry(1 To 2, 1 To 3)
LBound(tmpAry, 1)

対象配列「tmpAry」の「1」次元目を指定しているので、インデックス「1 To 2」の最小値「1」を返します。

最大インデックスを取得するUBound関数の使い方

セル範囲を配列に格納し、別のセル範囲にコピーする時など、取得した配列について、インデックスの上限を判定したいことがよくあります。

そうした際に、便利なのが「UBound」関数です。

基本構文

UBound(対象配列, [次元数])

「次元数」を省略すると一つ目の次元が指定される

コード例

Dim tmpAry(1 To 2, 1 To 3)
UBound(tmpAry, 2)

対象配列「tmpAry」の「2」次元目を指定しているので、インデックス「1 To 3」の最大値「3」を返します。

LBound・UBound関数を組み合わせて要素数を取得する

配列の要素数は、インデックスが必ず「1」から始まるなら、「UBound」関数で取得することができます。しかし、配列のインデックスは必ず「1」から始まるわけではありません。

とはいえ、配列の要素数を取得したい場面は数多くあります。そこでインデックスの「下限」に関わらず要素数を取得する方法を次の例で紹介します。

コード例

Dim tmpAry(0 To 2)
Dim cnt As Long
cnt = UBound(tmpAry) - LBound(tmpAry) + 1

「UBound(tmpAry)」は「2」、「LBound(tmpAry)」は「0」なので、「tmpAry」の要素数は「2 – 0 + 1」つまり「3」となります。

例のように「LBound」関数と「UBound」関数を組み合わせることで、インデックス「下限」の値に関わらず、対象配列の要素数を求めることができます。

対象配列の要素数を求めるための数式

UBound(対象配列) – LBound(対象配列) + 1

配列を使う効果

配列には次のメリットがあります。順番に解説します。

処理速度が速い
可読性が高まる

処理速度が速い

配列を使わないコード例

Dim sampleSh As Worksheet
Set sampleSh = ThisWorkbook.Worksheets("サンプル")

Dim i As Long
Dim j As Long
For i = 1 To 100
    For j = 1 To 100
        sampleSh.Cells(i, j).Value = sampleSh.Cells(i, j).Value + 1
    Next
Next

「サンプル」シート上の「A1」から100行、100列の範囲で、セルの値に「1」を加算しています。ループ処理を行方向・列方向それぞれに「100」回繰り返し、セルの値に直接演算をかけています。

これを配列を使って書き直すと、次のようになります。

配列を使ったコード例

Dim sampleSh As Worksheet
Set sampleSh = ThisWorkbook.Worksheets("サンプル")
Dim trgtRng As Range
Set trgtRng = sampleSh.Range(sampleSh.Cells(1, 1), sampleSh.Cells(100, 100))
Dim trgtAry()
trgtAry = trgtRng.Value

Dim i As Long
Dim j As Long
For i = LBound(trgtAry, 1) To UBound(trgtAry, 1)
    For j = LBound(trgtAry, 2) To UBound(trgtAry, 2)
        trgtAry(i, j) = trgtAry(i, j) + 1
    Next
Next

trgtRng.Value = trgtAry

対象範囲を配列「trgtAry」に格納し、配列の全要素に「1」を加算した後、対象範囲に「trgtAry」の値を出力しています。配列を使わないコードと結果は同じです。

それぞれのコードの処理速度については次の通りとなりました。秒数は、数回実行した平均値をとっています。

配列を使わないコードの処理速度：0.09 秒
配列を使ったコードの処理速度　：1.38 秒

実に15倍以上の開きがあります。ExcelVBAでは、Rangeなどの「オブジェクト」を操作する回数が多いほど処理に時間がかかるため、これだけの差が出ています。

先ほどの例では、配列を使わないコードの場合は合計で10,000回（100×100）もRangeオブジェクトに対して加算処理を行っています。
一方、配列を使ったコードの場合は、配列内で処理をした後、最後に1回だけRangeオブジェクトに対して代入処理を行っています。

処理速度が遅いと感じた時は、配列を使ってオブジェクトへの処理回数を減らせないかを意識してチェックしてみましょう。うまく書き直すことができれば、一気に処理速度が速いコードになることもあるので、ぜひ覚えておきましょう。

可読性が高まる

配列を使わないコード例

Dim delShName1 As String
delShName1 = "Sheet1"
Dim delShName2 As String
delShName2 = "Sheet2"
Dim delShName3 As String
delShName3 = "Sheet3"

If ActiveSheet.Name = delShName1 Then
    ActiveSheet.Name = "サンプル1"
ElseIf ActiveSheet.Name = delShName2 Then
    ActiveSheet.Name = "サンプル2"
 ElseIf ActiveSheet.Name = delShName3 Then
    ActiveSheet.Name = "サンプル3"
End If

アクティブシートの名前が「Sheet1」なら「サンプル1」に「Sheet2」なら「サンプル2」に「Sheet3」なら「サンプル3」にシート名を変更するコードです。

これを配列を使って書き直すと、次のようになります。

配列を使ったコード例

Dim delShNameAry(1 To 3)
delShNameAry(1) = "Sheet1"
delShNameAry(2) = "Sheet2"
delShNameAry(3) = "Sheet3"

Dim i As Long
For i = LBound(delShNameAry) To UBound(delShNameAry)
    If ActiveSheet.Name = delShNameAry(i) Then
        ActiveSheet.Name = "サンプル" & i
    End If
Next

まず、「Sheet1」「Sheet2」「Sheet3」を配列「delShNameAry」に格納し、ループ処理実行します。この時、配列のインデックスとシート名末尾の数字を一致させていることがポイントです。よって、配列に定義したシート名のいずれかと合致すれば、シート名の内「Sheet」を「サンプル」に書き換える処理であることがわかります。

配列の使いどころを実例で紹介

任意の要素に処理を実行し配列で取得

ここでは例として、「全売上データを税込に更新して配列で取得」するサンプルコードを紹介します。

'対象となるシート、範囲を定義
Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("売上")
Dim trgtRng As Range
Set trgtRng = trgtSh.Range(trgtSh.Cells(2, 1), trgtSh
.Cells(27, 3))

'対象範囲のデータを配列に格納する
Dim tmpAry As Variant
tmpAry = trgtRng.Value

'対象となる税率を変数に定義する
Dim tax As Long
tax = 8

'税込み計算の対象となる「売上」列のインデックスを変数に定義する
Dim trgtIndex As Long
trgtIndex = 3

Dim i As Long
Dim trgtPrice As Double
For i = LBound(tmpAry, 1) To UBound(tmpAry, 1)
    '税込み計算前の値を変数に格納し、設定した税の課税計算をする
    trgtPrice = tmpAry(i, trgtIndex)
    trgtPrice = trgtPrice + (trgtPrice * tax / 100)
    '計算された値を小数第一位で繰り上げる
    trgtPrice = WorksheetFunction.RoundUp(trgtPrice, 0)
    
    '計算した値を配列に書き込む
    tmpAry(i, trgtIndex) = trgtPrice
Next

'配列をシートに出力する
trgtRng.Value = tmpAry

上記コードの処理結果は、次の画像のようになります。

順にコードを解説します。

まず、次の冒頭部分で、対象となるワークシート「trgtSh」、対象範囲「trgtRng」を定義します。定義した変数「trgtRng」の値を配列「tmpAry」に格納します。

セル範囲の値「.Value」を配列に格納した場合、1次元・2次元方向ともに、インデックスの最小値は「1」となることにも注意してください。

'対象となるシート、範囲を定義
Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("売上")
Dim trgtRng As Range
Set trgtRng = trgtSh.Range(trgtSh.Cells(2, 1), trgtSh.Cells(27, 3))

'対象範囲のデータを配列に格納する
Dim tmpAry As Variant
tmpAry = trgtRng.Value

配列にデータを格納した後、対象となる税率を変数「tax」に、税率計算の対象となる配列のインデックスを変数「trgtIndex」に定義します。

繰り返しになりますが、配列にセル範囲の値「.Value」を格納した場合はインデックスの最小値は「1」となるので、インデックスは「店番 = 1」「店名 = 2」「売上 = 3」となります。そのため「trgtIndex」には「3」を定義しています。

'対象となる税率を変数に定義する
Dim tax As Long
tax = 8

'税込み計算の対象となる「売上」列のインデックスを変数に定義する
Dim trgtIndex As Long
trgtIndex = 3

最後に、「ForNext」を使って、配列「tmpAry」内の「売上」データ全てを税込の値に書き換えます。

ループ処理は、1次元方向のインデックスの最小値「LBound(tmpAry, 1)」から最大値「UBound(tmpAry, 1)」まで行います。ループ内処理では、まず変数「trgtPrice」に課税計算前の値「tmpAry(i, trgtIndex)」を格納します。続いて、「trgtPrice」に税率「tax」をかけて100で割った数値（税額）を加算します。

これを次の一覧のように最後の要素まで繰り返します。

tmpAry(1, trgtIndex)　 ※i = 1
tmpAry(2, trgtIndex)　 ※i = 2
tmpAry(3, trgtIndex)　 ※i = 3
・・・
tmpAry(26, trgtIndex)　※i = 26

ここまでの計算で求められた数値は、小数点以下の数が含まれる可能性があるので、「RoundUp」関数を使って、小数点第一位で繰り上げ処理を行います。最後に、課税計算された「trgtPrice」を配列の値「tmpAry(i, trgtIndex)」に書き込みます。

ループが終了し、配列の全ての値への処理が終わったら、最後に元のセル範囲「trgtRng」に配列の値を代入すると、全売上データを税込に更新することができます。

Dim i As Long
Dim trgtPrice As Double
For i = LBound(tmpAry, 1) To UBound(tmpAry, 1)
    '税込み計算前の値を変数に格納し、設定した税の課税計算をする
    trgtPrice = tmpAry(i, trgtIndex)
    trgtPrice = trgtPrice + (trgtPrice * tax / 100)
    '計算された値を小数第一位で繰り上げる
    trgtPrice = WorksheetFunction.RoundUp(trgtPrice, 0)

    '計算した値を配列に書き込む
    tmpAry(i, trgtIndex) = trgtPrice
Next

'配列をシートに出力する
trgtRng.Value = tmpAry

条件を満たすレコードを配列で取得

ここでは例として、「売上データの内、利益が100万円を超えるレコードを取得」するサンプルコードを紹介します。

'対象となるシート、範囲を定義
Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("利益")
Dim trgtRng As Range
Set trgtRng = trgtSh.Range(trgtSh.Cells(2, 1), trgtSh.Cells(27, 4))

'対象範囲のデータを配列に格納する
Dim tmpAry As Variant
tmpAry = trgtRng.Value

'条件の基準となる金額を変数に定義
Dim borderProfit As Long
borderProfit = 1000000

'判定対象となる「利益」列のインデックスを変数に定義する
Dim trgtIndex As Long
trgtIndex = 4

Dim i As Long
Dim trgtProfit As Long
Dim trgtAry As Variant
Dim j As Long
For i = LBound(tmpAry, 1) To UBound(tmpAry, 1)
    '利益を変数に定義
    trgtProfit = tmpAry(i, trgtIndex)
    
    '利益が基準額を超えていたら、取得結果となる配列「trgtAry」に格納
    If trgtProfit > borderProfit Then
        If IsArray(trgtAry) = False Then
            '1つ目の要素格納時には、「trgtAry」は配列ではないので、配列として宣言し直す
            ReDim trgtAry(1 To 4, 1 To 1) As Variant
        Else
            '2つ目以降の要素格納時には、その時点での最大要素から要素を1つ増やす
            ReDim Preserve trgtAry(1 To 4, 1 To UBound(trgtAry, 2) + 1) As Variant
        End If
        
        'tmpAryの2次元の要素数だけループする
        For j = LBound(tmpAry, 2) To UBound(tmpAry, 2)
            '最終次元しか拡張できない配列の特性のため、「trgtAry」と「tmpAry」の要素の向きは逆になっている
            '拡張された最終要素「UBound(trgtAry,2)」に値を格納
            trgtAry(j, UBound(trgtAry, 2)) = tmpAry(i, j)
        Next
    End If
Next

'配列をシートに出力する
Dim pasteRng As Range
Set pasteRng = trgtSh.Range(trgtSh.Cells(2, 8), trgtSh.Cells(2 + UBound(trgtAry, 2) - 1, 11))
'「trgtAry」と「tmpAry」の要素の向きは逆になっているため、次元を入れ替える「WorksheetFunction.Transpose」を使用してシート出力
pasteRng.Value = WorksheetFunction.Transpose(trgtAry)

上記コードの処理結果は、次の画像のようになります。

'対象となるシート、範囲を定義
Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("利益")
Dim trgtRng As Range
Set trgtRng = trgtSh.Range(trgtSh.Cells(2, 1), trgtSh.Cells(27, 4))

'対象範囲のデータを配列に格納する
Dim tmpAry As Variant
tmpAry = trgtRng.Value

配列にデータを格納した後、判定基準となる金額を変数「borderProfit」に、判定の対象となる配列のインデックスを変数「trgtIndex」に定義します。

繰り返しになりますが、配列にセル範囲の値「.Value」を格納した場合はインデックスの最小値は「1」となるので、インデックスは「商品名 = 1」「売上 = 2」「経費 = 3」「利益 = 4」となります。そのため「trgtIndex」には「4」を定義しています。

'条件の基準となる金額を変数に定義
Dim borderProfit As Long
borderProfit = 1000000

'判定対象となる「利益」列のインデックスを変数に定義する
Dim trgtIndex As Long
trgtIndex = 4

次に、「ForNext」を使って、配列「tmpAry」から条件に合致したレコードだけを動的配列「trgtAry」に格納します。

ループ処理は、1次元方向のインデックスの最小値「LBound(tmpAry, 1)」から最大値「UBound(tmpAry, 1)」まで行います。ループ内処理では、まず変数「trgtPofit」に対象レコードの利益の値「tmpAry(i, trgtIndex)」を格納します。続いて、「If文」を使用して条件に合致するかどうかを判定します。

条件に合致した場合、結果を格納する配列「trgtAry」に値を格納していきます。この時、1つ目の要素を格納する際には「trgtAry」が配列ではないことに注意しましょう。「trgtAry」が配列かどうかを判定するため「IsArray」関数を使用します。「IsArray」関数は、対象配列が配列なら「True」、配列でなければ「False」を返します。

「trgtAry」が配列でない場合（1つ目の要素を格納する場合）は、2次元方向の要素が「1 To 1」、「trgtAry」が配列の場合（2つ目以降の要素を格納する場合）は、2次元方向の要素が「1 To UBound(trgtAry,2) + 1」となります。これで、要素を格納する度に、2次元方向の要素が1つずつ増えていくことになります。

次に、「tmpAry」の2次元方向のインデックスの最小値「LBound(tmpAry, 2)」と最大値「UBound(tmpAry, 2)」を使って再度ループ処理を行います。「trgtAry」と「tmpAry」では行列方向が入れ替えているので、「trgtAry(j, UBound(trgtAry, 2)) = tmpAry(i, j)」で目的の値を格納することができます。

Dim i As Long
Dim trgtProfit As Long
Dim trgtAry As Variant
Dim j As Long
For i = LBound(tmpAry, 1) To UBound(tmpAry, 1)
    '利益を変数に定義
    trgtProfit = tmpAry(i, trgtIndex)
    
    '利益が基準額を超えていたら、取得結果となる配列「trgtAry」に格納
    If trgtProfit > borderProfit Then
        If IsArray(trgtAry) = False Then
            '1つ目の要素格納時には、「trgtAry」は配列ではないので、配列として宣言し直す
            ReDim trgtAry(1 To 4, 1 To 1) As Variant
        Else
            '2つ目以降の要素格納時には、その時点での最大要素から要素を1つ増やす
            ReDim Preserve trgtAry(1 To 4, 1 To UBound(trgtAry, 2) + 1) As Variant
        End If
        
        'tmpAryの2次元の要素数だけループする
        For j = LBound(tmpAry, 2) To UBound(tmpAry, 2)
            '最終次元しか拡張できない配列の特性のため、「trgtAry」と「tmpAry」の要素の向きは逆になっている
            '拡張された最終要素「UBound(trgtAry,2)」に値を格納
            trgtAry(j, UBound(trgtAry, 2)) = tmpAry(i, j)
        Next
    End If
Next

ループが終了したら、取得した配列を基に貼り付けセル範囲「pasteRng」を定義して、配列の値を代入します。「pasteRng」の最終行は、「貼り付け開始行から取得したレコードの数 – 1」で取得しています。

また、「trgtAry」は取得する要素数を可変にするため、元の配列「tmpAry」とは1次元・2次元の順番が逆になっています。そのためセル範囲代入時に、行列を入れ替えるワークシート関数「Transpose」を使用しています。

以上で、条件を満たすレコードを配列で取得し、シートに出力することができます。

'配列をシートに出力する
Dim pasteRng As Range
Set pasteRng = trgtSh.Range(trgtSh.Cells(2, 8), trgtSh.Cells(2 + UBound(trgtAry, 2) - 1, 11))
'「trgtAry」と「tmpAry」の要素の向きは逆になっているため、次元を入れ替える「WorksheetFunction.Transpose」を使用してシート出力
pasteRng.Value = WorksheetFunction.Transpose(trgtAry)