エクセルマクロの高速化

※エクセル・・・Excel。会計や出た解析等に利用されるマイクロソフト社の表計算ソフト
※マクロ・・・一連の操作を自動実行する機能の一つ

知らないと恐ろしい高速化テクニック

開発者が処理速度に対する配慮を怠ると、出来る事も処理時間が掛かり利用に支障があり価値が薄らぎます。そのため、代替手段の考案に時間を取られ、場合によっては仕様に盛り込まれなくなる事もあります。原因は、エクセルのVBAマクロ(VBA…Visual Basic for Applications プログラミング言語)の処理時間が非常に長くなる傾向があるからです。場合によっては10分掛かる処理も、処理時間を軽減化する処理を入れるだけで10秒もたたずに終わる事もあります。対策を取らず処理時間が長いものは、使われない機能となる見込みが強く、仕様から外されてしまいます。 しかし、これは簡単に軽減化できます。この方法を知らないだけで、機能実現の有無が変わってしまうのです。 ところが、簡単な方法だけでは十分でなく、更にスピードを必要とする事もあります。遅さが操作性の悪さに繋がっているケースや利益に直結するケースです。特に操作性の悪さは誤操作に繋がり、使い勝手の悪いモノになります。この段階になると小手先の知識の適用だけでは通用しません。各自が調べて原因を追及して初めて高速化できるものです。 ここでは、簡単な高速化の具体的な手段と更なる高速化方法について触れ、どのように開発者が取り組んでいるか知る手段を伝えたいと思います。

高速化の基本路線

処理速度の高速化は、以下の3点を行えば大抵満足できる速さに収まります。
・画面の描画の抑制
・自動計算から手動計算に変更
・イベント発生の抑制
三点とも自動実行している処理を抑制するものです。

Sub macro
    Application.EnableEvents = False	‘ イベント抑制
    Application.Calculate = xlCalculationManual ‘ 自動計算を手動計算へ
    Application.ScreenUpdating = False  ‘  画面の描画の抑制
    ' 処理
    Application.ScreenUpdating = True
    Application.Calculate = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub
かつて非表示化のマクロに適用したところ劇的に処理時間が短くなり、10分ほど掛かっていた処理時間が10秒もたたずに完了しました。高速化に極めて強力な処理ですが、上の処理だけでは速度が十分に向上しないケースもあります。 そこで各処理に掛かる時間を計測し、ボトルネックとなっている処理を改善していく事になります。

処理速度の計測法

測定精度が高いほど高速化の精度があがるので、m秒単位の時間測定を行います。しかし、windowsの時間解像度が約10m秒のため、実際には1/100秒単位の比較なります。

最も手軽に処理速度を確認できるのはTimer(現在時刻の変数)とCDbl()(任意の変数を倍精度浮動小数点に変換する関数)との組み合わせです。
秒単位のTimerで取得し、double型(倍精度浮動小数点の変数型)に変換する事で時刻をm秒単位の精度を得ています。

Sub Macro()
    Dim timeA As Double
    Dim timeB As Double
    
    timeA = CDbl(Timer)   
  ' 処理
    timeB = CDbl(Timer)
   
    Debug.Print ((timeB - timeA))
End Sub
このような速度測定を行いながら、処理時間の掛かっている処理からロジックの見直しを行い、高速化を目指します。

処理速度の速い変数型とは

ロジックの見直しが処理の向上に繋がる事が多いのですが、どのような処理が処理速度に影響するのか知るのも欠かせません。
計測環境
OS windows7 32bit / Excel2007 32bit / メモリ4Gbytes
変数型による処理速度の違い
変数型が処理速度に影響する事はまずありません。何故なら、関数の選択を初めてとするアルゴリズムのウェイトが比較にならないほど高いからです。ですが、型選択の迷いをなくす意義はあります。
マクロ

Sub MacroCal()
    Dim timeA As Double
    Dim timeB As Double
    
    Dim n As Integer
    Dim m As Integer
    Dim value As Integer
'    Dim value As Long
'    Dim value As Single
'    Dim value As Double
'    Dim value As Currency
    
    timeA = CDbl(Timer)
    For m = 0 To 30000 - 1
        value = 0
        For n = 0 To 10000 - 1
          value = value + 2	  ' 加算の測定はコメントを外す
‘            value = 7 * value   ' 積算・除算の測定はコメントを外す
‘            value = value / 6   ' 積算・除算の測定はコメントを外す
        Next n
    Next m
    timeB = CDbl(Timer)
    Debug.Print ((timeB - timeA));
End Sub
処理時間の結果
変数型加算(ms)積算・除算(ms)
Integer4.27712.887
Long4.30912.379
Single6.18410.383
Double6.19910.410
Currency5.37115.590
32bitのため、Integer(16bit整数型)とLong(32bit整数型)の違いはありません。
しかし、32bit環境にもかかわらずSingle(32bit単精度浮動小数点型)とDouble(64bit倍精度浮動小数点型)もほぼ同じ処理時間です。内部演算がDoubleに統一されているのでしょう。この仮定が正しいとすると、選択する変数は、LongとDoubleとなります。

開発者が速度をどこまで意識しているのか知る方法

処理速度に難ありの個所を見つけたら、何故遅いのか尋ねてみる事です。
その時、例え明確な答えが返ってきたとしても、それで十分検証しているのか判断する事はできません。何故なら応えた相手は想像で語る事ができるからです。そうではなく、どのように突き止めたかを知る事です。具体的に何をしたのかを聞けば直ぐにわかるでしょう。仮説を立て計測し実測し仮説が誤っていたら再度別の仮説を立てて検証したか、そうした取り組み姿勢を一つ一つ見るのです。
このような高速化への追求が、ユーザーの利便性を向上させるのです。