【Excel】VBAで処理速度を劇的に改善!ExcelのApplication設定で画面更新を抑制する最適化術

Excel VBAで大量のデータを処理する際、処理速度の遅さに悩むことはありませんか。

Excelの画面が頻繁に更新されることで、処理に時間がかかっている可能性があります。

この記事では、VBAで画面更新を抑制し、処理速度を劇的に向上させるApplication.ScreenUpdatingプロパティの設定方法を解説します。

これにより、大規模なデータ操作や複雑な計算もスムーズに行えるようになります。

VBA実行時の画面更新が処理速度に与える影響

Excel VBAでマクロを実行する際、コードが1行ずつ実行されるたびにExcelの画面が更新されることがあります。

これは、セルの値が変更されたり、書式が適用されたりするたびにExcelが画面表示を再描画するためです。

特に、大量のセルに対する操作や、複雑な計算を繰り返す処理では、この画面更新のオーバーヘッドが無視できないほど大きくなります。

画面更新を抑制するApplication.ScreenUpdatingプロパティ

Excel VBAには、この画面更新の挙動を制御するためのプロパティがあります。

それが `Application.ScreenUpdating` プロパティです。

このプロパティを `False` に設定することで、VBAコードの実行中は画面の更新が一時的に停止されます。

これにより、Excelは画面描画にかかるリソースを消費せず、純粋にコードの処理に集中できるようになります。

ScreenUpdatingプロパティの基本設定

画面更新を抑制するには、VBAコードの実行開始直前に `Application.ScreenUpdating = False` を記述します。

そして、処理が完了した後、または処理の途中で画面表示を元に戻したい場合に `Application.ScreenUpdating = True` を記述します。

この2つの設定を適切に組み合わせることが、処理速度の最適化の基本となります。

画面更新を抑制する具体的なVBAコード例

ここでは、実際に画面更新を抑制して処理速度を改善するVBAコードの例を示します。

例として、10000行のデータに対して特定の計算を行い、結果を別のセルに書き込む処理を考えます。

1. 画面更新の停止
   コードの冒頭で `Application.ScreenUpdating = False` を設定します。これにより、以降の処理中の画面更新が無効になります。
2. 処理の実行
   ここで、大量のデータ操作や計算処理を記述します。例えば、ループ処理で各行の値を参照・計算し、結果を書き込むなどです。
3. 画面更新の再開
   処理が完了したら、必ず `Application.ScreenUpdating = True` を設定して画面更新を元に戻します。これにより、ユーザーは最終的な結果を確認できます。

エラー発生時の画面更新再開処理

VBAコードの実行中にエラーが発生した場合、`Application.ScreenUpdating = True` の行に到達しない可能性があります。

その結果、Excelの画面が更新されないままフリーズしたような状態になってしまうことがあります。

このような事態を防ぐために、エラーハンドリングと組み合わせて画面更新の再開を保証する必要があります。

1. エラーハンドリングの開始
   コードの冒頭に `On Error GoTo ErrorHandler` のように記述し、エラー発生時の処理を指定します。
2. 本来の処理
   画面更新を停止した状態で、本来実行したい処理を記述します。
3. 正常終了時の画面更新再開
   処理が正常に完了した場合、`Application.ScreenUpdating = True` を実行し、その後 `Exit Sub` などでプロシージャを抜けます。
4. エラーハンドラセクション
   コードの最後に `ErrorHandler:` ラベルを設け、その下にエラー発生時の処理を記述します。
5. エラーハンドラ内での画面更新再開
   エラーハンドラセクションの最初に `Application.ScreenUpdating = True` を記述します。これにより、エラー発生時でも必ず画面更新が再開されます。
6. エラーメッセージの表示(任意)
   必要に応じて、ユーザーにエラーが発生したことを知らせるメッセージボックスなどを表示します。

画面更新と同時に抑制すべき設定

画面更新 (`ScreenUpdating`) 以外にも、VBAの処理速度に影響を与えるプロパティがいくつか存在します。

これらを同時に `False` に設定することで、さらに処理速度を向上させることが可能です。

