1次元配列とは。静的配列と動的配列エクセルVBA

エクセルVBAの1次元配列変数の使い方の説明です。

奥が深い配列ですが、普通に必要な部分を押えておけば十分常用できます。

まずは、使えることが重要です。

こんにちは、じゅんぱ店長（@junpa33）です。

エクセルVBAで変数を利用するとき、複数の同じ型で変数を設定したいときがあります。

そんな時、出番となるのが配列です。

プログラムの基礎

コンテンツ

1次元配列の静的配列と動的配列を最速に理解

変数は、記述コードが多くなればなるほど、実行させたい事が増えれば増えるほど、種類と数が増えてくるものです。

配列変数は、そういった場面で実力を発揮してきます。

簡単なコード編成ですと逆に要りません。

ですので、簡単なコードでの解説であれば、「ふ～ん！」程度で、何のことかよくわからないままだと思います。

ということでこの例題としては、より実用的なサンプルで説明していきたいと思います。（ブログカテゴリー的には、「業務ツールのプチなVBAコード」的なものになっています。）

チョッとじっくり目にお読みいただければと思います。

配列には、「静的配列」と「動的配列」があります。

また、配列の次元として、「一次元配列」と「多次元配列」があります。

「多次元配列」と言っても、一般実用としては「二次元配列」までになると思います。

今回の説明では、「一次元配列」について行っていきます。

説明はこのような順序で行っていきます。

今回の例題を紹介します。
配列を使わずにVBAを組み立てます
配列を使ってVBAを組み立てます（静的配列）
配列を使ってVBAを組み立てます（動的配列）

今回の例題を紹介します

お買い物の「お買い得情報の表示リスト」を作成しました。

右側の野菜のお買い物「価格表」には5店舗のスーパーのチラシ価格の一覧表（データベース）となっています。
左側の購入品リストに、今日の「買い出し品」を入力します。

エクセルVBAを実行すれば、最安価格がいくらで、どこのスーパーで販売しているかが「買い出し野菜」毎に表示されるようにします。

というサンプルコードについてです。

配列変数を使わずにVBAを組み立てます

最初に完成形のコードを紹介します。

配列変数を使わない

一つのプロシージャーとしては無駄に長大になっています。

ただし、コード組み立ての進め方的には、

買い出し野菜名の変数化
買い出し野菜をデータベース検索し行番号変数化
買い出し野菜の最安価格を変数化
買い出し野菜の最安販売先の列番号を変数化
買い出し野菜の最安価格と販売先を所定セルに表示

の順序は配列変数の時でも共通です。

参考記事

2020年12月15日 VBAで使うMatch関数活用度アップでテッパン関数に！

2021年8月11日 Max・Min・Average関数で最大値・最小値・平均を求める

2020年11月8日 VBA 回数不定のループ処理はDo LoopとFor Each

2020年11月4日 If条件文のVBAコードの組み方。条件の絞り方を最速理解

VBA

Sub 一次元配列1()

Dim vegeA, vegeB, vegeC As String
Dim vegeD, vegeE, vegeF As String
Dim vegeG As String

Dim vAr, vBr, vCr, vDr As Long
Dim vEr, vFr, vGr As Long

Dim vAc, vBc, vCc, vDc As Long
Dim vEc, vFc, vGc As Long

Dim vArVal, vBrVal, vCrVal, vDrVal As Long
Dim vErVal, vFrVal, vGrVal As Long

Dim vArRange, vBrRange, vCrRange As Range
Dim vDrRange, vErRange, vFrRange As Range
Dim vGrRange As Range

'買い出し野菜名の変数化
Worksheets("Sheet1").Select
vegeA = Range("B3").Value
vegeB = Range("B4").Value
vegeC = Range("B5").Value
vegeD = Range("B6").Value
vegeE = Range("B7").Value
vegeF = Range("B8").Value
vegeG = Range("B9").Value

