VBAのパフォーマンス調査に関する報告書

前の会社で書いた報告書を見つけたので、サクッとネットの海へ放流。

バリバリ報告書っぽい体裁になってたので、ふんわり意訳版。

概要

ExcelVBAのパフォーマンス調査の結果＆考察。

測定準備

動作環境（やばい古い）
- CPU：Pentium(R)4 2.93GHz
- メモリ：1.49GB
- OS：Windows XP Professional Version 2002 Service Pack 3
- Excel：Excel 2003（Office Professional Edition 2003）
測定方法
- if文を用いて1つのコードにつき10 回の測定を行い、平均値も考慮して結果を比較する
- 測定を正確に行うため、以下のWindows APIを使用して時間を測定するためのクラスを用いた
  - QueryPerformanceCounter：コンピュータが起動してからのクロック数を求める
  - QueryPerformanceFrequency：クロックの周波数を求める
- 測定に使用したプロシージャと時間測定用クラスのソースコードは後述
測定内容
- 詳細は「測定結果・考察」に後述
  - 基本的に、以下サイトを参考に作成したコード、もしくは同一コードによる追試
    - Office TANAKA - Excel VBA高速化テクニック[目次]

測定結果・考察

画面の更新（画面更新の有無による処理時間の違いを調査する）

測定用コード

' // ケースA：画面更新を行う
Dim i As Integer, j As Integer
Application.ScreenUpdating = True
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 54, 17).Select
    Selection.Value = j
  Next j
Next i

' // ケースB：画面更新を行わない
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 54, 18).Select
    Selection.Value = j
  Next j
Next i

測定結果・考察
- 詳細な結果は下の表のとおり
- 画面の更新を行わない場合は、更新を行う場合の約5分の1の時間で処理
- 画面の更新を止めておくと処理速度の大幅な上昇が期待できる

試行	A	B	(B/A)*100
1	492.7216	97.69845	19.82833
2	488.2042	97.89708	20.05248
3	492.0748	95.996	19.50841
4	492.6925	96.52847	19.59203
5	489.2806	95.87084	19.59425
6	488.0673	96.76733	19.82663
7	487.2351	96.22284	19.74875
8	489.0856	96.83409	19.79901
9	491.049	96.30749	19.6126
10	487.2404	95.94348	19.6912
平均	489.7651	96.60661	19.72537

Selectメソッドの使用（セルの選択の有無による処理時間の違いを調査する）

測定用コード

' // ケースC：Selectを使用する
Dim i As Integer, j As Integer
For i = 1 To 100
  For j = 1 To 10
    Cells(j + 53, 4).Select
    Selection.Value = j
  Next j
Next i

' // ケースD：Selectを使用しない
Dim k As Integer, l As Integer
For k = 1 To 100
  For l = 1 To 10
    Cells(l + 53, 5).Value = l
  Next l
Next k

測定結果・考察
- 詳細な結果は下の表のとおり
- Selectを使用しない場合は使用する場合に比べて17％ほどの時間で処理
- ただしケースDは実行時間の誤差が大きく、期待通りの時間短縮にならないこともある

試行	C	D	(D/C)*100
1	465.02167	78.934334	16.974334
2	457.84478	80.925928	17.675407
3	459.8509	78.987972	17.176866
4	459.09326	80.210753	17.47156
5	459.11478	79.451159	17.305293
6	458.91699	78.998029	17.214013
7	460.53646	78.367223	17.016508
8	459.17652	79.591121	17.333448
9	461.22426	79.251134	17.182776
10	458.37055	79.163693	17.270676
平均	459.91502	79.388135	17.262088

Value プロパティの記述（Value プロパティを省略する場合と記述する場合の処理時間の違いを調査する）

測定用コード

' // ケースE：Valueプロパティを省略する
Dim i As Long
For i = 1 To 10000
  Range("F54") = i
Next i

' // ケースF：Valueプロパティを記述する
Dim i As Long
For i = 1 To 10000
  Range("F54").Value = i
Next i

測定結果・考察
- 詳細な結果は下の表のとおり
- 処理時間はほぼ同じで、Value プロパティの省略は実行時間に影響を与えないと考えてよい

試行	E	F	(F/E)*100
1	2326.4577	2345.0455	100.79898
2	2324.4613	2337.1275	100.54491
3	2329.7536	2335.5049	100.24686
4	2347.5447	2331.6855	99.324431
5	2339.4532	2349.2494	100.41874
6	2326.0666	2335.1348	100.38985
7	2323.0444	2329.5581	100.28039
8	2345.9035	2330.3953	99.338928
9	2329.1851	2362.5732	101.43346
10	2330.7468	2335.5527	100.2062
平均	2332.2617	2339.1827	100.29827

セルの指定方法（セルの指定方法による処理時間の違いを調査する）

測定用コード

' // ケースG：Rangeプロパティ
Dim i As Long
For i = 1 To 10000
  Range("F56") = Rnd
Next i

' // ケースH：Cellsプロパティ
Dim i As Long
For i = 1 To 10000
  Cells(56, 6) = Rnd
Next i

測定結果・考察
- 詳細な結果は下の表のとおり
- RangeプロパティよりもCellsプロパティを使用した場合の方が早く処理を終えている
  - ただし差は5％ほど

