非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

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

   

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

[ad#top-1]

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

以下のプログラムコードは、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秒まで縮まりました。ここまで短縮できたのはビックリです。

 

まとめ

まとめると以下の表になります。当初のプログラムにTimeTest1～3の対策を施すと5分近くあった処理時間がたった3秒にまでなってしまいます。99%も改善できました。

マクロ名 処理時間（秒） 割合
TimeTest0 273 100.0%
TimeTest1 106 38.8%
TimeTest2 72 26.4%
TimeTest3 3 1.1%

 

こちらがグラフ。大幅に下がっていることがわかります。

 

プロのプログラマにしたら当然の処置なのかもしれませんが、一般サラリーマンが自分の業務改善に使う程度の処理にはあまり速度を気にしていません。でもやっぱり遅いとストレスになるので、こんなことを気にしながらプログラミングされたらと思います。

[ad#ad-1]

スポンサーリンク

 - Excel, VBA