今回はExcelVBAで処理速度を向上させるコツを紹介いたします。

まずは下のコードをご覧ください。

Sub TimeTest0()
   time1 = Now()
   result = 0
   For i = 0 To 1000000
      result = result + Range("C2") + Range("C3")
   Next i
   Range("C4") = result
   Range("C6") = (CDbl(Now()) - CDbl(time1)) * 24 * 60 * 60
End Sub

以下のプログラムコードは、C2セルの数値とC3セルの数値を割り算して前の結果に加算する計算を100万回行っています。

改善1 セルへの参照を最小限に抑える

Excelのセルへの参照に時間がかかることはよく知られています。そこで、ループ内でC2セルとC3セルを100万回参照している部分について、ループ前に変数化してみます。

Sub TimeTest2()
   time1 = Now()
   a1 = Range("C2")
   a2 = Range("C3")
   result = 0
   For i = 0 To 1000000
      result = result + Application.Round(a1 / a2, 0)
   Next i
   Range("C4") = result
   Range("C6") = (CDbl(Now()) - CDbl(time1)) * 24 * 60 * 60
End Sub

改善２ 変数を型宣言する

ExcelVBAは動的に型付けを行うため、VBA側が変数の型を勝手に解釈してくれます。逆に言えばその分の処理に時間を食うわけで、意識的に型宣言することで処理動作の向上を図ります。

Sub TimeTest3()
   time1 = Now()
   Dim a1 As Integer
   Dim a2 As Integer
   Dim result As Long
   a1 = Range("C2")
   a2 = Range("C3")
   result = 0
   For i = 0 To 1000000
      result = result + Application.Round(a1 / a2, 0)
   Next i
   Range("C4") = result
   Range("C6") = (CDbl(Now()) - CDbl(time1)) * 24 * 60 * 60
End Sub

今回のような小さなプログラムでは劇的に向上するわけではありませんが、プログラムが大きくなればなるほど効果を発揮します。

ご覧いただき、ありがとうございました。

■参考元

http://pineplanter.moo.jp/non-it-salaryman/2017/01/15/excel-vba-speedup/