2次元動的配列の実務での利用法エクセルVBA

エクセルVBAの二次元動的配列変数の使い方の例を説明します。

この二次元動的配列を使えるようになれば、VBAコード作成時の強力なツールになるでしょう。

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

エクセルVBAで変数を利用するとき、二次元配列を知っていれば何かと便利なことがあります。

今回はそんな配列をより実際に近い例題で説明していきます。

プログラムの基礎

コンテンツ

2次元動的配列を実例的利用法で最速理解

先の記事

2020年12月12日 1次元配列とは。静的配列と動的配列エクセルVBA

で一次元配列の説明をさせていただきましたが、

今回は二次元配列についてのテーマで記事を進めさせていただきます。

二次元配列は、「変数名（1次元インデックス番号，2次元インデクス番号）」という様に記述します。

これは、「Cells（行，列）」と記述方法において値の配置の考え方と共通しています。

例えば、「変数名 = 飲み物」であった場合

	A列	B列	C列
1	A	B	C
2	あ	緑茶	ウーロン茶
3	い	コーヒー	カフェオレ
4	う	紅茶	アップルティ

二次元配列のシート上のセルとの位置関係は完全に一致します。

配列の宣言は・・・Dim　飲み物(３，３)　As　String

Cells(１，１)　=　飲み物（１，１）　＜A＞

Cells(１，２)　=　飲み物（１，２）　＜B＞

Cells(１，３)　=　飲み物（１，３）　＜C＞

Cells(２，１)　=　飲み物（２，１）　＜あ＞

Cells(２，２)　=　飲み物（２，２）　＜緑茶＞

Cells(２，３)　=　飲み物（２，３）　＜ウーロン茶＞

Cells(３，１)　=　飲み物（３，１）　＜い＞

Cells(３，２)　=　飲み物（３，２）　＜コーヒー＞

Cells(３，３)　=　飲み物（３，３）　＜カフェオレ＞

Cells(４，１)　=　飲み物（４，１）　＜う＞

Cells(４，２)　=　飲み物（４，２）　＜緑茶＞

Cells(４，３)　=　飲み物（４，３）　＜アップルティ＞

二次元配列のインデックス番号とシートのセル番号は完全連携していることは覚えておきましょう。

それでは例題で二次元配列を説明していきます。

2020年12月12日 1次元配列とは。静的配列と動的配列エクセルVBA

2020年12月13日 Array・LBound・RBoundの配列関数と各ステートメントを最速理解

例題の紹介をします

今回は、一次元配列と比較対象出来るように、

先の記事で使った例題を利用します。

先回は、これを一次元配列を使ってコード化して最安情報の欄に表示をさせました。

今回の例題のテーマ

二次元配列を利用して最安情報の空欄に値を表示させていきたいと思います。
配列の種別的には動的配列によって、「購入品リスト」や「価格表」のデータ量がその時々に増減しても配列で対応できるようにしたいと思います。

技術的な部分も含めて先の記事を参考にしていただき、VBAコードのコード提示とその説明という方法で進めていきます。

例題のコードはこれです

二次元配列変数

今回は二次元配列ですので、ReDimの再宣言は１つです。

VBAコード組み立ての流れとしては、

VBA

Option Explicit

Sub 二次元配列()

    Dim vege() As Variant
    Dim FR As Long, R2C As Long
    Dim myData As Variant
    Dim s As Long, t As Long, n As Long
    Dim AR As Long, MtRow As Long, MinVal As Variant
    Dim DaRange As Range
        Worksheets("Sheet1").Select
'配列に格納するデータ範囲を考慮します。
        FR = Cells(Rows.Count, 6).End(xlUp).Row - 1
        R2C = Cells(2, Columns.Count).End(xlToLeft).Column - 5
'インデックス数確定により配列の再宣言をします。
        ReDim vege(FR, R2C)
'価格表のデータを二次元配列に格納します。
        For s = 1 To FR
            For t = 1 To R2C
                vege(s, t) = Cells(s + 1, t + 5)
            Next t
        Next s
