Excel VBAの再計算設定

ExcelのVBAで大量のデータを処理する場合など、再計算の処理をコードで制御することで大幅な高速化につながる。

1.Calculation、ScreenUpdating
最初と最後に

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
～処理～
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

のコードを入れる。
1行目はExcelの再計算の設定を"手動"にし、Calculateメソッドを実行しない限り再計算を行わない、というものである。
2行目は再計算ではないが、画面の再描画をOFFにするもので、Macroの処理が終わるまでステータスバー等以外の画面が更新されない。
これらはサイトや本などで割とよく見る手法である。

以下、これの更に応用例を挙げる。


2.他のプロシージャからコールする、又は他のBookから実行する場合

Dim c As Integer
 c = Application.Calculation
Dim a As Boolean
 a = Application.ScreenUpdating

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

～処理～

Application.Calculation = c

Application.ScreenUpdating = a

とする。
他のプロシージャ、例えばControl()の中で

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Call Task()

～処理～

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

のようにTaskプロシージャをコールしている場合、Task（）の方にも同様の記述がしてあると、Taskプロシージャが完了した時点で再計算、再描画の設定が自動、ONになってしまい、その後のControlプロシージャの処理が全て自動再計算/自動再描画の状態で行われてしまう。

これを避けるためにTask()のコードに手動再計算の処理を入れない、あるいは終了時に自動再計算に戻す処理を入れない、ということをすると、今度はTaskプロシージャを単独で動かすときに弊害が生じる。

これは、
・まず最初に現在の再計算/再描画の設定を変数に取得する
・終了時はプロシージャ開始時の再計算/再描画設定に戻す
ことで解決できる。


3.特定の範囲のみを再計算する(1)
再計算の必要な箇所が特定の1シート、あるいは特定のRangeに限られるという場合はCalculateに代えて以下を使う。
Worksheets("シート名").Calculate
あるいは
[再計算範囲].Calculate

普段使われるCalculateは、より丁寧に記述すると実はApplication.Calculateであり、対象のApplication全体（通常は開いているブック全て）を再計算の対象とする。
再計算の範囲は他にも「特定のワークシート」「指定されたセル範囲」があり、必要な箇所のみ再計算を行うようにするとより効率がいい。

ただし、再計算の必要な箇所が複数シートに渡っている場合、複数シートを対象とする再計算のメソッドはどうやら無いようである。また、特定のブックのみを再計算するメソッドも無いようである。
こういう時には、次の方法を使う。

4.特定の範囲のみを再計算する(2)
以下のように、EnableCalculationプロパティを設定する。
Worksheets("シート名").EnableCalculation = False

上の3.で書いたように、複数シートあるいは特定のブックを対象とする再計算が必要な場合、素直には達成できない。仮に複数シートを1シートずつ順番に再計算していくと、後で再計算を行ったシートの結果を先に再計算を行ったシートが参照している場合、誤った計算結果を返してしまう。

これを防ぐため、発想を逆にして、「再計算の必要なシート以外は手動でも再計算しない」という、より強い設定を行う。
具体的には、ワークシートのプロパティにEnableCalculationというのがあるので、これをFalseに設定する。
これを行うと、自動だろうが手動だろうが、Calculateメソッドではそのワークシートは再計算されない。
再計算時に不要なシートを全てEnableCalculation = Falseに設定した上で、Application.Calculateで全体を再計算すれば、シートの計算順序による間違いも起こらず、計算速度を保つことができる。

特定のブックのみ再計算させたい場合も同じである。その他の開いているブックについて、全てのシートを再計算不可にしてしまえばいい。
こういうコードが役に立つと思う。

Dim ws As Worksheet
For Each ws In Workbooks("ブック名").Worksheets
 ws.EnableCalculation = False
Next ws