1. Application.EnableEvents

このプロパティは、Excelのイベント(ワークシートの変更、ブックの保存など)を有効にするか無効にするかを制御します。

VBAコードの実行中にイベントが発生すると、それが処理の遅延につながることがあります。

そのため、`Application.EnableEvents = False` と設定することで、不要なイベント発生による処理の妨げを防ぐことができます。

2. Application.DisplayStatusBar

Excelウィンドウ下部に表示されるステータスバーの表示・非表示を制御します。

ステータスバーは処理の進行状況を表示するために使われますが、VBAコード実行中は不要な場合が多いです。

`Application.DisplayStatusBar = False` と設定することで、ステータスバーの更新処理を抑制し、わずかですが処理速度の向上に貢献します。

3. Application.Calculation

ワークシート上の数式の再計算モードを制御します。

デフォルトは `xlCalculationAutomatic` で、セルが変更されるたびに自動で再計算が行われます。

大量のセルを変更する処理では、この自動再計算が頻繁に発生し、処理速度を著しく低下させます。

`Application.Calculation = xlCalculationManual` に設定することで、再計算を手動で行うように変更できます。

手動計算モードにした後は、必要なタイミングで `Application.Calculate` または `ThisWorkbook.Calculate` を実行して明示的に再計算を行う必要があります。

これらのプロパティを組み合わせたVBAコード例

以下は、`ScreenUpdating`、`EnableEvents`、`DisplayStatusBar`、`Calculation` の各プロパティを組み合わせて、処理速度を最大化するためのVBAコードの典型的な構成です。

1. 初期設定(最適化)
   コードの開始部分で、以下の設定を行います。
   `Application.ScreenUpdating = False`
   `Application.EnableEvents = False`
   `Application.DisplayStatusBar = False`
   `Application.Calculation = xlCalculationManual`
2. エラーハンドリングの設定
   エラー発生時の処理に備え、`On Error GoTo ErrorHandler` を記述します。
3. メインの処理
   画面更新、イベント、ステータスバー表示、自動計算が無効な状態で、実行したいVBAコードを記述します。
4. 手動での再計算(必要な場合)
   もし `Application.Calculation = xlCalculationManual` に設定した場合、処理の完了前または完了後に `Application.Calculate` を実行して、数式の再計算を行います。
5. 後処理(画面更新再開など)
   処理が正常に完了した場合、以下の後処理を行います。
   `Application.ScreenUpdating = True`
   `Application.EnableEvents = True`
   `Application.DisplayStatusBar = True`
   `Application.Calculation = xlCalculationAutomatic`
   `Exit Sub`
6. エラーハンドラセクション
   コードの最後に `ErrorHandler:` ラベルを設け、エラー発生時の処理を記述します。
7. エラーハンドラ内での後処理
   エラーハンドラセクションの最初に、正常終了時と同様の後処理(各プロパティを元に戻す)を記述します。これにより、エラー発生時でもExcelの状態が元に戻ります。

最適化設定の注意点とよくある失敗例

Application.ScreenUpdatingなどのプロパティ設定は、処理速度を劇的に向上させる強力な手法ですが、いくつか注意すべき点があります。

これらの注意点を理解せずに設定を行うと、意図しない動作を引き起こしたり、デバッグが困難になったりする可能性があります。

エラー発生時に設定が元に戻らない

最もよくある失敗は、VBAコードの実行中にエラーが発生し、`ScreenUpdating = True` などの復旧処理が実行されないまま処理が終了してしまうケースです。

この場合、Excelの画面が更新されない、イベントが発生しない、ステータスバーが表示されないなどの状態になり、Excelが正常に動作しなくなります。

解決策: 前述したように、`On Error GoTo` を使用したエラーハンドリングを必ず実装し、エラーハンドラセクション内で全てのプロパティを初期値に戻すようにしてください。

画面更新の再開を忘れる

コードの最後で `Application.ScreenUpdating = True` を記述し忘れると、マクロ実行後も画面更新が無効なままになります。

これは、`EnableEvents` や `Calculation` についても同様です。

