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

★気に入ったらシェアをお願いします!


speed

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

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

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

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

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

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

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

スポンサーリンク

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

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

値を書きこんでその行を無駄にコピーするというのを1000回繰り返すプログラムです。

しかも、Sheet2をご覧いただくと

無駄な数式がたくさんのSheet2

Sheet1のA列の1000行分をSUMするという数式が1000行目まで埋め尽くされているという、こちらもかなり意味不明な内容です。

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

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

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

実行前の時間を取得して、実行後の時間から引き算するという古典的な方法ですね。Format関数で分と秒による書式に変更をしています。

あ、ちなみに日付をまたいじゃうと計算できないので、何日もかかるプログラムの測定は別の方法で実装しましょうね。

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

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

何もしない場合のエクセルVBA実行速度

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

Application.Calculation 自動計算を停止する

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

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

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

Application.Calculation = xlCalculationManual

Applicationはエクセルのアプリケーションを表すオブジェクトと思って頂ければOKです。Caluculationというのが計算モードに関する状態を表すプロパティです。

それをマニュアルにするわけですから自動計算がオフになります。

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

Application.Calculation = xlCalculationAutomatic

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

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

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

結果は

自動計算を停止した場合のエクセルVBA実行時間

1分8秒でした。少しだけ速くなりましたね。

Sheet2のSUMの再計算がいちいち実行されなくなった結果だと思います。SUMPRODUCTなど配列×配列で計算するような数式が乱用されている場合などにはもっと効果があると思います。

Application.ScreenUpdating 画面表示の更新を停止する

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

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

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

書き方としては

Application.ScreenUpdating = False

これで画面表示の更新が停止されます。

これも処理が完了したら戻す必要がありますので

Application.ScreenUpdating = True

とする必要があります。

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

ではこちらも実行してみましょう。ちなみに、再計算はオンの状態で実行します。

結果は

画面表示の更新を停止した場合のエクセルVBA実行時間

おお!29秒!

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

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

自動計算も画面表示の更新も停止する

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

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

自動計算と画面表示の更新を停止した場合のエクセルVBA実行時間

23秒です!何も対策をしていない状態より1分近くの速度向上となりました。

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

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

まとめ

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

こちら二つのテクニック

  • Application.Calculation 自動計算を停止する
  • Application.ScreenUpdating 画面表示の更新を停止する

いずれも有効な場合がほとんどです。

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