自動計算を停止させる Excel(エクセル)マクロ(VBA)

マクロを非常に重くしてしまう要因の一つに、エクセルシートに大量の計算式が入っていることがあります。その場合は自動計算を停止させることで一気にスピードアップさせることができる場合があります。

まずはその方法をご紹介いたします。

自動計算停止・自動計算再開コード

Application.Calculation = xlCalculationManual’自動計算を停止し、手動計算に変更する
～処理～
Application.Calculation = xlCalculationAutomatic’自動計算に戻す

「Application.Calculation = xlCalculationManual」は自動計算を手動計算に変更しています。エクセルの画面ではツールバーの数式タブで下の画像のように計算方法に手動に切り替えているのと同じことをしています。

「Application.Calculation = xlCalculationAutomatic」は手動計算を自動計算に変更しています。エクセルの画面ではツールバーの数式タブで下の画像のように計算方法に手動に切り替えているのと同じことをしています。

使用例

時間のかかるコードの例

マクロの処理時間を計るでも使用したエクセルシートを使ってみます。

A列には適当な文字を10,000行まで埋めて、B列には適当な数字を入れておきます（後でマクロで書き換えます）。C列には1,000という数字を10,000行まで埋めておきます。

そしてD列にはB列/C列の式を入れておきます。

次に時間を計測できるコードを書きます。

こちらはマクロの処理時間を計るでも使用したコードです。

Sub 実行()

    Dim starttime As Double: starttime = Timer '時間計測開始
    
    Call 処理1
    
    Dim myspeed As Double: myspeed = Timer - starttime '時間計測経過1
    myspeed = WorksheetFunction.Round(myspeed, 3)
    
    Debug.Print "処理1の時間は" & myspeed & "秒です"
    
    starttime = Timer '時間をリセットする
    
    Call 処理2
    
    myspeed = Timer - starttime '時間計測経過2
    myspeed = WorksheetFunction.Round(myspeed, 3)
    Debug.Print "処理2の時間は" & myspeed & "秒です"
    
End Sub

Sub 処理1()
    Dim s As Long: s = 3
    Dim i As Long: i = 1
    Do Until Cells(i, 1) = ""
        Cells(i, 2) = s
        s = s + 2
        i = i + 1
    Loop
End Sub

Sub 処理2()
    Dim s As Long: s = 3
    Dim i As Long
    i = 1
    Do Until i = 100
        Cells(i, 2) = s
        s = s + 2
        i = i + 1
    Loop
End Sub

処理1はB列に数字を入れていくという単純なものです。

処理2は処理1の処理を100行だけ行うというものです。

さっそく実行してみましょう

するとイミディエイトウィンドウにこのような結果が表示されました。

※イミディエイトウィンドウについてはイミディエイトウィンドウの表示方法のブログを参照してください。

処理1は処理2の100倍近いボリュームになっているのがわかります。

自動計算停止コードを挟む

Sub 処理1()
    Dim s As Long: s = 3
    Dim i As Long: i = 1
    Application.Calculation = xlCalculationManual '自動計算停止
        Do Until Cells(i, 1) = ""
            Cells(i, 2) = s
            s = s + 2
            i = i + 1
        Loop
    Application.Calculation = xlCalculationAutomatic '自動計算再開
End Sub

処理1の中に自動計算停止コードを挟みました。これでもう一度実行してみます。

すると処理1の時間は約1/6になりました。

手動計算中に一度だけ再計算

ブック全体の再計算

Application.Calculation = xlCalculationManual’自動計算を停止し、手動計算に変更する

～処理～

Application.Calculate '自動計算を停止したまま手動計算を一旦実行する

～処理～

Application.Calculation = xlCalculationAutomatic’自動計算に戻す

「Application.Calculate」は計算方法を手動に変更した後、一度だけ再計算を実行します。エクセルの画面ではツールバーの数式タブで下の画像のように「再計算実行」ボタンを押すのと同じことです。

一部だけ再計算

ブックの別シートや、同じシート内で今回の処理とは関係ない別の計算式が動作を重くしている原因になっている場合、ブック全体ではなく範囲を限定したセルの中だけ再計算を実行することもできます。

Application.Calculation = xlCalculationManual’自動計算を停止し、手動計算に変更する

～処理～

Worksheets("Sheet1").Calculate’Sheet1のみ再計算を実行


Range("A1:C3").Calculate’「A1」セルから「C3」セルの範囲を再計算を実行

～処理～

Application.Calculation = xlCalculationAutomatic’自動計算に戻す

これらの方法を活用して、エクセルの関数によって重くなっているマクロの高速化にお役立てください。