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

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

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

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