VBAの高速化

画面の更新をオフにする

画面更新はコストが高いというのは常識です。Application.ScreenUpdating = Falseで画面更新をオフにしておいて、最後にApplication.ScreenUpdating = Trueするだけで処理スピードが早くなります。

※各Publicプロシージャーの先頭でScreenUpdating = Falseを実行しろとは言っていないことに注意。メインエントリーポイントで1回だけやればいいのです。こんなことはわざわざ言わなくても理解できると思っていましたが、今時はあんがいそうでもないらしいですね。

Public Sub MainEntrypoint()

    Application.ScreenUpdating  = False
    ....
    ....
    ....

    Application.StatusBar = False
    Application.ScreenUpdating = True

End Sub

.Selectionはダメ絶対

マクロ記録は便利ですが、ゴミのようなコードしか生成されません。特にマクロ記録で生成された.Select、.Selectionに依存したコードは使うべきではありません。
理由１ 画面更新が頻繁に起こり、その更新に時間がかかる。
理由２ 現在のシートが変わると実行時エラーが起こる。
ソートをするときでさえSelectionを使ってはいけません。Rangeオブジェクトを使えば同じことはできるのです。

セル自動計算オフは禁止

ScreenUpdatingに似た機能でセル自動計算オフがあります。セルの値を変えるたびに関係するセルを再計算するわけですから、これをオフにすれば処理スピードが早くなります。

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

自動計算をオフすれば高速になるのは明らかですが、処理内容によってはやってはいけない場合があります。例えば、VBAの中でセルに数式を設定して、その計算結果に応じて次の処理をしたいケースでは、セルが更新されないので実行時エラーが発生します。

xTmp.Offset(0, 6) = "=RC[-1]-RC[-2]"
if xTmp.Offset(0, 6) < 0 Then Debug.Print "マイナス"

このケースでは、xTmp.Offset(0, 6)が更新されないので、次のIf文はスルーしてしまいます。

イベントをオフにする

Worksheet_Changeイベントなどのようなイベントプロシージャは特殊な用途として使うと便利な場合がありますが、そのイベントを発生させないようにするのがEnableEventsです。Changeイベントの中でセルを変更したら、またChangeイベントが呼び出されて、永久ループに陥ってしまう。というような状況を回避するには便利ですが、VBAの高速化という意味では意味がないような気がします。
Application.EnableEvents = False
Application.EnableEvents = True

ステータスバーの更新頻度を下げる

VBAの中で処理時間が長い場合に、ステータスバーに"処理中"などと表示させておいて、終了したら"終了"に変える。というようなことはよくやります。しかし、"処理中"だけでは本当に処理中なのか、フリーズしてしまったのか不安になる、という時に"処理中： 15/510 残り時間： 8.5分" のように表示すれば心理的に安心できそうです。ところがあまりにもその更新頻度が高いと、逆に時間がかかってしまうことがあります。
やり方はいろいろあると思いますが、次のようにすれば簡単に頻度を落とすことができます。参考にしてください。※"If iCount mod 10 = 0 Then"とは、レコード件数10件ごとに処理中メッセージを更新するという意味です。

If iCount mod 10 = 0 Then
    Application.StatusBar = "処理中：" & iCount & "/" & iTotalRec & " 残り時間:" & iRemain
End If

ループの中でのDoEvents

まずDoEventsというのは、VBAのCPUスライスタイムをエクセル本体にいったん戻してあげるという機能を持っています。例えば、VBAの中で高速にループさせると人間がそれを止めることは極めて困難になります。しかし、ループの中でDoEventsを呼び出すと、エクセルがマウスクリックやキーボードに反応するようになって、CTRL+BREAKでVBAの実行を中断させることが可能になるわけです。

一見便利なDoEventsですが、これが原因で処理スピードが極端に遅くなるというバグが発生してしまい、発見するのに大変な思いをしました。どのようなループで遅延が起こるのかはっきり分かりませんが、次のような２重ループでした。

Do While True
    For Each xCell in xRange
        DoEvents

    Next
Loop

Webクエリが遅くなる問題

Webクエリのダウンロード時間がかかるようになった

Webクエリはテーブル指定で

.WebSelectionType = xlEntirePageで全ページデータを落とすのではなく、.WebSlelectionType = xlSpecifiedTables指定にしておいて、.WebTables にテーブル番号指定することで(例：.WebTables = "1,2,3")、かなり時間短縮になる。

 それは言われなくても知ってます編

■オブジェクト変数のLate Bindは使用しない。
■自分でDoループを書くくらいならApplication.WorksheetFunction.Matchを使う。
■WorksheetFunction.Findは遅いので使わない。
■.Selectとか.Activateは使わない。
■VBAで書式を変えるのではなく極力条件付き書式を利用する。
■Range.Textプロパティは.Valueよりも４倍遅い

■１万件を超えるデータはエクセル ではなくAccess、SQL Server、MySQLなどのデータベースで管理する。※SQL ServerやSybaseは使いやすいです。(個人の感想です