遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法

speed

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

せっかく組んだエクセルVBAの実行速度が遅いな~って時ありませんか?

読み書きするデータ数が多かったり、ファイルにたくさんの計算式が仕込まれていたりすると、とっても遅くなってしまうことがあります。

しかも実行中って、PCが使えないわけではないのですがアプリケーションが「応答なし」になると嫌なので、結局待たなくてはなりませんよね。

そんな時にエクセルVBAのプログラムの処理速度を速くするステキなテクニックを紹介します。

また、合わせてプログラムの実行時間を測定する方法についてもお伝えしますね。

これで重いマクロともオサラバ…では早速行ってみましょう!

スポンサーリンク

今回の課題とするプログラム

処理速度を無駄に稼ぐだけのこんなプログラムを用意してみました。

Sub MySub()

    Sheet1.Cells.Clear
    Sheet2.Cells.Clear
    
    With Sheet1
        Dim i As Long
        For i = 1 To 300
            .Cells(i, 1).Value = i
            .Cells(i, 2).FormulaLocal = "=SUM(A1:A" & i & ")"
            .Rows(i).Copy
            Sheet2.Cells(i, 1).PasteSpecial
        Next i
    End With
End Sub
  • Sheet1のA列に連番の数値を書き込む
  • Sheet1のB列にA列の1行目から現在行の合計値を書き込む
  • Sheet1の現在行をSheet2にコピー&ペーストする

という、微妙に意味不明な内容です。

このプログラムを例にとって、処理速度をアップするテクニックをご紹介しつつ、その効果を試していきたいと思います。

VBAプログラムの実行時間を測定する

エクセルVBAの実行時間を測定する場合はPC内の時刻を返すTime関数を使います。

Dim start As Date: start = Time

'処理    
        
Dim finish As Date: finish = Time
MsgBox "実行時間は " & Format(finish - start, "nn分ss秒") & " でした", vbInformation + vbOKOnly

実行前の日時を取得して、実行後の日時から引き算するという古典的な方法ですね。

Format関数で分と秒による書式に変更をしています。

何も対策をしない場合の実行時間

この方法で、冒頭のプログラムの実行時間を測定してみますと

何もしない場合のマクロの実行時間

53秒でした。これはちょっと時間かかっている印象ですね。

これを設定するだけで改善できる、Applicationオブジェクトの便利なプロパティを3つほど紹介していきます。

Calculationプロパティで自動計算を停止する

エクセルってどこかのセルの値を変更したら、そのセルの値を参照してる数式があればその値も自動で変更されますよね?

エクセルVBAでは数式の自動計算のオン・オフをプログラムで切り替えることができます。

書き方としてはこちらです。

Application.Calculation = 計算モード

Applicationはエクセルのアプリケーションを表すオブジェクトと思って頂ければOKです。

計算モードには、手動計算を表す定数xlCalculationManualや、自動計算を表す定数xlCalculationAutomaticを設定します。

ですから、プロシージャの冒頭で以下のように設定しておけばマニュアル、つまり手動にするわけですから自動計算がオフになります。

Application.Calculation = xlCalculationManual

実行したい処理が完了したら

Application.Calculation = xlCalculationAutomatic

として自動計算に戻します。

自動計算を停止した場合の実行時間

プログラム冒頭で自動計算の停止をした場合の測定をしてみます。

結果は

計算モードをマニュアルにした場合のマクロの実行時間

40秒…!少しだけ速くなりましたね。

SUM関数の再計算がいちいち実行されなくなった結果だと思います。

SUMPRODUCTなど配列×配列で計算するような数式が乱用されている場合などにはもっと効果があると思います。

ScreenUpdatingプロパティで画面表示の更新を停止する

まだ遅いのでもう一つ紹介しましょう。

エクセルのいずれからのセルの変更時には、画面表示が更新されています。私たちはあまり気にしていませんが、セルが入力されたり、値が変更されたりする際に、都度画面表示が更新されているわけです。

その画面表示の更新をプログラムで止めてしまおうということです。

画面表示の更新を設定するには、ApplicationオブジェクトのScreenUpdatingプロパティを使います。

書き方としては以下の通りです。

Application.ScreenUpdating = ブール値

Falseに設定すると画面表示の更新が停止します。

Application.ScreenUpdating = False

これも処理が完了したら戻す必要がありますので処理の最後に以下のように設定を戻しておきます。

Application.ScreenUpdating = True

画面表示の更新を停止した場合の実行時間

ではこちらも実行してみましょう。

ちなみに、再計算はオンの状態で実行します。

結果は

画面表示の更新を停止した場合のマクロの実行時間

おお!33秒!

だいぶ速くなりましたね。

ちなみに行をコピーするPasteSpecialメソッドは画面の再描画にけっこう時間を食う印象がありますね。

ただ、一方で画面が止まるので処理の様子が見られなくなるというデメリットもあります。

運用も考えて検討しましょうね。

EnableEventsプロパティでイベントを停止する

さて、もう一つプロパティを紹介します。

イベントの発生を停止するEnableEventsプロパティです。

ブックを開くとか、シートを編集するとか、このようなタイミングを見張っていて、それに応じてプロシージャを呼び出すというイベントという仕組みがあります。

実際に、イベントの機能を使っていなかったとしても、この「見張り」を停止することで、マクロの実行速度が速くなるんですって。

EnableEventsプロパティの書式はこちらです。

書き方としては以下の通りです。

Application.EnableEvents = ブール値

Falseに設定するとイベントの発生を停止します。

Application.EnableEvents= False

これも処理の最後にTrueに戻しておきましょう。

Application.EnableEvents= True

イベントの発生を停止した場合の実行時間

では、これも試してみましょう。

ちなみに、再計算はオンの状態、画面表示の更新もオンの状態で実行します。

結果は

イベントの発生を停止した場合のマクロの実行時間

おお!21秒!

すごい効果あるじゃないですか!?

各プロパティをすべて停止にした場合の実行時間

当然、すべて停止したほうが速くなりますよね。

両方の停止をした場合の実行結果は

全プロパティを停止した場合のマクロの実行時間

6秒です!

何も対策をしていない状態より8.5倍ほど速度向上しました。

これはやらない手はありませんね!

まとめとして最終的なプログラムを紹介しておきますね。

Sub MySub()
                      
    Dim start As Date: start = Time
    
    Sheet1.Cells.Clear
    Sheet2.Cells.Clear
    
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    With Sheet1
        Dim i As Long
        For i = 1 To 300
            .Cells(i, 1).Value = i
            .Cells(i, 2).FormulaLocal = "=SUM(A1:A" & i & ")"
            .Rows(i).Copy
            Sheet2.Cells(i, 1).PasteSpecial
        Next i
    End With
        
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
        
    Dim finish As Date: finish = Time
    MsgBox "実行時間は " & Format(finish - start, "nn分ss秒") & " でした", vbInformation + vbOKOnly
    
End Sub

まとめ

以上、遅くて重いエクセルVBAのプログラムの処理速度を速くする方法プログラムの実行時間を測定する方法についてお伝えしました。

Applicationオブジェクトの3つのプロパティ

  • Calculationプロパティで自動計算を停止する
  • ScreenUpdatingプロパティで画面表示の更新を停止する
  • EnableEventsプロパティでイベントの発生を停止する

で、マクロの実行速度がかなり速くなりました。

またTime関数による実行時間の測定も、高速化の作業をする際には必須ですので、ぜひ覚えておいて下さいね。

タイトルとURLをコピーしました