EXCEL VBA 高速化　描画・監視・自動計算停止・（画面・更新・停止）　（テクニック）

2020年8月12日

EXCEL VBA 高速化　　（画面・更新・停止）

●はじめに

ここでは、EXCEL VBAの高速化に関する説明をしたいと思います。VBAコード（プログラム）は、長くなったり、複雑になったり、特にループ処理が多くなると、非常に遅くなります。ここでは、２パターン高速化する方法を説明します。

EXCEL VBA高速化の説明①

●プログラム説明

●下記のプログラムは、VBAを高速する手法の中で、プログラム作成の際に、取り入れた方が良いでしょう。実際どのように事をしているのか順番に解説します。
① 　ScreenUpdating = False ‘描画停止
②　EnableEvents = False ‘イベント監視停止
③　Calculation = xlCalculationManual ‘手動計算
●EXCELでは、常に描画作成・イベント監視・自動計算を行っています。この機能をプログラムに記述する事で、３つの機能を一時的に止める事でき、全体的な処理スピードを高める事ができます。

Sub kosoku_Rei()
　　
　　Dim I as Long

    With Application   '高速化はじめ
        .ScreenUpdating = False                 '描画停止
        .EnableEvents = False                   'イベント監視停止
        .Calculation = xlCalculationManual      '手動計算
    End With

　　'------------実行処理を行うコードを書く(高速化のコードで囲む）---------
    Dim　I = 1 to 100000
         Cells("A",I) = I
    Next I
    '---------------------------------------------------------------

    With Application       '高速化おわり
        .ScreenUpdating = True                 '描画再開
        .EnableEvents = True                   'イベント監視再開
        .Calculation = xlCalculationAutomatic  '自動計算
    End With

End Sub

Sub kosoku_Rei()

　　Dim I as Long

With Application '高速化はじめ

.ScreenUpdating = False '描画停止

.EnableEvents = False 'イベント監視停止

.Calculation = xlCalculationManual '手動計算

End With

　　'------------実行処理を行うコードを書く(高速化のコードで囲む）---------

Dim　I = 1 to 100000

Cells("A",I) = I

Next I

'---------------------------------------------------------------

With Application '高速化おわり

.ScreenUpdating = True '描画再開

.EnableEvents = True 'イベント監視再開

.Calculation = xlCalculationAutomatic '自動計算

End With

End Sub

●プログラム説明　VBA高速化しない場合

下記のプログラムでは、描画作成・イベント監視・自動計算のいづれも停止せずに実行するプログラムです。

Sub hikakuTEST()

Dim I As Long
Dim TIME_S, TIME_E, TIME_G As Date

    TIME_S = Now()                  '処理の実行前に時間を代入

        For I = 1 To 1000000
            Cells(I, "A") = I
        Next I
        
    TIME_E = Now()                  '処理の終了後に時間を代入
    
    TIME_G = TIME_E - TIME_S        '終了後-実行前＝差の時間を算出
    
    MsgBox TIME_G                   '差の時間を表示する（処理時間）
    
End Sub

Sub hikakuTEST()

Dim I As Long

Dim TIME_S, TIME_E, TIME_G As Date

TIME_S = Now() '処理の実行前に時間を代入

For I = 1 To 1000000

Cells(I, "A") = I

Next I

TIME_E = Now() '処理の終了後に時間を代入

TIME_G = TIME_E - TIME_S '終了後-実行前＝差の時間を算出

MsgBox TIME_G '差の時間を表示する（処理時間）

End Sub

●実行結果

上記のプログラムは、描画作成・イベント監視・自動計算を一時停止せずに、実行した結果になります。

●実行後　※上記のプログラムを実行した結果、３５秒掛りました。　（クリックして拡大）

●プログラム説明　VBA高速化した場合

下記のプログラムでは、描画作成・イベント監視・自動計算を一時的に停止させて、プログラムを実行した場合です。

Sub hikakuTEST2()

Dim I As Long
Dim TIME_S, TIME_E, TIME_G As Date


    With Application   '高速化はじめ
        .ScreenUpdating = False                 '描画停止
        .EnableEvents = False                   'イベント監視停止
        .Calculation = xlCalculationManual      '手動計算
    End With
    
    TIME_S = Now()                  '処理の実行前に時間を代入

        For I = 1 To 1000000
            Cells(I, "A") = I
        Next I
    
    TIME_E = Now()                  '処理の終了後に時間を代入
    
    TIME_G = TIME_E - TIME_S        '終了後-実行前＝差の時間を算出
    
    With Application       '高速化おわり
        .ScreenUpdating = True                 '描画再開
        .EnableEvents = True                   'イベント監視再開
        .Calculation = xlCalculationAutomatic  '自動計算
    End With
    
    MsgBox TIME_G                               '差の時間を表示する（処理時間）
    
End Sub

Sub hikakuTEST2()

Dim I As Long

Dim TIME_S, TIME_E, TIME_G As Date

With Application '高速化はじめ

.ScreenUpdating = False '描画停止

.EnableEvents = False 'イベント監視停止

.Calculation = xlCalculationManual '手動計算

End With

TIME_S = Now() '処理の実行前に時間を代入

For I = 1 To 1000000

Cells(I, "A") = I

Next I

TIME_E = Now() '処理の終了後に時間を代入

TIME_G = TIME_E - TIME_S '終了後-実行前＝差の時間を算出

With Application '高速化おわり

.ScreenUpdating = True '描画再開

.EnableEvents = True 'イベント監視再開

.Calculation = xlCalculationAutomatic '自動計算

End With

MsgBox TIME_G '差の時間を表示する（処理時間）

End Sub

●実行結果

上記のプログラムは、描画作成・イベント監視・自動計算を一時停止させて、プログラムを実行した結果になります。
（上記の手順を行う事により、全体的に処理スピードを上げる事ができます）

●実行前　※上記のプログラムを実行した結果、１７秒掛りました。　（クリックして拡大）
描画作成・イベント監視・自動計算を一時停止せずに、実行した結果　35秒
描画作成・イベント監視・自動計算を一時停止して、実行した結果　　17秒一時停止した場合は、18秒早く処理する事ができました。

最後まで、ご覧いただきまして誠に有難うございました。
以上で、EXCEL　VBA高速テクニックでした。また、その他高速化に繋がる方法は、プログラムの内部を見直す、無駄なループ処理を減らすなど様々ありますが、今回説明した部分をプログラムに追加するだけでも、しない場合とくらべても、早く実行してくれます。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

VBAプログラム（テクニック）EXCEL, VBA