PART 11 — セル操作の高速化
Range vs 配列一括読み書き・Calculation制御

' NG: 1万行 × 1列 = 1万回のセルアクセス
Sub SlowExample()
    Dim i As Long
    For i = 1 To 10000
        Cells(i, 1).Value = i * 2   ' 毎回COM境界をまたぐ
    Next i
End Sub

Option Explicit

Sub BulkReadExample()
    Dim ws  As Worksheet
    Dim arr As Variant

    Set ws = ThisWorkbook.Sheets("データ")

    ' Range.Value でそのまま2次元配列に転送（1回のCOM呼び出し）
    arr = ws.Range("A1:C10000").Value

    ' arr(行, 列) でアクセス（1始まり）
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        ' ── VBA 側だけで処理（COM境界をまたがない）──
        If arr(i, 2) > 100 Then
            arr(i, 3) = arr(i, 2) * 1.1
        End If
    Next i

    ' 処理結果を一括書き戻し
    ws.Range("A1:C10000").Value = arr
End Sub

Sub BulkWriteExample()
    Dim rows As Long: rows = 10000
    Dim cols As Long: cols = 3

    ' 2次元配列をメモリ上で組み立て
    Dim arr(1 To 10000, 1 To 3) As Variant
    Dim i As Long
    For i = 1 To rows
        arr(i, 1) = i
        arr(i, 2) = i * 100
        arr(i, 3) = "データ" & i
    Next i

    ' 一括書き込み（1回のCOM呼び出し）
    ThisWorkbook.Sheets("出力").Range("A1").Resize(rows, cols).Value = arr
End Sub

Option Explicit

Sub FastProcessTemplate()
    ' ── 高速化設定 ON ──
    Application.ScreenUpdating = False
    Application.Calculation    = xlCalculationManual
    Application.EnableEvents   = False

    On Error GoTo Cleanup

    ' ── メイン処理 ──
    Dim arr As Variant
    arr = ThisWorkbook.Sheets("Sheet1").Range("A1:E10000").Value

    Dim i As Long
    For i = 1 To UBound(arr, 1)
        ' ... 配列内で処理 ...
    Next i

    ThisWorkbook.Sheets("Sheet1").Range("A1:E10000").Value = arr

Cleanup:
    ' ── 高速化設定を必ず戻す ──
    Application.ScreenUpdating = True
    Application.Calculation    = xlCalculationAutomatic
    Application.EnableEvents   = True

    If Err.Number <> 0 Then
        MsgBox "エラー: " & Err.Description, vbCritical
    End If
End Sub