試行	G	H	(H/G)*100
1	2421.3896	2277.0321	94.038239
2	2389.8699	2291.2066	95.871601
3	2387.951	2282.2504	95.573586
4	2404.8618	2299.6845	95.626471
5	2381.5926	2285.0334	95.945604
6	2388.6293	2277.1433	95.332639
7	2388.7131	2277.4294	95.34127
8	2397.8883	2279.875	95.078448
9	2382.8615	2295.9938	96.354478
10	2383.6088	2285.588	95.887713
平均	2392.7366	2285.1236	95.505005

セルの参照に配列を使用する（配列に移したデータを参照するか、セルを直接参照するかの違いを調査する）

測定用コード

' // 測定前にC81～CX10080 へ0～100 の整数の乱数を入力しておく

' // ケースI：セルを直接参照する
Dim i As Long, j As Long, buf As Long
For i = 1 To 10000
  For j = 1 To 100
    buf = Cells(i + 80, j + 2)
  Next j
Next i

' // ケースJ：セルを配列に格納してから配列を参照する
Dim i As Long, j As Long, buf As Long, C As Variant
C = Range("C81:CX10080")
For i = 1 To 10000
  For j = 1 To 100
    buf = C(i, j)
  Next j
Next i

測定結果・考察
- 詳細な結果は下の表のとおり
- セルを配列に格納してからデータを参照した方が、約7％という短時間で処理を終えている
- 多くのデータを扱う場合は特に、配列に格納し使用すると、処理速度の大幅な上昇が期待できる

試行	I	J	(J/I)*100
1	9563.1584	645.33789	6.7481668
2	9568.8748	661.04072	6.9082388
3	9557.432	659.11617	6.8963731
4	9566.9553	660.18083	6.9006367
5	9555.9391	658.64293	6.8924982
6	9569.343	659.31145	6.8898298
7	9562.0421	661.05944	6.9133709
8	9554.2299	658.96671	6.8971201
9	9553.607	663.79163	6.9480734
10	9585.2875	668.88389	6.9782351
平均	9563.6869	659.63317	6.8972543

使用した測定プログラムのソースコード

メインプロシージャ

Public Sub SpeedComp ()
  Dim cnt As Long
  Dim myQPT As New QueryPerformanceTimer '*** 測定クラスのインスタンス化
  For cnt = 1 To 10 '*** 10 回試行するため
    Call myQPT.BeginTimer '*** 測定開始
    '**********************************
    '*** 時間を計りたい処理のコード ***
    '**********************************
    Cells(cnt, 1).Value = myQPT.EndTimer '*** 測定終了、測定結果を格納
  Next
End Sub

測定用クラス

'! @class QueryPerformanceTimer
'! @brief 計測を行うためのタイマ
'! @date 2009/07/02

'* WindowsAPI の関数を使用のため
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Boolean
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Boolean

'* 変数宣言
Private m_curFreq As Currency '* 周波数
Private m_curStartTime As Currency '* 開始時刻

'*
'* メソッド：計測の可否を調べ、可能なら開始時間を取得する
'* @return 時間取得に成功ならTrue、失敗ならFalse
'*
Public Function BeginTimer() As Boolean
  '* 周波数未取得のとき
  If m_curFreq = 0 Then
    '* 高分解能パフォーマンスカウンタの周波数取得に失敗時False を返す
    If QueryPerformanceFrequency(m_curFreq) = False Then
      BeginTimer = False
    End If
  End If
  '* 開始時間を取得し、True を返す
  Call QueryPerformanceCounter(m_curStartTime)
  BeginTimer = True
End Function

'*
'* メソッド：計測開始時と終了時の差をとり、ミリ秒換算する
'* @return 計測時間（単位：ミリ秒）計測できていない場合は-1
'*
Public Function EndTimer() As Double
  '* 変数宣言
  Dim curEndTime As Currency '* 終了時刻
  '* 周波数が取得できている場合
  If m_curFreq <> 0 Then
    '* 終了時間を取得
    Call QueryPerformanceCounter(curEndTime)
    '* 終了時間と開始時間の差をクロック周波数で除算し、ミリ秒換算する
    EndTimer = ((curEndTime - m_curStartTime) / m_curFreq) * 1000
    '* 周波数が取得できていない場合は-1 を返す
  Else
    EndTimer = -1
  End If
End Function

'*
'* 起動時の処理
'*
Private Sub Class_Initialize()
  '* Private 変数の初期化
  m_curFreq = 0
  m_curStartTime = 0
End Sub

参考文献

Microsoft サポートオンライン（QueryPerformanceCounter について）
- Acquiring high-resolution time stamps (Windows)
Office TANAKA
- Office TANAKA - Excel VBA高速化テクニック[目次]
MSDN（ScreenUpdating プロパティ、Select メソッド、Value プロパティについて）

アナログCPU：5108843109

ゲームと音楽とプログラミング（酒と女とロックンロールのノリで）

VBAのパフォーマンス調査に関する報告書

概要

測定準備

測定結果・考察

画面の更新（画面更新の有無による処理時間の違いを調査する）

Selectメソッドの使用（セルの選択の有無による処理時間の違いを調査する）

Value プロパティの記述（Value プロパティを省略する場合と記述する場合の処理時間の違いを調査する）

セルの指定方法（セルの指定方法による処理時間の違いを調査する）

セルの参照に配列を使用する（配列に移したデータを参照するか、セルを直接参照するかの違いを調査する）

使用した測定プログラムのソースコード

メインプロシージャ

測定用クラス

参考文献