'買い出し品の品数を数えます。
        AR = Cells(Rows.Count, 2).End(xlUp).Row - 2
'価格表野菜種をRangeオブジェクトに格納します。
        Set DaRange = Range(Cells(2, 6), Cells(FR, 6))
        For n = 1 To AR
'買い出し品を価格表の野菜リストから探します。
            On Error Resume Next
'MtRowは配列格納範囲の先頭行からの行数です。
            MtRow = WorksheetFunction.Match(Cells(n + 2, 2), DaRange, 0)
'探し出した野菜の最低価格を調べます。
            MinVal = WorksheetFunction.Min(Range(Cells(MtRow + 1, 7), _
                Cells(MtRow + 1, R2C + 5)))
'最低価格の販売元を確認して最終、最安情報リストに表示します。
            For t = 1 To R2C
                If vege(MtRow, t) = MinVal Then
                Worksheets("Sheet1").Range("C" & n + 2) = MinVal
                Worksheets("Sheet1").Range("D" & n + 2) = vege(1, t)
                End If
            Next t
        Next n
End Sub

実行結果

参考記事

2020年10月27日 For～Nextのループと入れ子構造をVBA最速理解

2020年12月5日データ入力済セルの最終行番号を取得する

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

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

価格表のデータ量を調べます。

価格表の最終行と最終列を調べます。

価格表にあるデータ自体は行と列の隅にあるわけではないので、その分を加味してデータセルの大きさ計ります。

FR = Cells(Rows.Count, 6).End(xlUp).Row - 1 
R2C = Cells(2, Columns.Count).End(xlToLeft).Column - 5

青枠囲みのセルデータを配列に格納します。

二次元配列の再宣言をします。

インデックス番号として「FR」価格表のデータ行番号の最大値、「R2C」価格表のデータ列番号の最大値で再宣言します。

その都度、データの最大行と最大列が変化するものとして、この値も変数として扱います。（動的配列）

ReDim vege(FR, R2C)

価格表のデータを二次元配列に格納していきます。

For～Nextの入れ子構造で配列に格納していきます。

これだけのコード行で格納完了です。

For s = 1 To FR
    For t = 1 To R2C
        vege(s, t) = Cells(s + 1, t + 5)
    Next t
Next s

格納したデータはこのようになります。

項目行と項目列の配列インデックス番号は、黄色と青色の部分になります。

購入品リスト側の品数を調べます。

購入品リストにある買い出し品の行数を調べます。

買い出し品を価格表と対照する前に、価格表の野菜種類のセル部分をオブジェクト変数として格納します。

'買い出し品の品数を数えます。
AR = Cells(Rows.Count, 2).End(xlUp).Row - 2

'価格表野菜種をRangeオブジェクトに格納します。
Set DaRange = Range(Cells(2, 6), Cells(FR, 6))

目的の買い出し品の価格表行番号と、一番安い価格を選び出します。

Match関数を使って買い出し品に適合する野菜がある行番号を調べます。

この行番号はオブジェクト変数で格納したセル範囲の最上部から数えた行数になります。

最低価格はMin関数で調べますが、範囲指定をセル範囲で指定しています。

'買い出し品を価格表の野菜リストから探します。
    On Error Resume Next
    'MtRowは配列格納範囲の先頭行からの行数です。
    MtRow = WorksheetFunction.Match(Cells(n + 2, 2), DaRange, 0)
'探し出した野菜の最低価格を調べます。
    MinVal = WorksheetFunction.Min(Range(Cells(MtRow + 1, 7), Cells(MtRow + 1, R2C + 5)))

最安価格と、販売スーパー名を最安情報リストに表示していきます。

最低価格になった値を持つ配列のインデックス番号を取り出します。

その番号は、スーパーの個別番号になっているので、スーパー名を取り出すことが出来ます。

'最低価格の販売元を確認して最終、最安情報リストに表示します。
    For t = 1 To R2C
        If vege(MtRow, t) = MinVal Then
        Worksheets("Sheet1").Range("C" & n + 2) = MinVal
        Worksheets("Sheet1").Range("D" & n + 2) = vege(1, t)
        End If
    Next t