Excel VBAで再計算を制御する方法まとめ

VBAの処理中に無駄な再計算を発生させない様にする方法です。

再計算の発生条件についてはExcelの再計算を抑制する方法をご覧ください。

関連 Excelの再計算を抑制する方法

自動再計算を手動にする

Excel全体で再計算を手動にする

Excelの再計算設定を変更します。通常はxlCalculationAutomaticとxlCalculationManualを切り替えて使います。

ブック単位の設定ではないので、処理が終わったらxlCalculationAutomaticに戻す様にしてください。エラーでプログラムが中断した場合の処理は忘れられがちなので注意が必要です。

' 再計算「自動」
Application.Calculation = xlCalculationAutomatic
' 再計算「手動」
Application.Calculation = xlCalculationManual
' 再計算「データテーブル以外自動」
Application.Calculation = xlCalculationSemiautomatic

ワークシート単位で再計算をオフにする

ワークシート単位で再計算の設定を変更できます。次の様な場合に有効です。

• アクティブシートのみ再計算させたい
• 計算の重いシートを再計算させない様にしたい

' 無効化
Worksheets("Sheet1").EnableCalculation = False
' 有効化
Worksheets("Sheet1").EnableCalculation = True

保存前の再計算設定を変える

再計算の設定で「手動」の隣にある「ブックの保存前に再計算を行う」に相当する設定です。

' 無効化
Application.CalculateBeforeSave = False
' 有効化
Application.CalculateBeforeSave = True

強制的に再計算させる

再計算を手動にした状態ではセルを変更しても数式に反映されませんから、必要な部分を再度計算させる必要があります。

Excel全体に再計算を指示する

Excelで開いているブックをすべて再計算させるには、Application.Calculateメソッドを使います。

' 最小限の再計算を行う
Application.Calculate
' すべての関数の再計算を行う
Application.CalculateFull

Application.CalculateとApplication.CalculateFullの違い

Application.Calculateは揮発性関数と、依存関係にあるセルに変更があったもののみ再計算しますが、Application.CalculateFullはすべての関数を再計算します。通常はApplication.Calculateを使ってください。

詳しくはExcelの再計算を抑制する方法の内容を確認してください。

' 再計算設定を「自動」に戻す
Application.Calculation = xlCalculationAutomatic

比較のために次の様なユーザー定義関数を用意して、ワークシートに入力します。

' 非揮発性関数
Public Function Hoge()
    MsgBox "Hoge"
End Function

' 揮発性関数
Public Function HogeVolatile()
    Application.Volatile
    MsgBox "Hoge Volatile"
End Function

Application.Calculateだと揮発性関数であるHogeVolatileだけが実行されますが、Application.CalculateFullだと非揮発性関数であるHogeも再計算されます。

再計算設定を自動に戻してもOK

ちょっと趣旨が違う気がしますが、再計算が必要になった関数と揮発性関数は、再計算を自動に戻せば再計算されます(Application.Calculateと同等の動作)。上記の方法で明示的に再計算を指示し直す必要はありません。

シート単位・セル単位で再計算をする

シートやRangeオブジェクトに対して再計算を指示することもできます。Application.Calculateと同じで、再計算が必要な部分だけが再計算されます。Application.CalculateFullに相当するものはありません。

ブック単位で再計算をする方法はないようです。

' Sheet1を再計算
Worksheets("Sheet1").Calculate
' A1:C5の範囲を再計算
Range("A1:C5").Calculate

セルに再計算フラグを立てる

「このセルは再計算が必要」というフラグを立てることができます。

再計算を自動にするか、Application.Calculateなどを実行すると、このフラグに従って再計算を行います。既に再計算が自動になっている場合は、即座に再計算が実行されます。

変更があった部分に対してはExcelが自動的にフラグを立てているので、通常はRange.Dirtyを実行する必要はありません。

' A1:C3の範囲に再計算フラグを立てる
Range("A1:C3").Dirty