Excel/VBA：処理速度を劇的に上げるテクニック３つ

2017/01/15

プログラマ以外の一般的なサラリーマンの間でExcel/VBAがよく利用されていますが、アマチュアだけにあまり処理速度を気にしていません。処理時間に数分かかってしまうようなものも放置されがちですが、やはり早いことには越したことはないので、速度をあげるちょっとしたテクニックを紹介します。

[ad#top-1]

1 まずはサンプルコードです。
2 改善1　セルへの参照を最小限に抑える
3 改善２　変数を型宣言する
4 改善３　四捨五入処理を自作する
5 まとめ

まずはサンプルコードです。

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

処理の最初と最後の時刻差を計算して秒数に直してC6セルに表示させています。この時間（秒）が処理時間となります。

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

このプログラムを僕のパソコンで動かすと273秒かかりました。5分近くもかかってしまいます。

このプログラムをどこまでスピードアップさせることができるでしょうか。結論から申しますと、最終的に3秒まで短縮することに成功しています。

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

Excelのセルへの参照に時間がかかることはよく知られています。そこで、ループ内でC2セルとC3セルを何度も参照しているところを、ループ前に変数化してみるとどうなるでしょうか。

以下がそのコードになります。前のプログラムはループにRaneg(“C2”)とRaneg(“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

すると処理時間は106秒となり、前に比べ61%もスピードアップできました。

改善２　変数を型宣言する

Excel/VBAは動的型付けのため、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

処理時間は72秒となり、前に比べ12.5%もスピードアップです。

改善３　四捨五入処理を自作する

Excel関数を利用できるApplicationオブジェクトはとても便利ですが、結構速度を犠牲にしていることを知っている人はあまりいません。僕も人から指摘されて知りました。

そこで、例に使われているApplication.Round（四捨五入）を自作してみました。


Sub TimeTest4()
   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 + RoundFnc(a1 / a2, 0)
   Next i
   Range("C4") = result
   Range("C6") = (CDbl(Now()) - CDbl(time1)) * 24 * 60 * 60
End Sub
 
Function RoundFnc(数値, 桁)
   result = 数値 * (10 ^ 桁)
   result = result + 0.5
   result = Int(result)
   resunt = result / (10 ^ 桁)
   RoundUpFnc = resunt
End Function

すると！なんと3秒まで縮まりました。ここまで短縮できたのはビックリです。