りゅうはじめに
今回は、自動計算の停止について解説していきます。
マクロを作ったけど、完了までに長時間かかってしまう場合、もしかすると本記事のテクニックで解決できるかもしれません。
ぜひ最後までお付き合いください。
この記事を読むメリット
- ExcelVBAの高速化テクニック「自動計算の停止」方法が分かります。
- どれほどの期待効果があるのか分かります。
自動計算とは
まず、自動計算とは何かをご説明します。
自動計算は、Excel関数の処理が自動的に実行されることを指します。
具体例
例えば、以下ような表があり、Sum関数で合計しているとします。
次に、以下の画像の通り、ばななの売上を 1,500 から 2,000に変更します。
すると、Sum関数が自動的に実行され、合計値が7,500に再計算されます。
この動作が自動計算です。
この計算処理ですが、実はマクロ実行中も有効になっているので無効化を実施すると高速化につながります。
構文
自動計算を停止する方法は以下の通りです。
構文
Application.Calculation = xlCalculationManual自動計算を再開するときには以下を実行してください。
構文
Application.Calculation = xlCalculationAutomaticプログラムの冒頭で自動計算をOFFにして、最後に自動計算をONにすることをオススメします。
一般的に Excelは自動計算がONになっているものなので、自動計算がOFFのまま処理が終わってしまうと、「マクロ実行後にExcelがおかしくなってしまった」という事象につながるからです。
一般的に Excelは自動計算がONになっているものなので、自動計算がOFFのまま処理が終わってしまうと、「マクロ実行後にExcelがおかしくなってしまった」という事象につながるからです。
期待効果について
自動計算を停止すると、どれほど早くなるのかを実験してみました！
検証の前提
本テクニックで一番効果が出るのが、「計算式が埋め込まれているシートを処理する場合」です。
今回の検証では、以下のようなシート作成しました。
りゅう上記シートでは、B列のいずれかのセルの値を変更すると、C列のSUM関数の再計算が一斉に実行されます。
自動計算を実施する場合
サンプルコード
Option Explicit
Public Sub testAutoCalc()
Debug.Print "-- 自動計算あり --"
' 計測開始時刻を出力
Debug.Print Now
Dim i As Integer
For i = 2 To 10000
' 2列目(B列)の数値を一律 101に変更
ThisWorkbook.Worksheets(1).Cells(i, 2).Value = 101
Next i
' 計測終了時刻を出力
Debug.Print Now
End Sub
実行結果
以下の通り、B列のデータがすべて 101に変更されました！
C列の合計値についても更新されていることが分かります。
8分17秒もの時間がかかってしまいました。
自動計算を実施しない場合
サンプルコード
Option Explicit
Public Sub testAutoCalc()
Debug.Print "-- 自動計算なし --"
' 計測開始時刻を出力
Debug.Print Now
' 自動計算OFF
Application.Calculation = xlCalculationManual
Dim i As Integer
For i = 2 To 10000
' 2列目(B列)の数値を一律 101に変更
ThisWorkbook.Worksheets(1).Cells(i, 2).Value = 101
Next i
' 自動計算ON
Application.Calculation = xlCalculationAutomatic
' 計測終了時刻を出力
Debug.Print Now
End Sub
実行結果
1秒で処理が完了しました。
検証結果まとめ
なんと、8分17秒 → 1秒になりました！
今回は極端な例ではありますが、いかに自動計算処理が重たいかが分かりますね。
| # | 分類 | 所要時間 |
| 1 | 自動計算あり | 8分17秒 |
| 2 | 自動計算なし | 1秒 |
| 差分 | 8分16秒 | |
最後に
いかがでしたでしょうか。
私自身の経験として、一生懸命組んだのになぜかマクロが遅いことがありました。
悩みに悩んだ挙句この方法で解決した経験があります。
この記事が、読者さまのお力になれれば嬉しいです！
コメント