'買い出し野菜をデータベース検索し行番号変数化
On Error Resume Next
vAr = WorksheetFunction.Match(vegeA, Range("F3:F22"), 0) + 2
vBr = WorksheetFunction.Match(vegeB, Range("F3:F22"), 0) + 2
vCr = WorksheetFunction.Match(vegeC, Range("F3:F22"), 0) + 2
vDr = WorksheetFunction.Match(vegeD, Range("F3:F22"), 0) + 2
vEr = WorksheetFunction.Match(vegeE, Range("F3:F22"), 0) + 2
vFr = WorksheetFunction.Match(vegeF, Range("F3:F22"), 0) + 2
vGr = WorksheetFunction.Match(vegeG, Range("F3:F22"), 0) + 2

'買い出し野菜の最安価格を変数化
vArVal = WorksheetFunction.Min(Range(Cells(vAr, 7), Cells(vAr, 11)))
vBrVal = WorksheetFunction.Min(Range(Cells(vBr, 7), Cells(vBr, 11)))
vCrVal = WorksheetFunction.Min(Range(Cells(vCr, 7), Cells(vCr, 11)))
vDrVal = WorksheetFunction.Min(Range(Cells(vDr, 7), Cells(vDr, 11)))
vErVal = WorksheetFunction.Min(Range(Cells(vEr, 7), Cells(vEr, 11)))
vFrVal = WorksheetFunction.Min(Range(Cells(vFr, 7), Cells(vFr, 11)))
vGrVal = WorksheetFunction.Min(Range(Cells(vGr, 7), Cells(vGr, 11)))

'買い出し野菜の最安販売先の列番号を変数化
For Each vArRange In Range(Cells(vAr, 7), Cells(vAr, 11))
    If vArRange.Value = vArVal Then
        vAc = vArRange.Column
    End If
Next
For Each vBrRange In Range(Cells(vBr, 7), Cells(vBr, 11))
    If vBrRange.Value = vBrVal Then
        vBc = vBrRange.Column
    End If
Next
For Each vCrRange In Range(Cells(vCr, 7), Cells(vCr, 11))
    If vCrRange.Value = vCrVal Then
        vCc = vCrRange.Column
    End If
Next
For Each vDrRange In Range(Cells(vDr, 7), Cells(vDr, 11))
    If vDrRange.Value = vDrVal Then
        vDc = vDrRange.Column
    End If
Next
For Each vErRange In Range(Cells(vEr, 7), Cells(vEr, 11))
    If vErRange.Value = vErVal Then
        vEc = vErRange.Column
    End If
Next
For Each vFrRange In Range(Cells(vFr, 7), Cells(vFr, 11))
    If vFrRange.Value = vFrVal Then
        vFc = vFrRange.Column
    End If
Next
For Each vGrRange In Range(Cells(vGr, 7), Cells(vGr, 11))
    If vGrRange.Value = vGrVal Then
        vGc = vGrRange.Column
    End If
Next

'買い出し野菜の最安価格と販売先を所定セルに表示
Range("C3") = vArVal
Range("D3") = Cells(2, vAc)

Range("C4") = vBrVal
Range("D4") = Cells(2, vBc)

Range("C5") = vCrVal
Range("D5") = Cells(2, vCc)

Range("C6") = vDrVal
Range("D6") = Cells(2, vDc)


Range("C7") = vErVal
Range("D7") = Cells(2, vEc)

Range("C8") = vFrVal
Range("D8") = Cells(2, vFc)

Range("C9") = vGrVal
Range("D9") = Cells(2, vGc)

End Sub

実行結果

赤枠のように正常に表示されました。

結構このコード組み立てはVBAの力業で作った感じで、あまり上手くない例だと思います。

配列変数を使ってVBAを組み立てます（静的配列）

次に、配列変数を使ったVBAコードを例示します。

配列変数を使う（静的配列）

この例は、「静的配列」と言って設定する変数の数が既に決まっている場合の例です。

今回は「買い出し野菜」が7種類ということでのコード組み立てになっています。

配列変数の数を明示的にするため宣言時に「（1 to 7）」というように表示します。

「（7）」というように記述してもいいですが、その場合は「0から7」まで、つまり変数の数が8個とみなされます。

