Excel VBA 配列を使いこなすためのプチサンプル紹介します

'空データを代入して初期化する方法
Sub ArraySample_01()
    Dim i As Long, j As Long
    Dim arr(5, 100) As String
    arr(1, 1) = "配列初期化テスト⓵"
    MsgBox "データ確認(" & arr(1, 1) & ")"
    For i = 0 To 5
        For j = 0 To 100
            arr(i, j) = ""   '空白を代入して消しています
        Next
    Next
    MsgBox "データ確認(" & arr(1, 1) & ")"
End Sub

'ReDim で宣言を再設定して初期化する方法
Sub ArraySample_02()
    ReDim arr(5, 100) As String
    arr(1, 1) = "配列初期化テスト⓶"
    MsgBox "データ確認(" & arr(1, 1) & ")"
    ReDim arr(5, 100)    'ReDimで再宣言して消しています
    MsgBox "データ確認(" & arr(1, 1) & ")"
End Sub

'Eraseステートメントで配列データを初期化する方法
Sub ArraySample_03()
    Dim arr(5, 100) As String
    arr(1, 1) = "配列初期化テスト"
    MsgBox "データ確認(" & arr(1, 1) & ")"
    Erase arr            'Eraseステートメントで消去しています
    MsgBox "データ確認(" & arr(1, 1) & ")"
End Sub

'Join関数で配列要素を連結する方法
Sub ArraySample_04()
    Dim arr(2) As String
    Dim arrStr As String
    arr(0) = "ABC"
    arr(1) = "DEF"
    arr(2) = "GHI"
 
    '区切り文字の指定しない（この場合はスペースが入る）
    arrStr = Join(arr) & vbCrLf                 'ABC DEF FHI
    '区切り文字を空白に指定
    arrStr = arrStr & Join(arr, "") & vbCrLf    'ABCDEFFHI
    '区切り文字に「|」を指定
    arrStr = arrStr & Join(arr, "|") & vbCrLf   'ABC|DEF|FHI
    '区切り文字を改行に指定
    arrStr = arrStr & Join(arr, vbCrLf) & vbCrLf 'ABC
                                                 'DEF
                                                 'FHI
    MsgBox arrStr
End Sub

'Array関数で配列を一括代入
Sub ArraySample_05()
    Dim n As Long
    Dim tgDate As Date, inDate As String
    Dim youbi As Variant
    youbi = Array("日", "月", "火", "水", "木", "金", "土")
    inDate = InputBox("｢年/月/日｣を入力して下さい！")
    If IsDate(inDate) Then
        tgDate = CDate(inDate)
        tgDate = Format(tgDate, "yyyy""年""mm""月""dd""日""")
    Else
        MsgBox "｢年/月/日｣が入力されませんでした！"
        Exit Sub
    End If
    n = Weekday(tgDate)
    MsgBox tgDate & " は " & youbi(n - 1) & "曜日です！"
End Sub

'セル範囲データをまとめて配列へ代入する
Sub ArraySample_06()
    Dim db As Variant      'セルデ－タ処理
    Dim i As Long, j As Long 'Loopカウンタ
    Dim endr As Long        '最終行
    Dim endc As Long        '最終列
    Dim a As String
 
    'セル範囲取得
    With ThisWorkbook.Sheets("Sheet1")
        endr = .Cells.SpecialCells(xlCellTypeLastCell).Row
        endc = .Cells.SpecialCells(xlCellTypeLastCell).Column

        ReDim db(endr, endc)
        'CurrentRegionプロパティでセル範囲の値を代入
        db = .Range("A1").CurrentRegion.Value
    End With
 
    With ThisWorkbook.Sheets("Sheet3")
        .Cells(1, 1) = "dt = new Array();"
        For i = 1 To endr
            a = "dt[" & i & "] = |"
            For j = 1 To endc
                a = a & db(i, j) & "|"
            Next
            .Cells(i + 1, 1) = a
        Next
    End With
End Sub

