1次元配列変数の利用法。静的配列と動的配列を最速に理解する

vbahairetueyecatch

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

基本的な使い方が理解できればまずはOKです。

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

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

 

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

そんな時、出番となるのが配列変数ということです。

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

 

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

 

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

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

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

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

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

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

 

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

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

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

 

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

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

 

 

二次元配列についての記事はこちらになります。

vbahairetu3eyecatchエクセルVBA 2次元動的配列変数を実例的利用法で最速に理解

配列に関しての関数・ステートメントについての記事はこちらになります。

vbahairetu2eyecatch配列のArray・LBound・RBound関数と各ステートメントを最速に理解

 

今回の例題を紹介します

 

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

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

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

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

 

vbahairetu001

 

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

 

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

配列変数を使わない

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

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

コード作成のポイント

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

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

 

MEMO

Match関数についてはこちらを参考にしてください。

エクセルVBAで使うMatch関数 活用度アップでテッパン関数に!

Min関数についてはこちらを参考にしてください。

エクセルVBA ワークシート関数 Max・Min・Averageで最大値・最小値・平均を求める

For Each~Next構文についてはこちらを参考にしてください。

VBA 回数不定のループ処理はDo LoopとFor Each

If条件文についてはこちらを参考にしてください。

エクセルVBA 「If条件文」を最速に理解。条件の絞り方

 

コード
Sub 一次元配列1()

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

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

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

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

Dim vArRange As Range, vBrRange As Range, vCrRange As Range
Dim vDrRange As Range, vErRange As Range, 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

実行結果

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

vbahairetu002

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

 

 

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

 

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

配列変数を使う(静的配列)

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

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

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

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

 

コード
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

実行結果

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

vbahairetu003

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

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

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

 

 

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

 

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

配列関数を使う(動的配列)

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

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

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

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

 

コード
Sub 一次元配列3()

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

'その時の変数の数を調べる。数は「BR」になりました。
Worksheets("Sheet1").Select
BR = Cells(Rows.Count, 2).End(xlUp).Row - 2
If BR < 1 Then Exit Sub

'ReDimを使って、変数のその時の確定数を宣言する
ReDim vege(BR) As String, vr(BR) As Long, vc(BR) As Long
ReDim vrVal(BR) As Long, 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

実行結果

vbahairetu004
vbahairetu005

購入品リストに、今回は「きゅうり」「玉ねぎ」「ピーマン」を増やした時の

最安情報が表示されました。

その日によって買い出しするものが変わりますので、こちらの方が一番実用的なコード作成だと思います。

 

 

1次元配列変数の利用法を最速に理解のまとめ

 

ご覧いただいた通り、

配列関数は、エクセルVBAのコードでは必須のテクニックになっています。

さらに作成したプロシージャーの再利用を考えた場合は、変数を「動的配列」でセットしておく方が吉になると思います。

1次元配列の作成手順が解れば ”ムチャムチャ” 難しくはないと思います。

 

短期間でエクセルVBAの独学習得を目指したいなら

 

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること!

です。

正直、VBAの学習について自分の周りの仕事(業務)からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

 

このサイトでよく使うVBAのコードのトピックを纏めています。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。