VBA

Sub 一次元配列2()

Dim n As Long, m As Long
Dim vege(1 To 7) As String
Dim vr(1 To 7) As Long
Dim vc(1 To 7) As Long
Dim vrVal(1 To 7) As Long
Dim vrRange(1 To 7) As Range

'買い出し野菜名の変数化
    Worksheets("Sheet1").Select
    For n = 1 To 7
        vege(n) = Range("B" & n + 2).Value
    Next n

'買い出し野菜をデータベース検索し行番号変数化
    For n = 1 To 7
        On Error Resume Next
        vr(n) = WorksheetFunction.Match(vege(n), Range("F3:F22"), 0) + 2
    Next n

'買い出し野菜の最安価格を変数化
    For n = 1 To 7
        vrVal(n) = WorksheetFunction.Min(Range(Cells(vr(n), 7), _
            Cells(vr(n), 11)))
    Next n

'買い出し野菜の最安販売先の列番号を変数化
    For n = 1 To 7
        For Each vrRange(n) In Range(Cells(vr(n), 7), Cells(vr(n), 11))
            If vrRange(n).Value = vrVal(n) Then
                vc(n) = vrRange(n).Column
            End If
        Next
    Next n

'買い出し野菜の最安価格と販売先を所定セルに表示
    m = 3
    For n = 1 To 7
        Range("C" & m) = vrVal(n)
        Range("D" & m) = Cells(2, vc(n))
        m = m + 1
    Next n

End Sub

実行結果

青枠のように正常に表示されました。

配列変数を使うと、恐ろしく簡潔にコード組み立てが出来るようになりました。

ただ、これでもまだ、大きな問題が存在しています。

一回に付き　”買い出し野菜が7種類しか買えない。”　という大きな問題があるからです。

配列変数を使ってVBAを組み立てます（動的配列）

最後にもう一つの配列変数を使ったVBAコードです。

配列関数を使う（動的配列）

こちらの例は「動的配列」という、使うたびに変数の数が違う（固定していない）場合の例です。

その時々によって買い物の野菜の種類数が変わる時のVBAコードになります。

最初に配列変数を宣言しますが、「変数の数はまだわかりませんよ！」という記述をします。「()」空のカッコを付けます。

その時の変数の数を調べた後、「お待たせしました！」と「ReDim」で変数の数を再宣言します。（この変数の数も実は変数です。）

VBA

Sub 一次元配列3()

Dim n, m, BR As Long
Dim vege() As String
Dim vr() As Long
Dim vc() As Long
Dim vrVal() As Long
Dim vrRange() As Range

'その時の変数の数を調べる
    Worksheets("Sheet1").Select
    BR = Cells(Rows.Count, 2).End(xlUp).Row - 2
    If BR < 1 Then Exit Sub

'ReDimを使って、変数のその時の確定数を宣言する
ReDim vege(BR), vr(BR), vc(BR) As Long
ReDim vrVal(BR), vrRange(BR) As Range

'変数の数分ループする
    For n = 1 To BR

'買い出し野菜名の変数化
        vege(n) = Range("B" & n + 2).Value

'買い出し野菜をデータベース検索し行番号変数化
        On Error Resume Next
        vr(n) = WorksheetFunction.Match(vege(n), Range("F3:F22"), 0) + 2

'買い出し野菜の最安価格を変数化
        vrVal(n) = WorksheetFunction.Min(Range(Cells(vr(n), 7), _
            Cells(vr(n), 11)))

'買い出し野菜の最安販売先の列番号を変数化
        For Each vrRange(n) In Range(Cells(vr(n), 7), Cells(vr(n), 11))
            If vrRange(n).Value = vrVal(n) Then
                vc(n) = vrRange(n).Column
            End If
        Next
    Next n

'買い出し野菜の最安価格と販売先を所定セルに表示
    m = 3
    For n = 1 To BR
        Range("C" & m) = vrVal(n)
        Range("D" & m) = Cells(2, vc(n))
        m = m + 1
    Next n

End Sub

実行結果