【ExcelVBA】処理が遅い場合の対処法（高速化／速度改善）

Sub Function1()
    
    ' 描画停止
    Application.ScreenUpdating = False
    
    'ここで重い処理を行う
    '...
    
    ' 描画再開
    Application.ScreenUpdating = True
    
End Sub

Sub Function2()
    
    '手動再計算
    Application.Calculation = xlCalculationManual
    
    'ここで重い処理を行う
    '...
    
    '自動再計算
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Sub Function3()
    
    Dim data1 As String
    
    Dim row_start As Integer: row_start = 1
    Dim row_end As Integer: row_end = 10000
    Dim col_start As Integer: col_start = 1
    Dim col_end As Integer: col_end = 10
    Dim col_offset As Integer: col_offset = 100
    
    '開始時間計測
    startTime = Timer
    
    '１つずつコピー
    For row_index = row_start To row_end
        For col_index = col_start To col_end
            '取得
            data1 = Cells(row_index, col_index).Value
            '設定
            Cells(row_index, col_index + col_offset).Value = data1
        Next
    Next
    
    '終了時間計測
    endTime = Timer
    processTime = endTime - startTime
    Debug.Print ("経過時間[" & processTime & "]秒")
    
End Sub

Sub Function4()
    
    Dim data1 As Variant
    
    Dim row_start As Integer: row_start = 1
    Dim row_end As Integer: row_end = 10000
    Dim col_start As Integer: col_start = 1
    Dim col_end As Integer: col_end = 10
    Dim col_offset As Integer: col_offset = 100
    
    '開始時間計測
    startTime = Timer
    
    '一括コピー
        '取得
        data1 = Range(Cells(row_start, col_start), Cells(row_end, col_end)).Value
        '設定
        Range(Cells(row_start, col_start + col_offset), Cells(row_end, col_end + col_offset)).Value = data1
    
    '終了時間計測
    endTime = Timer
    processTime = endTime - startTime
    Debug.Print ("経過時間[" & processTime & "]秒")
    
End Sub