作ったEXCELマクロがめちゃくちゃ重い

EXCEL

世の中にはちょっと重いマクロと、めちゃくちゃ重いマクロがある。ちょっと重いマクロの原因は多岐にわたるが、めちゃくちゃ重いマクロの原因は２つに集約される。
１つは、自動計算との衝突。もう１つは処理件数が多い場合の再描画だ。
・自動計算との衝突マクロによって、セルの値が変更されると、変更された値をトリガとして再計算が実施される。この再計算が、単純な再計算であっても10^1から10^2msecのオーダーで時間がかかったりするので、100件程度の情報更新であっても数秒から数十秒かかる。だから、EXCELマクロを作成する場合は、必ず自動計算を止めるコードを書いてから、実行されるように記述するのがよい。また、エラーによって自動計算がキャンセルされてしまったままになってしまうので、かならずエラー処理に「自動計算を再開する」コードを書いておくことが必要だ。簡単に書いてしまうと

Function nantoka() as Long
    '自動更新を止めます
    Application.Calculation = xlCalculationManual

On Error GoTo kantoka
    '〜
    '各種処理
    '〜
    '自動更新を再開します
    Application.Calculation = xlCalculationAutomatic
    Exit Function

kantoka:
    '自動更新を再開します
    Application.Calculation = xlCalculationAutomatic
    msgbox "なんかエラーしました"

End Function

てな感じになる。基本、メインとなるようなモジュールには、自動計算の有無にかかわらず必ず書いておくとよい。
⇒後から計算式が追加された場合にあらかじめ備える。

・件数が多い場合の再描画
再描画は時間がかかるが、人は進捗が見えないことに重さを感じるので、数百件程度であれば、進捗を見せるために再描画することが望ましい。
ただし、処理件数が1000件を超えると描画コストのデメリットのほうが大きくなるので、その場合は表示を切ってしまったほうがいいだろう。尚、再描画の再開は、マクロ終了時に勝手に再開されるので、特に記述の必要はない。

Function nantoka() as Long
    '再描画を止めます
    Application.ScreenUpdating = False
    '〜
    '各種処理
    '〜
End Function

まずはこの2つだけで、劇的に早くなるので、やってみるといいだろう。