その他

VBAプログラム遅いと感じたら試してみよう３つの高速化！

VBA高速化

EXCEL VBAはプログラム実行時にコードを機械語に翻訳するインタプリタ型の言語のため実行速度は遅いと言われます。

確かに軽い処理であれば、速度に問題を感じることが少ないですが、重い処理、ループをして数千から数万行のセルを転記する処理や行の挿入や削除、書式設定をするなど行うと処理速度が著しく低下してVBAは遅いと感じることが多いでしょう。

何時間もかかる処理をせっかく自動化しても重い処理のためパソコンが使えないと利用者の待ち時間につながる可能性があります。

そこで、今回、VBAプログラムで遅いと感じたら覚えておきたい３つの高速化方法を紹介します。

①セルのリアルタイム更新をOFFにする

VBAでセルの値や書式設定、シートの移動など処理を行うとリアルタイムに画面が更新されます。

この更新処理、数回の更新なら瞬間で終わりますが、数千、何万行のデータを変更する場合に1回は0.1秒の処理でも1万回になれば1000秒と数十分ほどかかります。

画面更新を止めてVBAの実行が完了した時に画面の更新を行えば、その分プログラムの実行速度が高速化します。

コード例


Sub Test()
  
 '画面更新OFF
 Application.ScreenUpdating = False
 

 'VBAの処理コード...
 

 '画面更新ON
 Application.ScreenUpdating = True

End Sub

処理を始める前に False でOFFにします。

Application.ScreenUpdating = False

VBAの処理が終わったら Trueで画面更新を再開します。

Application.ScreenUpdating = True

②自動計算を止める

EXCELの表の中にたくさんの関数や計算式を使用している場合、ひとつのセルの数値を変更すると他のセルの数値が変わるものが多い場合は画面更新と同じ状況が起こり速度が著しく低下します。

Sub Test()
　
 '手動計算
 Application.Calculation = xlCalculationManual 
 

 'VBAの処理コード...
 '処理の途中でセルの値を更新したい時は
　Application.Calculate 

 
 '自動計算
 Application.Calculation = xlCalculationAutomatic  
 
End Sub

xlCalculationManual（手動）とxlCalculationManual（自動）で切り替えます。

セルの値を途中で更新したい時はApplication.Calculate

Application.Calculate

③イベントを停止する

EXCELの表で「セルの値を変更した」、「セルをクリックした」なイベントが発生した時に操作が行われるイベントプロシージャと呼ばれる関数があります。

こちらも画面更新や自動計算と同じように変更があるたびに処理が行われます。もし、イベント発生時の処理を行わないなら停止させることによって高速化が見込めます。

Sub Test()
　
 'イベント停止
　Application.EnableEvents = False


 'VBAの処理コード...
 

 'イベント再開
 Application.EnableEvents = True 
 
End Sub

False（停止）して処理が終わったら True(再開）して終わります。

さいごに

今回、３つのVBA高速化を紹介しましたが、ひとつ注意点があります。プログラムや関数の間違いや修正時は①リアルタイム更新をONにしないと処理手順が見えなくなりプログラムの間違いが見つけづらくなります。

②自動計算や③イベントをOFFにしたせいで計算が合わなくなったりする場合もありますのでデバック作業時はすべてONにした方が良いと思います。