今回はマクロ・VBAの高速化について説明します。
皆さんは作成したマクロ・VBAの処理速度が遅くなったことはないでしょうか。
データ数やデータ容量が多ければ大きいほど処理速度は遅くなります。
データが逐一入ってくるシステムだと追いつかないことも…
そこで高速化のスキルが必要になるのですが
様々な手法があるため数回に分けて説明します。
それでは見ていきましょう。
■時間がない人用コピーリスト
・使用方法
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 LongTime1 = Timer
””計算開始
For A = 1 To 100000 Step 1Cells(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 ”イベントをOFFDim Time1 As Double, Time2 As Double, Result As Double
Dim A As LongTime1 = Timer
””計算開始
For A = 1 To 100000 Step 1Cells(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 ”イベントをONEnd Sub
ちなみにすべての処理が終わったらOFFにした項目はONに戻しましょう。
Application.ScreenUpdating = True ”画面更新をON
Application.Calculation = xlAutomatic ”自動計算をON
Application.EnableEvents = True ”イベントをON
では実行してみましょう。
結果が出ました！
3.62秒から2.20秒と1.42秒短縮しました。
結果的に39%短縮短縮です。
⬛︎公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
Application.ScreenUpdating プロパティ (Excel) | Microsoft Learn
Application.Calculation プロパティ (Excel) | Microsoft Learn
Application.EnableEvents プロパティ (Excel) | Microsoft Learn
■まとめ
いかがだったでしょうか。
私も大容量の処理には必ず入れているテンプレートです。
ぜひ高速化に役立ててみてください。
次回の高速化の記事はこちらから☟
【VBA知識 高速化②】あまり知られていない？大量データで処理が遅いVBAを高速化 – 独学エクセル塾 (dokugakuexcel.com)
それでは次回の記事でお会いしましょう。