みなさん、こんにちは!
タカハシ(@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はエクセルのアプリケーションを表すオブジェクトと思って頂ければOKです。
計算モードには、手動計算を表す定数xlCalculationManualや、自動計算を表す定数xlCalculationAutomaticを設定します。
ですから、プロシージャの冒頭で以下のように設定しておけばマニュアル、つまり手動にするわけですから自動計算がオフになります。
Application.Calculation = xlCalculationManual
実行したい処理が完了したら
Application.Calculation = xlCalculationAutomatic
として自動計算に戻します。
自動計算を停止した場合の実行時間
プログラム冒頭で自動計算の停止をした場合の測定をしてみます。
結果は
40秒…!少しだけ速くなりましたね。
SUM関数の再計算がいちいち実行されなくなった結果だと思います。
SUMPRODUCTなど配列×配列で計算するような数式が乱用されている場合などにはもっと効果があると思います。
ScreenUpdatingプロパティで画面表示の更新を停止する
まだ遅いのでもう一つ紹介しましょう。
エクセルのいずれからのセルの変更時には、画面表示が更新されています。私たちはあまり気にしていませんが、セルが入力されたり、値が変更されたりする際に、都度画面表示が更新されているわけです。
その画面表示の更新をプログラムで止めてしまおうということです。
画面表示の更新を設定するには、ApplicationオブジェクトのScreenUpdatingプロパティを使います。
書き方としては以下の通りです。
Falseに設定すると画面表示の更新が停止します。
Application.ScreenUpdating = False
これも処理が完了したら戻す必要がありますので処理の最後に以下のように設定を戻しておきます。
Application.ScreenUpdating = True
画面表示の更新を停止した場合の実行時間
ではこちらも実行してみましょう。
ちなみに、再計算はオンの状態で実行します。
結果は
おお!33秒!
だいぶ速くなりましたね。
ちなみに行をコピーするPasteSpecialメソッドは画面の再描画にけっこう時間を食う印象がありますね。
ただ、一方で画面が止まるので処理の様子が見られなくなるというデメリットもあります。
運用も考えて検討しましょうね。
EnableEventsプロパティでイベントを停止する
さて、もう一つプロパティを紹介します。
イベントの発生を停止するEnableEventsプロパティです。
ブックを開くとか、シートを編集するとか、このようなタイミングを見張っていて、それに応じてプロシージャを呼び出すというイベントという仕組みがあります。
実際に、イベントの機能を使っていなかったとしても、この「見張り」を停止することで、マクロの実行速度が速くなるんですって。
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関数による実行時間の測定も、高速化の作業をする際には必須ですので、ぜひ覚えておいて下さいね。