結果として、その後の手動操作でも画面が更新されない、数式が計算されないといった問題が発生します。

解決策: コードの終了部分(正常終了時とエラーハンドラ時)に、全てのプロパティを初期値に戻す処理を確実に記述してください。

意図しないイベントの発生

`Application.EnableEvents = False` を設定した場合、ワークシートの変更イベントなどをトリガーとする他のマクロが実行されなくなります。

もし、メインの処理とは別に、特定のイベント発生時に自動実行されるべきマクロがある場合、それは実行されません。

解決策: 処理の前後で `EnableEvents` を適切に管理するか、あるいはイベントをトリガーとするマクロ自体を一時的に無効化するのではなく、メインの処理内で必要な処理を明示的に呼び出すようにコードを修正することを検討してください。

手動計算モードでの再計算忘れ

`Application.Calculation = xlCalculationManual` に設定した場合、数式の結果は自動で更新されません。

処理の途中で数式の結果を参照する必要がある場合や、処理完了後に最新の結果を表示したい場合、`Application.Calculate` を実行し忘れると、古い値が表示されたままになります。

解決策: `Application.Calculation = xlCalculationManual` を設定した場合は、処理のどこかで、または最後に必ず `Application.Calculate` を実行するコードを追加してください。特に、ユーザーに結果を表示する前には再計算が完了していることを確認してください。

デバッグ時の困難さ

画面更新やイベントが無効になっていると、ブレークポイントを設定してコードをステップ実行しても、変数の値の変化や処理の進行状況が視覚的に把握しにくくなります。

これにより、デバッグ作業が通常よりも困難になることがあります。

解決策: デバッグを行う際は、一時的に `Application.ScreenUpdating = True` や `Application.EnableEvents = True` など、最適化設定を無効にして実行することを推奨します。デバッグが完了したら、再度最適化設定を有効にして処理速度の確認を行ってください。

Application設定最適化の適用場面

Application.ScreenUpdatingなどの設定は、以下のような場面で特に効果を発揮します。

大量データのコピー・移動・削除

数万行、数十万行といった大量のデータをコピー&ペーストしたり、削除したりする処理は、画面更新のオーバーヘッドが大きくなりがちです。

これらの処理の前に画面更新を抑制することで、処理時間を大幅に短縮できます。

書式設定の適用

条件付き書式の設定、セルの色やフォントの変更など、大量のセルに書式を適用する処理も同様に画面更新の負荷が大きいです。

画面更新を抑制することで、これらの書式設定処理も高速化できます。

ループ処理によるデータ更新

ForループやDoループを使用して、各行または各列のデータを一つずつ処理・更新していく場合、ループの各イテレーションで画面が更新されると処理が非常に遅くなります。

ループ処理の全体を `ScreenUpdating = False` で囲むことで、劇的な速度向上が期待できます。

外部データソースからのデータ取得・更新

Power QueryやADOなどを利用して外部データベースやWebからデータを取得し、Excelシートに書き込む処理においても、画面更新の抑制は有効です。

特に、取得したデータを加工・整形してシートに展開する際に、画面更新を停止することで処理時間を短縮できます。

数式計算の最適化が必要な場合

ワークシート上に多数の複雑な数式が存在し、データ更新時に頻繁な再計算が発生して処理が遅くなる場合、`Application.Calculation = xlCalculationManual` を活用します。

これにより、必要なタイミングでのみ再計算を実行するように制御し、全体の処理速度を向上させます。

まとめ

Excel VBAで `Application.ScreenUpdating = False` を設定することで、VBAコード実行中の画面更新を抑制し、処理速度を劇的に改善できます。

さらに、`Application.EnableEvents`、`Application.DisplayStatusBar`、`Application.Calculation` といったプロパティも同時に最適化することで、より一層の高速化が期待できます。

エラーハンドリングと組み合わせて、これらの設定が必ず元に戻るように実装することが、安定したマクロ開発の鍵となります。

大量データ処理や複雑な操作を行うVBAコードに、これらの最適化術を適用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】