ExcelVBA: スピードアップするための３つの方法

2016/01/13 2016/06/11

[ad#top-1]

職業プログラマーではない一般人が最も多く触れているプログラミング言語は、おそらくExcel上で走らせるマクロだと思います。言語仕様はイマイチですが、Excel自体はMicroSoft Officeの中で一番使われていることもあって、業務効率を上げるために多くの人がマクロに手を出していると思います。

ただ、JavaやC#などのオブジェクト指向言語に触れてきた僕としては、Excelマクロ(VBA)ってVisual Basic特有の曖昧仕様がどうも慣れない上に、速度が遅いということが少々気になります。あんまり速度を気にせずに作ると処理に異様な時間をかけてしまうケースがあり、便利な反面、かえって業務効率を落としてしまうことも稀にあったりします。

そこで、Excelマクロを扱う上で、スピードアップするためのテクニックを３つ紹介したいと思います。

ベンチマークとして以下のプログラムを即興で作りました。100,000回の処理を繰り返した後、処理時間を指定のセルに記録するマクロです。ここからどう工夫すれば処理時間が短くなるか色々試してみたので紹介します。

Sub testA()

   dt1 = Now()

   For i = 0 To 100000

      j = Application.RoundDown((i + 5) / 3, 0)

      Range(“C3”) = j

   Next i

   dt2 = Now()

   Range(“C4”) = (dt2 – dt1) * 24 * 60 * 60

End Sub

対策１　変数宣言する　処理時間35%削減

時間変数のdt1, dt2や、i, jなどを宣言せずに使っていましたが、これをすべて変数宣言すると速度が向上します。以下がそのプログラムコードです。僕のパソコン上で走らせるとtestAの比べ処理時間が35%削減できました。

Sub testB()

   Dim i As Long

   Dim j As Double

   Dim dt1 As Date

   Dim dt2 As Date

   dt1 = Now()

   For i = 0 To 100000

      j = Application.RoundDown((i + 5) / 3, 0)

      Range(“D3”) = j

   Next i

   dt2 = Now()

   Range(“D4”) = (dt2 – dt1) * 24 * 60 * 60

End Sub

対策2　Applicationオブジェクトを使わない　処理時間6%削減

Application.Roundという機能を使っていますが、ApplicationってExcelシートの関数をマクロ上で使えるオブジェクトで、これが処理を遅くしている原因にもなっています。特にループ内で使うとかなりパフォーマンスを落とすんです。これを別の形で計算すれば地味にスピードアップできたりします。以下がその例。処理時間6%の削減ができました。

Sub testC()

   Dim i As Long

   Dim j As Double

   Dim dt1 As Date

   Dim dt2 As Date

   dt1 = Now()

   For i = 0 To 100000

      j = Int((i + 5) / 3)

      Range(“E3”) = j　‘Application.RowndDownの代わり

   Next i

   dt2 = Now()

   Range(“E4”) = (dt2 – dt1) * 24 * 60 * 60

End Sub

対策3　画面描画を止める　処理時間87%削減

今回のケースではループ内でセルへの書き込みを行っています。Excelマクロではセルの読み書きって結構遅くする要因になっているんです。でも、どうしても読み書きしたいと思ったら、画面の動きをロックしてしまえばいいんです。具体的には、Application.ScreenUpdating = False とやれば描画が止められます。処理が終わったら、Application.ScreenUpdating = True とやって元に戻します。この方法はかなり処理速度を向上させることができ、今回のケースでいけば、なんと87%も削減することができました。

Sub testD()

   Dim i As Long

   Dim j As Double

   Dim dt1 As Date

   Dim dt2 As Date

   dt1 = Now()

   Application.ScreenUpdating = False

   For i = 0 To 100000

      j = Int((i + 5) / 3)

      Range(“F3”) = j

   Next i

   dt2 = Now()

   Range(“F4”) = (dt2 – dt1) * 24 * 60 * 60

   Application.ScreenUpdating = True

End Sub