重いマクロ(VBA)を軽くする簡単で超効率的な方法

マクロが重すぎてフリーズしてしまったことありませんか？

今回はそんなときの対象法を紹介します。

よく使われる方法：全部OFF

個人的にはそんなにと思いますが、まずはよく使われる全部OFFを紹介します。

  With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .Calculation = Excel.XlCalculation.xlCalculationManual
  End With


 With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = Excel.XlCalculation.xlCalculationAutomatic
 End With

これは何かというと、Excelの機能を一時的にOFFにしているのです。
ポップアップが出たり、画面のアニメーションであったり、自動計算であったり、そういった機械的にはどうでもいい部分を全部OFFにして、最後にONにしています。

そこそこ早くなります。
また、デメリットも特にないので便利です。

※強いてデメリットを言うと、エラーを起こしてONのコードが読み込まれる前に止まるとちょっとめんどくさいです。
手動でONにするか、その部分だけコードを実行させないといけませんからね。

個人的におすすめしたい方法：全部配列

個人的に大好きな方法が全部配列です。

これは配列内ですべての計算や処理を終わらせ、最後に出力するだけなのでめちゃくちゃ軽いです。
どれだけ大規模な計算でも1秒ぐらいで終わります。

簡単な例

↓のような無駄な計算をするとします。
PCのスペックによってはフリーズしますよね。
私のPCでは２８秒くらいかかりました。

Sub test()

Dim n As Long

For n = 1 To 1000000
    
    Cells(n, 3) = Cells(n, 1) + Cells(n, 2)
    
Next n

End Sub

※100万回足し算をしています。

↓は配列を利用した方法です。
私のPCでは3.5秒くらいで終わりました。

Sub test2()

Dim a
Dim n As Long

a = Range("A1").CurrentRegion

ReDim Preserve a(1 To UBound(a), 1 To 3)
    
For n = 1 To UBound(a)
    
    a(n, 3) = a(n, 1) + a(n, 2)
    
Next n
Range(Cells(1, 1), Cells(UBound(a), 3)) = a

End Sub

ちょっとややこしいですが、とても速くなります。

暗記と暗算的な方法

なぜ速くなるかというと、イメージとしては暗記と暗算です。

私たちは計算をするとき、紙に書いて計算し答えを随時書いていきますよね。
それを、見た瞬間にすべての問題を暗記し、脳内ですべて暗算して答えと問題をセットで記憶できるとしたらすごいですよね。

後は答えを書いていくだけですからね。

配列を使った方法というのは、そのありえないレベル暗記と暗算をやっているわけです。