【VBA】処理速度が遅い時の対処法

VBA
この記事は約3分で読めます。

マクロを組んだ時に「やたらと処理が速度が遅いな…」と感じたことはありませんか?

処理の中で扱うデータ量が多かったり、別のブックを開いたりすると処理が終わるまで時間が掛かることがあります。

うめ
うめ

エクセルが「応答なし」のまま操作できなくなることがありませんか?

実際にVBAの処理速度を改善する書き方をするとどのくらい差が出るのか比較してみようと思います!

スポンサーリンク
[ad01]

サンプル

今回は処理時間を計測しながら比較してみようと思います。

上記マクロを実行した結果がこちらです👇

【実行結果】

処理の内容としてはA~K列までAを10ずつ加算した数字が入り、縦には10万行まで加算した値が設定されています。

実行結果(対策なし)

速度改善の処理をせずに実行した結果は約42.8秒でした。

途中でExcelが応答なしになったので、処理が進んでいるのかフリーズしているのかわからなくなりました。

対策方法

画面の更新を停止

まずは画面の更新をリアルタイムで確認する必要はなので、止めてしまいましょう。

画面の更新を止める場合は処理の前に「Application.ScreenUpdating = False」と設定します。

処理の最後には「Application.ScreenUpdating = True」と設定して画面が更新するように戻してください。

画面の更新を止めてから処理時間を測定すると約42秒とほぼ変化がありませんでした。

自動計算を停止

続いてExcelの自動計算を停止しましょう。

注意点

Excelの自動計算を停止すると計算方法の設定が「自動」⇒「手動」に変更されます。実行後は必ず「自動」に戻してください。

Application.Calculation = xlCalculationManual」となっている間は手動計算に切り替わります。

処理の最後には「Application.Calculation = xlCalculationAutomatic」と設定して自動計算に戻してください。

画面更新、自動計算を停止すると約9秒ほど早くなりました。

うめ
うめ

今回は参照するセルが多いため、自動計算の停止が効果的でした。

セルの配列化

最後にセルにアクセスする回数を最小限に変更します。

For文で11回*100000回セルを参照していた分を配列に変更して、For文を抜けたあとに1回だけセルに設定するように修正してみました。

実行結果は1.4秒です!

うめ
うめ

比較するまでもなく一瞬で処理が終わりました…。

これでセルのアクセス回数が多いほど処理速度に影響があることがわかりますよね。

ポイント

配列化=セルのアクセス回数が減る」ため処理速度が劇的に改善します。

二次元配列を使った記事はこちらからどうぞ!

まとめ

最終的なサンプルコードは下記のようになりました。

まとめ

画面の更新や自動計算を停止することも有効的ですが、セルのアクセス回数を減らした方が処理速度が向上するケースが多いです。

うめ
うめ

配列を使いこなすことが、VBA処理速度改善の近道です!

この記事が気に入ったら
いいね ! しよう

Twitter で
この記事を書いた人
うめ

便利なガジェットレビューやPCのトラブル解消記事を書いています。
単調な繰り返し作業が苦手なので、作業効率化の仕組みを記事にしています。
月間9万PVを突破しました!
商品レビューのご依頼は「お仕事の依頼について」をご確認ください。

うめをフォローする
VBA
スポンサーリンク
[ad01]
梅屋ラボ
タイトルとURLをコピーしました