0. 共通
基本的に、全てのVBAの処理は以下のコードの間に入れることが望ましい。
Application.ScreenUpdating = False '画面の描画更新を停止する
Application.Calculation = xlCalculationManual '自動計算をOFFにする
Application.EnableEvents = False 'イベントの発生を無効にする
Application.Cursor = xlWait 'マウスポインタを砂時計にする
Application.Cursor = xlDefault 'マウスポインタをデフォルトにする
Application.EnableEvents = True 'イベントの発生を有効にする
Application.Calculation = xlCalculationAutomatic '自動計算をONにする
Application.ScreenUpdating = True '画面の描画更新を有効にする
VBA高速化に関して、自動計算や画面の描画更新については多くのWeb記事を拝見したが、
マウスポインタについてはあまり記事をみかけなかった。
この設定を入れていないと、Excel上にカーソルがある時に「砂時計←→通常」の状態変化を激しく繰り返すことによって、本来10秒程度で済む処理が5倍程度に膨れ上がっていた。
Excel2013以降で発生するらしい。（自分はExcel2019で同事象を確認）
こちらの記事に助けていただいた。ありがとう。
1. コピーする
コピーして貼り付けを行う場合、以下の点に気をつけたほうが良い。
1-0. クリップボードへのコピーは必要最低限にする
個人的に意外だったのだが、「クリップボードにコピーする」という処理がそれなりに重いということ。
例えば、テンプレートシートの１行をコピーして、出力用シートにデータ件数に応じて貼り付けを行うというような処理を想定する。
コピーした情報はクリップボードに保持されているため、出力処理を含むループ内で毎回コピーする必要はない。
クリップボード上の情報を貼り付けるだけで良いので、不要なコピー処理は避けること。
単一セルのコピー処理を100回ループさせたところ、3秒程度の時間がかかった。
1-1. 書式をコピーしたい
この場合は必要最低限の範囲を指定して書式の貼り付けを行った方が(値を含めた貼り付けと比べて)2倍ほど早い。
逆に、値のみをコピーする場合も同様の指定の方が望ましい（と思う）。
Range("A1").PasteSpecial Paste:=xlPasteFormats
| 設定項目 | 設定値 | 結果 |
|---|---|---|
| Paste | xlPasteValues | 値貼り付け |
| Paste | xlPasteFormats | 書式貼り付け |
※上記は一部情報の抜粋です。
参考:MS公式ドキュメント
1-2. 行や列をコピーして挿入したい
この場合は、先に行や列を新規挿入した上で、コピー内容を貼り付けた方がよい。
自分では未確認だが、処理件数によっては数分程度早くなったとも。。。
値をセットする
シートに値をセットする際、以下のような古典的な書き方をしていませんか？
- 処理対象の元となるデータの件数だけ、出力先のシートの行に対してループ
- データの項目の数だけ、出力先のシートの列に対してループしながら値をセット
単純な一覧形式のデータを出力する場合、二次元配列の変数に値をセットした後、出力先シートの指定範囲に対して一度だけ書き込みを行った方が、圧倒的に早いです。
ステータスバーの表示
処理に時間がかかる場合、ステータスバー（Excelの左下にあるエリア）に進捗状況を表示させる、ということをしがちである。
利用者に対して親切な仕組みではあるが、例えば1000回ループする際に1000回ステータスバーを更新する必要はあるのだろうか？
For i = 1 to 1000
Application.StatusBar = i & "件目を処理中です。。。"
Next
条件分岐の処理を追加してでもステータスバーの更新頻度を下げた方が、全体としてはパフォーマンスが向上するため、全体の処理時間との兼ね合いでステータスバーの表示は検討すべきである。
For i = 1 to 1000
If i Mod 100 = 0 Then
Application.StatusBar = i & "件まで処理しました。"
End If
Next