数式がたくさん入ったシートにVBAでセルの値を大量に書き込むと、1回書き込むたびにExcelが再計算を行うため、処理がどんどん遅くなっていきます。この再計算のタイミングをVBAからコントロールできるのがApplication.Calculationプロパティです。
この記事では、Application.Calculationによる計算モードの切り替え方法から、Worksheet.Calculate・Range.Calculateの使い分け、計算モードを安全に元に戻すための書き方まで、実務でそのまま使えるコードとあわせて解説します。
Application.Calculationとは
Application.Calculationは、Excel全体の再計算モードを取得・設定するプロパティです。通常Excelは「自動計算」モードになっており、セルの値が変わるたびに関連する数式がすべて再計算されます。
数式の数が多いシートに対してVBAでループ処理を行うと、ループの1回ごとに再計算が走ってしまい、処理全体が非常に重くなります。そこで、処理中だけ一時的に「手動計算」に切り替えることで、大幅な高速化が期待できます。
3つの計算モード
Application.Calculationには、以下の3つの定数（XlCalculation列挙型）を指定できます。
| 定数 | 内容 |
|---|---|
| xlCalculationAutomatic | 自動計算（既定値）。セルが変更されるたびに再計算する |
| xlCalculationManual | 手動計算。F9キーやコードで明示的に指示するまで再計算しない |
| xlCalculationSemiautomatic | データテーブル以外は自動計算する |
Sub CheckCalculationMode()
Debug.Print Application.Calculation
' -4105 = xlCalculationAutomatic
' -4135 = xlCalculationManual
' 2 = xlCalculationSemiautomatic
End Sub
実践コード：処理中だけ手動計算に切り替える
大量のセルに値を書き込む処理の前後で計算モードを切り替える基本パターンです。処理の途中でエラーが発生しても計算モードが手動のままにならないよう、On Errorで復元処理を必ず通すようにしています。
Sub WriteDataWithManualCalculation()
Dim originalCalcMode As XlCalculation
Dim i As Long
originalCalcMode = Application.Calculation
On Error GoTo ErrorHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To 100000
Cells(i, 1).Value = i * 2
Next i
CleanExit:
Application.Calculation = originalCalcMode
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "エラーが発生しました: " & Err.Description
Resume CleanExit
End Sub
処理の最初にoriginalCalcModeへ元の設定を退避しておき、処理が正常終了してもエラーで中断しても、必ずCleanExitを経由して元のモードに戻す構成にしています。これを忘れると、マクロ終了後もブックが手動計算のままになり、ユーザーが数式の値が更新されないトラブルに気づきにくくなるので注意が必要です。
手動計算中に必要な範囲だけ再計算する
手動計算モードの間でも、Calculateメソッドを使えば任意のタイミング・任意の範囲だけを再計算できます。
Worksheet.Calculate：シート単位で再計算
Sub RecalcSpecificSheet()
ThisWorkbook.Worksheets("集計").Calculate
End Sub
ブック全体ではなく特定のシートだけを再計算したい場合に使います。他のシートに重い数式が大量にあっても影響を受けません。
Range.Calculate：セル範囲単位で再計算
Sub RecalcSpecificRange()
ThisWorkbook.Worksheets("集計").Range("A1:A100").Calculate
End Sub
シートの中でも一部の範囲だけ再計算したい場合に使います。書き込んだセルに関連する数式だけをピンポイントで更新したいときに便利です。
Application.Calculate：ブック全体を再計算
Sub RecalcWholeWorkbook()
Application.Calculate
End Sub
Application.Calculateは、変更があったと認識されているセル（ダーティフラグが立っているセル）とその依存先だけを再計算します。通常はこのメソッドで十分ですが、まれに数式の依存関係が正しく認識されず、値が更新されないことがあります。
Application.CalculateFullとCalculateFullRebuild
依存関係が正しく更新されない場合は、以下の2つのメソッドを検討します。
' ブック内のすべての数式を強制的に再計算する
Application.CalculateFull
' 依存関係ツリーを再構築してから、すべての数式を再計算する
Application.CalculateFullRebuild
CalculateFullは変更フラグに関係なくすべての数式を再計算し、CalculateFullRebuildはさらに依存関係の構造そのものを作り直してから再計算します。処理は重くなりますが、「値を変えたのにセルが更新されない」という不具合の切り分けに役立ちます。
まとめ
今回は、Application.Calculationを使った計算モードの切り替えと、Calculate系メソッドの使い分けを解説しました。
Application.Calculationで自動計算・手動計算を切り替えられる- 大量のセルを書き換える処理の前後で手動計算に切り替えると高速化できる
- 計算モードは
On Errorと組み合わせて必ず元の状態に戻す - 再計算のタイミングは
Worksheet.Calculate・Range.Calculate・Application.Calculateで細かく制御できる - 依存関係の不具合が疑われる場合は
CalculateFull・CalculateFullRebuildで切り分ける
数式が多い重いシートを扱うマクロでは、計算モードのコントロールが処理速度を大きく左右します。ぜひ実務のマクロにも組み込んでみてください。
コメント