'セル範囲データを別シートのセルに書き出す
Sub ArraySample_07()
    Dim db() As Variant      'セルデ－タ処理
    Dim i As Long, j As Long 'Loopカウンタ
    Dim endr As Long        '最終行
    Dim endc As Long        '最終列
 
    'セル範囲取得
    With ThisWorkbook.Sheets("Sheet1")
        endr = .Cells.SpecialCells(xlCellTypeLastCell).Row
        endc = .Cells.SpecialCells(xlCellTypeLastCell).Column

        ReDim db(endr, endc)
        'CurrentRegionプロパティでセル範囲の値を代入
        db = .Range("A1").CurrentRegion.Value
    End With
 
    With ThisWorkbook.Sheets("Sheet2")
        For i = 1 To endr
            For j = 1 To endc
                .Cells(i, j) = db(i, j)
            Next
        Next
    End With
End Sub

'配列内のランダムなデータを昇順に並び替える
Sub ArraySample_08()
    Dim dat(10) As Long, damy As Long
    Dim i As Long, j As Long
    'サンプル数字をランダムに代入
    dat(1) = 3: dat(2) = 1: dat(3) = 8
    dat(4) = 0: dat(5) = 9:    dat(6) = 4
    dat(7) = 2: dat(8) = 5: dat(9) = 7: dat(10) = 6
    '昇順に並べ替えする
    For i = 1 To 10
        For j = i + 1 To 10
            If dat(i) > dat(j) Then
                damy = dat(i)
                dat(i) = dat(j)
                dat(j) = damy
            End If
        Next
    Next
    MsgBox dat(1) & " " & dat(2) & " " & dat(3) & " " _
            & dat(4) & " " & dat(5) & " " & dat(6) _
            & " " & dat(7) & " " & dat(8) & " " & _
            dat(9) & " " & dat(10)
End Sub

'Array関数を使った配列内のランダムなデータを降順に並び替える
Sub ArraySample_09()
    Dim dat As Variant, damy As Long
    Dim r As Long, j As Long
    'Array関数でランダムなサンプル数字を代入
    dat = Array(3, 1, 8, 0, 9, 4, 2, 5, 7, 6)
    '降順に並べ替える
    For j = 0 To 9
        For r = 9 To j Step -1
            If dat(j) < dat(r) Then
                damy = dat(j)
                dat(j) = dat(r)
                dat(r) = damy
            End If
        Next
    Next
    MsgBox dat(0) & " " & dat(1) & " " & dat(2) _
            & " " & dat(3) & " " & dat(4) _
            & " " & dat(5) & " " & dat(6) & " " _
            & dat(7) & " " & dat(8) & " " & dat(9)
End Sub

'配列要素の特定インデックスへデ－タを挿入する
Sub ArraySample_10()
    Dim dat As Variant
    Dim i As Long
    'サンプル文字を(最後に空白を入れて)Array関数で一括代入
    dat = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "")
    '4番目(0から始まるので)に文字(+)を追加
    dat(3) = "＋"
    '変更状況を表示
    MsgBox dat(0) & " " & dat(1) & " " & dat(2) & " " _
        & dat(3) & " " & dat(4) & " " & dat(5) & " " _
        & dat(6) & " " & dat(7) & " " & dat(8) & " " _
        & dat(9) & " " & dat(10)
End Sub

'配列内の途中にあるブランクデータを詰める
Sub ArraySample_11()
    Dim dat As Variant
    Dim i As Long, j As Long
    'サンプル文字を(6番目に空白を入れて)Array関数で一括代入
    dat = Array("A", "B", "C", "D", "E", "", "F", "G", "H", "I", "J")
    '空白を探して詰める処理
    For i = 0 To 9
        If dat(i) = "" Then
            For j = i To 9
                dat(j) = dat(j + 1)
            Next
        End If
    Next
    '変更状況を表示
    MsgBox dat(0) & " " & dat(1) & " " & dat(2) & " " _
        & dat(3) & " " & dat(4) & " " & dat(5) & " " _
        & dat(6) & " " & dat(7) & " " & dat(8) & " " & dat(9)
End Sub

'配列内データをセルに1行5列に記入例
Sub ArraySample_12()
    Dim dat As Variant
    Dim i As Long, r As Long, c As Long
    dat = Array("A", "B", "C", "D", "E", _
                "F", "G", "H", "I", "J", _
                "0", "1", "2", "3", "4", _
                "5", "6", "7", "8", "9", _
                "A", "B", "C", "D", "E", _
                "F", "G", "H", "I", "J")
    '列は「Mod」割り算の余り、行は「\」割り算の商
    For i = 1 To 30
        c = (i - 1) Mod 5 + 1
        r = (i - 1) \ 5 + 1
        Cells(r, c) = dat(i - 1)
    Next
End Sub