1、概要
Excel VBA
はプログラムの書き方次第で実行速度が何倍も変わってきます。科学技術計算で使う場合は、シートから大量のデータを読み込み、計算してシートへの書き出すという処理が必要ですが、自分が計算したいこと以外の実行速度がボトルネックにならないようなコードの書き方について説明します。
また、遅いコードと早いコードを書いて比較測定したいと思います。
1-1、テスト環境
・ CPU : Intel Core i7 860 @ 2.8GHz
・ RAM : 12GByte
・ OS : Windows7 Professional SP1、64bit版
・ Office : Office 2010、32bit版
2、プロファイラの準備
・プログラムの実行時間を計測するツールは「プロファイラ」と呼びますが、今回 Windows API の
timeGetTime 関数を使い、コードの処理時間を測定します。timeGetTime は Windows
起動時からの経過時間をミリ秒単位で返してくれる関数です。
この関数を測定したいコードの前後で呼び出して差分を取った値をコードの実行時間とします。また Windows のバックグラウンド処理の状況で計測値が毎回変わるので、何回か同じ測定をして出現頻度が高い数値を記録しました。
具体的な時間を計測するコードは下記の通りです。
Option Explicit
'関数宣言
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub sample()
Dim m_start As Long, m_end As Long
' 実行前の時間取得
m_start = timeGetTime()
' ここの部分に測定したいコードを書く
' 実行後の時間取得
m_end = timeGetTime()
' 結果表示
MsgBox "実行に " & CStr((m_end - m_start) * 0.001) & " [秒] かかりました。", vbOKOnly, "時間計測結果"
End Sub
3、シートから高速読込み
・Excel VBA では、行や列の範囲を表す Range オブジェクトを使ってセルにアクセスしますが、このオブジェクトにアクセスする回数をなるべく少なくなるようにプログラムすると実行速度が速くなります。
あらかじめ Excel シートの B3 列に、1～100,000 で列範囲に連続データを作成しておき、そのデータを
VBA で 100,000ポイント分配列変数へ代入したときの速度を比較します。
3-1、遅いコードの例
・毎回 Range オブジェクトの Value プロパティを使ってシートのセルにアクセスする。
Sub sample()
Dim i As Long
Dim data(100000) As Double
' ここの部分に測定したいコードを書く
For i = 0 To 99999
data(i) = Cells(3 + i, 2).Value
Next i
End Sub
3-2、早いコードの例
・ Variant 型の配列変数を使って１回だけ Range オブジェクトに範囲アクセスして、以後その変数からセルのデータを取得する。
Sub sample()
Dim i As Long
Dim data(100000) As Double
Dim rd As Variant
' ここの部分に測定したいコードを書く
rd = Range(Cells(3, 2), Cells(3 + 99999, 2))
For i = 0 To 99999
data(i) = rd(i + 1, 1)
Next i
End Sub
3-3、測定結果
早いコードは遅いコードに比べ 9.62 倍高速。
遅いコードの実行時間
早いコードの実行時間
4、シートへ高速書き込み
・シートからの読み込みと同様、行や列の範囲を表す Range オブジェクトを使ってセルにアクセスするので、このオブジェクトにアクセスする回数をなるべく少なくなるようにプログラムすると実行速度が速くなります。
まっさらな Excel
のシートに、Double 型で昇順に並んだ 1～100,000 の数値を
100,000ポイント分配列変数に代入し、そのデータをシートへ書き込んだときの速度を比較します。
4-1、遅いコードの例
・毎回 Range オブジェクトの Value プロパティを使ってセルに書き込む。
Sub sample()
Dim i As Long
Dim data(99999, 0) As Double
' 書き込みデータ生成
For i = 0 To 99999
data(i, 0) = i + 1
Next i
' 実行前の時間取得 → ここから時間計測
m_start = timeGetTime()
' ここの部分に測定したいコードを書く
For i = 0 To 99999
Cells(3 + i, 2).Value = data(i, 0)
Next i
End Sub
4-2、早いコードの例
・配列変数を使って、１回だけ Range オブジェクトに範囲を指定して書き込む。
ポイントは配列変数の確保を、変数(行範囲,列範囲) の２次元配列で確保しておくと、そのままシートへ書き込むことが出来ます。
Sub sample()
Dim i As Long
Dim data(99999, 0) As Double
' 書き込みデータ生成
For i = 0 To 99999
data(i, 0) = i + 1
Next i
' 実行前の時間取得 → ここから時間計測
m_start = timeGetTime()
' ここの部分に測定したいコードを書く
Range(Cells(3, 2), Cells(3 + 99999, 2)) = data()
End Sub
4-3、測定結果
早いコードは遅いコードに比べ 18.9 倍高速。
遅いコードの実行時間
早いコードの実行時間
5、文字列連結の高速化
・文字列連結を繰り返す場合、普通に & 演算子を使って文字列連結すると、処理速度が遅くなる場合があります。この場合文字列連結は Mid$ 関数を使うと処理速度が速くなります。
0,1,2,3 ... と CSV 形式風に、10,000 回文字列連結を繰り返したときの & 演算子と Mid$ 演算子の処理速度を比較します。
5-1、遅いコードの例
・普通に & 演算子を使って文字列を連結する。
Sub sample()
Dim i As Long
Dim data As String
' ここの部分に測定したいコードを書く
data = ""
For i = 0 To 9999
data = data & CStr(i) & ","
Next i
data = Left(data, Len(data) - 1)
End Sub
5-2、早いコードの例
・ Mid$ を使って文字列を連結する。
Sub sample()
Dim i As Long, j As Long
Dim data As String
' ここの部分に測定したいコードを書く
data = Space(60000)
j = 0
For i = 0 To 9999
Mid$(data, j + 1) = CStr(i)
j = j + Len(CStr(i)) + 1
Mid$(data, j) = ","
Next i
data = Trim(data)
data = Left(data, Len(data) - 1)
End Sub
5-3、測定結果
早いコードは遅いコードに比べ 19.5 倍高速。
& 演算子を使った場合は、毎回変数領域を確保し直すので処理速度が遅くなります。対する Mid$ 関数の場合は、あらかじめ変数領域を
Space 関数で大目に確保し、連結する文字列の長さを Len 関数で調べながら文字列を挿入していきます。
ただし Mid$ 関数が高速に動く場面は、連結した文字列が長くなる場合に有効で、例えば複素形式の x+yi
程度の短い文字列連結では、文字列の長さを調べる Len 関数のオーバーヘッドで、& 演算子とたいして変わらない処理速度になったりもします。
遅いコードの実行時間
遅いコードのメモリ確保(イメージ図)
早いコードの実行時間
早いコードのメモリ確保(イメージ図)
6、その他高速化の定石
・画面の更新を止める
処理速度を早くしたいコードの前後に Application.ScreenUpdating = False、
Application.ScreenUpdating = True を挟むと、画面更新しなくなるので速度が速くなります。
なお一時的に画面更新をしたい場面では、DoEvents 関数を挿入してください。
・再計算を止める
処理速度を早くしたいコードの前後に Application.Calculation = xlCalculationManual、
Application.Calculation = xlCalculationAutomatic を挟むと、再計算を一時的に止められるので速度が速くなります。
シートに大量の計算式が入っている重たいシートなどでは有効ですので試してみてください。
・アルゴリズムを見直す
実はこれが一番有効です、同じことさせるにも、より計算回数を減らす工夫をしてください。
7、参考文献
・日経ソフトウェア 2001年10月号