今回はExcelに埋め込まれた数式の計算の手動計算・自動計算・再計算を設定する方法をご説明します。
VBAに限らず、大量の数式が使われているExcelを使用すると再計算で「応答なし」になると思います。
この再計算による「応答」なしをVBAの処理中に回避するために、一時的に自動計算を停止させることで処理速度の改善をする事ができます。
処理速度の検証も行ってみましたので、ご覧ください。
その他の高速につながる方法については下記記事をご覧ください。
1.再計算を停止する方法
実際には最後の1回計算が実行されたら大丈夫、という処理は「Application.Calculation = xlCalculationManual」で適宜自動計算をとめる事で、無駄な再計算を回避出来ます。
自動計算に戻す場合は「Application.Calculation = xlCalculationAutomatic」と記述します。
手動計算にする場合は次のように記述します。
Application.Calculation = xlCalculationManual '自動計算停止(手動計算)
'処理
Application.Calculation = xlCalculationAutomatic '自動計算開始注意として、「Application.Calculation = xlCalculationManual」はExcelの「計算方法の設定」を変えてしまうため、プロシージャを抜けても自動には戻りません。
つまり戻さない場合は、ほかのExcelを使った時も手動計算のままという事です。
ほかの人に配布するツールとなると、尚更、手動になっている事に気付かずに計算ミスを起こす原因になります。
これはかなり重要です、必ず自動計算に戻しましょう。
2.再計算が実行されるタイミングとは
そもそもどんな時に再計算が実行されるのでしょうか。
一覧にまとめてみましたので、ご覧ください。
再計算は、以下の処理がトリガーとなり発生します。
- 新しいデータを入力
- メニューから再計算をする
- 行列の追加/削除をする
- 行の表示/非表示を切り替える
- ファイルを保存する
- オートフィルタを実行する
- ワークシート名を変更する
- 別ワークシートとの相対位置が変更される。
これで全てではないかもしれませんが、これらの処理が実行される度に、再計算が発生します。
「新しいデータを入力」という時点で、大抵の場合は再計算されることになるかと思います。
3.再計算を実行する方法
手動計算に設定していて、途中で計算された結果を使用したいなど、再計算を実行したい場合があります。
その時は「Application.Calculate」と記述する事で、再計算が実行されます。
自動計算に設定したわけではないので、実行後も「Application.Calculate」で再計算を実行しないと計算されません。
手動計算→再計算→自動計算といった流れでコードを記述する事をおすすめします。
Application.Calculation = xlCalculationManual'手動計算
'計算の処理
Application.Calculate'再計算実行
'処理継続
Application.Calculation = xlCalculationAutomatic'自動計算4.自動計算を停止する効果
自動計算を停止させる事で、どれくらいの速度改善になるか検証します。
もともと数式が大量に埋め込まれているExcelを操作するというケースにおいて改善が出来るだけですので、自動計算を停止する事ですべてのコードが速度改善される訳ではありません。
次のコードは検索値A列に2,000行とB列にVlookup関数が入力されていて、参照データDE列に2,000行あるデータあります。
こちらをE列のデータを変更します。
自動計算のまま処理
Sub Sample1()
Dim i As Long
For i = 2 To 2001
Cells(i, 5) = Cells(i, 5) * 2
Next i
End Sub「269.007秒」でした。
たった2000行でもかなり時間がかかりました。
このコードを自動計算を停止して処理してみます。
自動計算を停止して処理
Sub Sample2()
Dim i As Long
Application.Calculation = xlCalculationManual
For i = 2 To 2001
Cells(i, 5) = Cells(i, 5) * 2
Next i
Application.Calculation = xlCalculationAutomatic
End Subこちらは「0.515秒」でした、かなり早くなりました。
数式、特に処理の重たいVLOOKUPやCOUNTIFなどの関数が大量に埋め込まれているExcelを操作する場合は、自動計算を停止させる事非常に速度改善できました。
