ねらい：Excel VBAの「遅い原因」を素早く特定して、的確に直す
遅さの犯人は、ほぼ「セルへの往復」「描画・再計算」「イベント」「型変換」「文字列連結」のどれかです。感覚ではなく、計測→切り分け→A/B比較で原因を特定しましょう。初心者向けに、その場で貼って使える計測テンプレと、よくあるボトルネックの見つけ方を深掘りして解説します。
計測テンプレート：どこが遅いかを数値で掴む
区間計測（Timerでミリ秒級の測定）
Sub MeasureSections()
Dim t As Double
t = Timer
Call ReadData ' 例：入力読み込み
Debug.Print "ReadData:", Format(Timer - t, "0.000"), "s"
t = Timer
Call ProcessData ' 例：配列処理
Debug.Print "ProcessData:", Format(Timer - t, "0.000"), "s"
t = Timer
Call WriteData ' 例：出力書き戻し
Debug.Print "WriteData:", Format(Timer - t, "0.000"), "s"
End Sub
- 重要ポイント（深掘り）：
- 区間ごとに時間を出すと「読み」「処理」「書き戻し」のどこが遅いかが一目で分かる。
- Debug.Printで即時ウィンドウに表示。継続運用ならログシートへ記録も有効。
反復ベンチ（同じ処理を回して分散を見る）
Sub Benchmark(ByVal runner As String, ByVal rounds As Long)
Dim i As Long, t0 As Double, t1 As Double, sum As Double
For i = 1 To rounds
t0 = Timer
Application.Run runner
t1 = Timer
sum = sum + (t1 - t0)
Debug.Print runner & " round " & i & ": " & Format(t1 - t0, "0.000") & "s"
Next
Debug.Print "Avg:", Format(sum / rounds, "0.000"), "s"
End Sub
- 重要ポイント（深掘り）：
- 平均時間で“たまたま”を排除。最適化効果の有無が明確になる。
Application.RunならSub名を渡して汎用に使える。
よくあるボトルネックの特定と対策（深掘り）
セルへの往復（1セルずつの読み書き）
- 症状: ループが終わらない／CPUが高止まり。
- 見つけ方: セル参照を数える（
Cells(r, c).Valueがループ内に多用）。 - 対策: Range→配列で一括読み、配列内で処理、一括書き戻し。
' 悪い例（1セルずつ）
For r = 2 To last
Cells(r, "E").Value = Cells(r, "A").Value & Cells(r, "B").Value
Next
' 良い例（配列）
Dim arr As Variant: arr = Range("A1").CurrentRegion.Value
' ...配列処理...
Range("E1").Resize(UBound(arr,1), 1).Value = resultCol
描画と再計算（ScreenUpdating/Calculation）
- 症状: スクロールのように画面がチラつく／数式が多いブックで激遅。
- 見つけ方: 画面更新ON／計算自動のまま長処理。
- 対策: 開始時にOFF、終了時に必ずONへ戻す。
Sub AppEnter()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
End Sub
Sub AppLeave()
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
イベント多発（Change/Calculateの再帰）
- 症状: 何か書くたびに遅くなる／無限にイベントが走る。
- 見つけ方: Worksheet_ChangeやCalculateが存在、値書き込みがその中でも行われている。
- 対策: 処理中は
EnableEvents=False、フォームや進捗も必要時のみ更新。
WorksheetFunctionと型の暗黙変換
- 症状: なぜか数値処理が遅い／計算結果が不正。
- 見つけ方: Variantだらけ／
WorksheetFunction多用。 - 対策: VBA内蔵関数と正しい型（Double/Long）へ切替。型は明示宣言。
Dim total As Double ' 型を明示
Dim i As Long
For i = 1 To n
total = total + CDbl(arr(i, 1))
Next
文字列連結の多用
- 症状: 長い文字列生成で遅い。
- 見つけ方:
s = s & "..."が大ループに存在。 - 対策: 配列へ入れて最後にJoin。
Dim buf() As String: ReDim buf(1 To rows)
For r = 1 To rows
buf(r) = CStr(arr(r, 1))
Next
Dim all As String: all = Join(buf, vbCrLf)
Select/Activateの氾濫
- 症状: 画面がバタつく／余計な待ちが発生。
- 見つけ方:
.Selectや.Activateが多用されている。 - 対策: 参照は
Withやオブジェクト変数で直接指定。
' 悪い例
Range("A1").Select
Selection.Value = "x"
' 良い例
Worksheets("Sheet1").Range("A1").Value = "x"
切り分けの進め方（順番が肝）
- 最適化スイッチON/OFFで比較
- AppEnter/AppLeave有無で時間差を計測。大差が出るなら描画/再計算が犯人。
- I/Oの単位を比較（セルvs配列）
- 同じ処理をセル往復と配列でA/B比較。桁違いに差が出れば確定。
- イベント切断で比較
EnableEvents=Falseで速度が上がるならイベントが悪さ。
- 型・関数の切替で比較
- Variant→Double/Long、
WorksheetFunction→VBA内蔵へ。数値処理なら効果大。
- Variant→Double/Long、
- 進捗・DoEventsの間引き
- 更新頻度を1〜5%刻みにして比較。呼びすぎなら一気に改善。
- 重要ポイント（深掘り）：
- 「一度に全部」変えない。1要素ずつ切り替えて差分計測。原因の特定が確実になる。
- 数値で示せば、改善の説得力が上がる（運用メンバーにも共有しやすい）。
A/B比較の実例テンプレ（そのまま貼って検証）
1セルずつ vs 配列一括
Sub Case_CellByCell()
Dim ws As Worksheet: Set ws = Worksheets("Input")
Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim t As Double: t = Timer
Dim r As Long
For r = 2 To last
ws.Cells(r, "E").Value = ws.Cells(r, "A").Value & ws.Cells(r, "B").Value
Next
Debug.Print "CellByCell:", Format(Timer - t, "0.000"), "s"
End Sub
Sub Case_ArrayIO()
Dim ws As Worksheet: Set ws = Worksheets("Input")
Dim arr As Variant: arr = ws.Range("A1").CurrentRegion.Value
Dim rows As Long: rows = UBound(arr, 1)
Dim out() As Variant: ReDim out(1 To rows, 1 To 1)
Dim t As Double: t = Timer
Dim r As Long
For r = 2 To rows
out(r, 1) = CStr(arr(r, 1)) & CStr(arr(r, 2))
Next
ws.Range("E1").Resize(rows, 1).Value = out
Debug.Print "ArrayIO:", Format(Timer - t, "0.000"), "s"
End Sub
進捗更新の頻度比較
Sub Case_Progress_Frequent()
Dim total As Long: total = 50000
Dim t As Double: t = Timer
Dim i As Long
For i = 1 To total
Application.StatusBar = "進捗 " & Format(i / total, "0%")
DoEvents
Next
Debug.Print "Frequent:", Format(Timer - t, "0.000"), "s"
End Sub
Sub Case_Progress_Throttled()
Dim total As Long: total = 50000
Dim stepN As Long: stepN = Application.WorksheetFunction.Max(1, total \ 100)
Dim t As Double: t = Timer
Dim i As Long
For i = 1 To total
If i Mod stepN = 0 Then
Application.StatusBar = "進捗 " & Format(i / total, "0%")
DoEvents
End If
Next
Application.StatusBar = False
Debug.Print "Throttled:", Format(Timer - t, "0.000"), "s"
End Sub
- 重要ポイント（深掘り）：
- 目で見るのではなく秒数で比較。効果が明確なら採用判断が容易。
- 進捗は「体感の良さ」と「速度」の両立を狙い、刻み幅を調整する。
ログ・可視化で「再現性」を確保する
Sub LogTime(ByVal label As String, ByVal sec As Double)
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("PerfLog")
If ws Is Nothing Then Set ws = ThisWorkbook.Worksheets.Add: ws.Name = "PerfLog"
On Error GoTo 0
Dim r As Long: r = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(r, 1).Value = Format(Now, "yyyy-mm-dd HH:NN:SS")
ws.Cells(r, 2).Value = label
ws.Cells(r, 3).Value = sec
End Sub
- 重要ポイント（深掘り）：
- 計測値をシートに残して、変更前後で比較できるようにする。
- 時刻・ラベル・秒数の3点記録だけで十分有効。グラフ化すれば傾向も見える。
現場チェックリスト（遅さの犯人を潰す順番）
- 描画・イベント・計算を止めたか？（AppEnter/AppLeave）
- セル往復を配列へ置換したか？
- DoEvents/進捗更新を間引いたか？
- 型を明示して暗黙変換を減らしたか？
- Select/Activateを書き換えたか？
- フィルタやテーブルで対象行を絞ったか？
- チャンク処理に分割したか？（超大規模時）
- 計測値を残して効果を確認したか？
- 重要ポイント（深掘り）：
- 「やったつもり」では原因が残る。チェックリストを順に潰すと漏れがなくなる。
- 数値を残して改善を“資産化”。次のプロジェクトでも即応用できる。
スターター手順（最短導入）
- 区間計測（Timer）を入れて、遅い区間を特定。
- AppEnter/AppLeaveで描画・イベント・計算を制御。
- セル往復を配列I/Oへ置換し、A/B比較で効果確認。
- 進捗・DoEventsを1〜5%刻みに間引く。
- 型明示（Double/Long/String）とSelect排除でさらに詰める。
- PerfLogへ計測値を記録し、変更前後の差を見える化。