【マクロVBA高速化①】画面更新・自動計算・イベントのOFFで高速化！3つのApplication

今回はマクロ・VBAの高速化について説明します。
皆さんは作成したマクロ・VBAの処理速度が遅くなったことはないでしょうか。

データ数やデータ容量が多ければ大きいほど処理速度は遅くなります。
データが逐一入ってくるシステムだと追いつかないことも…

そこで高速化のスキルが必要になるのですが
様々な手法があるため数回に分けて説明します。

それでは見ていきましょう。

■時間がない人用コピーリスト
■比較用プログラムの説明
■画面更新のOFF
■自動計算をオフ
■イベントをオフ
■変更前と比較
⬛︎公式の説明
■まとめ

■時間がない人用コピーリスト

・使用方法

Application.ScreenUpdating = False ”画面更新をOFF
Application.Calculation = xlManual ”自動計算をOFF
Application.EnableEvents = False ”イベントをOFF
‘ここに高速化したいプログラム
Application.ScreenUpdating = True ”画面更新をON
Application.Calculation = xlAutomatic ”自動計算をON
Application.EnableEvents = True ”イベントをON

上記のように高速化したいプログラムに挟むように設置してください。

■比較用プログラムの説明

今回は下記のようなデータを用意しました。
A・B行に100000行、ランダムなデータがある状態です。

データの処理方法としてA1とB1の数値をかけてC1に出力する仕組みとします。
その処理を100000行繰り返すのにかかった時間を基準としましょう。

用意したプログラムがこちら。

一応、コピーできるように貼っておきます。

Sub 高速化①()
Dim Time1 As Double, Time2 As Double, Result As Double
Dim A As Long
Time1 = Timer
””計算開始
For A = 1 To 100000 Step 1
　Cells(A, 3) = Cells(A, 1) * Cells(A, 2)
Next A
””計算終わり
Time2 = Timer
Result = Time2 – Time1
MsgBox Result & “秒で処理しました”
End Sub

では実行してみましょう。

3.62秒かかりましたね。これを基準に早くしていきます。

■画面更新のOFF

まず、画面更新についてです。
マクロ・VBAでは通常、進行に合わせて過程を画面で確認できるようになっています。
要は
計算→画面出力→計算→画面出力
を100000回繰り返している流れです。

これを下記のコードでOFFにします。

Application.ScreenUpdating = False ”画面更新をOFF

これにより
計算*100000→画面出力
のように画面出力が1回になります。

出力回数が減るので高速化につながる仕組みです。

■自動計算をオフ

次に自動計算についてです。
エクセルはセルを参照して計算していると元の数値を変えると参照先も変わりますよね。

こちらも
入力→再計算→入力→再計算
のように繰り返してしまいます。

なので実行中、セルの参照がない場合はOFFにしましょう。
下記のコードでOFFにします。

Application.Calculation = xlManual ”自動計算をオフ

これにより
計算*100000→まとめて再計算
のようになります。

■イベントをオフ

最後はイベントのオフです
ここで言われるイベントとはクリックや内容更新、セルのアクティブにより発生するイベントをOFFにするものです。

こちらは下記のコードでOFFにします。

Application.EnableEvents = False ”イベントをOFF

■変更前と比較

では実際にプログラムに入れてみます。

このようになりました。
またまたコピー用載せておきます。

Sub 高速化①()
Application.ScreenUpdating = False ”画面更新をOFF
Application.Calculation = xlManual ”自動計算をOF
Application.EnableEvents = False ”イベントをOFF
Dim Time1 As Double, Time2 As Double, Result As Double
Dim A As Long
Time1 = Timer
””計算開始
For A = 1 To 100000 Step 1
　　Cells(A, 3) = Cells(A, 1) * Cells(A, 2)
Next A
””計算終わり
Time2 = Timer
Result = Time2 – Time1
MsgBox Result & “秒で処理しました”
Application.ScreenUpdating = True ”画面更新をON
Application.Calculation = xlAutomatic ”自動計算をON
Application.EnableEvents = True ”イベントをON
End Sub　　