Excel VBA 高速化で大量のデータ処理や繰り返し作業を効率化する方法

以下のシーンで利用します。

1.大量データのコピー・貼り付け・行削除・セル値変更・書式変更 ・計算の高速化

2.ループ（繰り返し）処理の最適化（行数が多い場合の負荷軽減）

3.シートを切り替えるマクロ（別のシートに移動して値を取得 など）

4.セル・シートの更新頻度を減らし、画面のちらつきを抑える

1. Option Explicit
3. Sub FastProcessing()
4.     Application.ScreenUpdating = False ' 画面更新を停止
5.     
6.     Dim ws As Worksheet
7.     Set ws = ThisWorkbook.Sheets("Sheet1")
8.     
9.     Dim i As Long
10.     For i = 1 To 10000
11.         ws.Cells(i, 1).Value = i ' A列に1?10000を入力
12.     Next i
13.     
14.     Application.ScreenUpdating = True ' 画面更新を再開
15. End Sub

計算式が多数あるシートでのセル更新を高速化

1. Option Explicit
3. Sub SpeedUpWithCalculation()
4.     Application.ScreenUpdating = False
5.     Application.Calculation = xlCalculationManual ' 自動計算を停止
6.     
7.     Dim ws As Worksheet
8.     Set ws = ThisWorkbook.Sheets("Sheet1")
9.     
10.     Dim i As Long
11.     For i = 1 To 10000
12.         ws.Cells(i, 2).Value = i * 2 ' B列に計算結果を入力
13.     Next i
14.     
15.     Application.Calculation = xlCalculationAutomatic ' 自動計算を再開
16.     Application.ScreenUpdating = True
17. End Sub

1万行以上のデータを扱う際、ループで直接セルを操作せずに配列で処理する

1. Option Explicit
3. Sub UseArrayForSpeed()
4.     Application.ScreenUpdating = False
5.     Application.Calculation = xlCalculationManual
6.     
7.     Dim ws As Worksheet
8.     Set ws = ThisWorkbook.Sheets("Sheet1")
9.     
10.     Dim dataArr() As Variant
11.     Dim i As Long
12.     
13.     ' 1万行 × 1列分のデータを配列に格納
14.     ReDim dataArr(1 To 10000, 1 To 1)
15.     
16.     For i = 1 To 10000
17.         dataArr(i, 1) = i * 3 ' 計算処理
18.     Next i
19.     
20.     ' 一括でセルに書き込み（高速）
21.     ws.Range("A1:A10000").Value = dataArr
22.     
23.     Application.Calculation = xlCalculationAutomatic
24.     Application.ScreenUpdating = True
25. End Sub

Select や Activate を使わずにセルを直接指定して処理する（遅いコード（Select を使用））

1. Option Explicit
3. Sub SlowMethod()
4.     Dim ws As Worksheet
5.     Set ws = ThisWorkbook.Sheets("Sheet1")
6.     
7.     ws.Range("A1").Select
8.     ActiveCell.Value = "遅い方法"
9. End Sub

高速なコード

1. Option Explicit
3. Sub FastMethod()
4.     Dim ws As Worksheet
5.     Set ws = ThisWorkbook.Sheets("Sheet1")
6.     
7.     ws.Range("A1").Value = "高速な方法"
8. End Sub

同じシートやセル範囲を何度も参照する場合

1. Option Explicit
3. Sub UseObjectVariables()
4.     Application.ScreenUpdating = False
5.     Application.Calculation = xlCalculationManual
6.     
7.     Dim ws As Worksheet
8.     Set ws = ThisWorkbook.Sheets("Sheet1")
9.     
10.     Dim rng As Range
11.     Set rng = ws.Range("A1:A10000")
12.     
13.     Dim i As Long
14.     For i = 1 To 10000
15.         rng.Cells(i, 1).Value = i
16.     Next i
17.     
18.     Application.Calculation = xlCalculationAutomatic
19.     Application.ScreenUpdating = True
20. End Sub