ExcelVBA: スピードアップするための3つの方法
2016/06/11
[ad#top-1]
職業プログラマーではない一般人が最も多く触れているプログラミング言語は、おそらくExcel上で走らせるマクロだと思います。言語仕様はイマイチですが、Excel自体はMicroSoft Officeの中で一番使われていることもあって、業務効率を上げるために多くの人がマクロに手を出していると思います。
ただ、JavaやC#などのオブジェクト指向言語に触れてきた僕としては、Excelマクロ(VBA)ってVisual Basic特有の曖昧仕様がどうも慣れない上に、速度が遅いということが少々気になります。あんまり速度を気にせずに作ると処理に異様な時間をかけてしまうケースがあり、便利な反面、かえって業務効率を落としてしまうことも稀にあったりします。
そこで、Excelマクロを扱う上で、スピードアップするためのテクニックを3つ紹介したいと思います。
ベンチマークとして以下のプログラムを即興で作りました。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 |
対策1 変数宣言する 処理時間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 |
いかがでしたでしょうか。対策1~3をすべて盛り込むと、当初のプログラムと比較してナント92%もの時間を短縮することができました。
ぜひ試してみてください。
[ad#ad-1]
スポンサーリンク