VBAのReDimで動的配列のサイズを変更する方法をわかりやすく解説

Sub BasicReDimExample()
    Dim numbers() As Integer ' 動的配列の宣言
    
    ReDim numbers(1 To 5) ' 実行時にサイズを決定
    
    ' 配列にデータを代入
    Dim i As Integer
    For i = 1 To 5
        numbers(i) = i * 10
    Next i
    
    ' 結果を表示
    For i = 1 To 5
        Debug.Print numbers(i)
    Next i
End Sub

Sub PreserveExample()
    Dim numbers() As Integer
    Dim i As Integer
    
    ' 最初のサイズを設定
    ReDim numbers(1 To 3)
    For i = 1 To 3
        numbers(i) = i * 10
    Next i
    
    ' サイズを拡張（既存データを保持）
    ReDim Preserve numbers(1 To 5)
    numbers(4) = 40
    numbers(5) = 50
    
    ' 結果を表示
    For i = 1 To 5
        Debug.Print numbers(i)
    Next i
End Sub

Sub StoreExcelData()
    Dim arr() As String
    Dim lastRow As Integer, i As Integer
    
    ' 最終行を取得
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' 配列のサイズを可変に設定
    ReDim arr(1 To lastRow)
    
    ' A列のデータを配列に格納
    For i = 1 To lastRow
        arr(i) = Cells(i, 1).Value
    Next i
    
    ' 結果をデバッグウィンドウに表示
    For i = 1 To lastRow
        Debug.Print arr(i)
    Next i
End Sub

Sub MultiDimPreserve()
    Dim arr() As Integer
    Dim i As Integer, j As Integer
    
    ' 2次元配列を初期化
    ReDim arr(1 To 2, 1 To 3)
    
    ' データを格納
    For i = 1 To 2
        For j = 1 To 3
            arr(i, j) = i * j
        Next j
    Next i
    
    ' 最後の次元のみ変更可能
    ReDim Preserve arr(1 To 2, 1 To 5)
End Sub

Sub SafeReDim()
    Dim arr() As Integer
    On Error Resume Next
    
    ' 初期化
    ReDim arr(1 To 5)
    
    ' サイズ変更
    ReDim Preserve arr(1 To 10)
    
    If Err.Number <> 0 Then
        MsgBox "エラー発生: " & Err.Description, vbExclamation, "エラー"
        Err.Clear
    End If
End Sub