[ExcelVBA]コレクションオブジェクトをループさせる際の注意

>Blog Top Page >[ExcelVBA] Top Page >[ExcelVBA] ExcelVBA ALL

結論：コレクションオブジェクトからループで値を取り出す際にFor文を使ってはいけない。


コレクションオブジェクトに値を代入し終える
時間と取り出す際の時間を計測してみた。
比較用に配列でも同じ事をしてみた。
サンプルコード

view plainprint?

1. Option Explicit  
2. Declare Function GetTickCount Lib "kernel32" () As Long  
3.   
4. Sub SampleCode()  
5.     Dim Temp As Long  
6.     Dim BaseTime As Double, i As Long  
7.     Dim MaxNumber As Long: MaxNumber = 1000  
8.     Dim VarCollection As New Collection  
9.     Dim VarArray() As Long  
10.       
11.     '■コレクションオブジェクトに値を格納する  
12.     BaseTime = GetTickCount  
13.     For i = 0 To MaxNumber  
14.         VarCollection.Add i  
15.     Next  
16.     Debug.Print "Collection[In] :" & GetTickCount - BaseTime  
17.       
18.     BaseTime = GetTickCount  
19.     For i = 1 To MaxNumber + 1  
20.         Temp = VarCollection.Item(i)  
21.     Next  
22.     Debug.Print "Collection[In] :" & GetTickCount - BaseTime  
23.       
24.       
25.     '■配列に値を格納する  
26.     BaseTime = GetTickCount  
27.     For i = 0 To MaxNumber  
28.         ReDim Preserve VarArray(i)  
29.         VarArray(i) = i  
30.     Next  
31.     Debug.Print "Collection[In] :" & GetTickCount - BaseTime  
32.           
33.     BaseTime = GetTickCount  
34.     For i = 0 To MaxNumber  
35.         Temp = VarArray(i)  
36.         Temp = VarArray(i)  
37.     Next  
38.     Debug.Print "Collection[In] :" & GetTickCount - BaseTime  
39. End Sub  

Option Explicit
Declare Function GetTickCount Lib "kernel32" () As Long

Sub SampleCode()
    Dim Temp As Long
    Dim BaseTime As Double, i As Long
    Dim MaxNumber As Long: MaxNumber = 1000
    Dim VarCollection As New Collection
    Dim VarArray() As Long
    
    '■コレクションオブジェクトに値を格納する
    BaseTime = GetTickCount
    For i = 0 To MaxNumber
        VarCollection.Add i
    Next
    Debug.Print "Collection[In] :" & GetTickCount - BaseTime
    
    BaseTime = GetTickCount
    For i = 1 To MaxNumber + 1
        Temp = VarCollection.Item(i)
    Next
    Debug.Print "Collection[In] :" & GetTickCount - BaseTime
    
    
    '■配列に値を格納する
    BaseTime = GetTickCount
    For i = 0 To MaxNumber
        ReDim Preserve VarArray(i)
        VarArray(i) = i
    Next
    Debug.Print "Collection[In] :" & GetTickCount - BaseTime
        
    BaseTime = GetTickCount
    For i = 0 To MaxNumber
        Temp = VarArray(i)
        Temp = VarArray(i)
    Next
    Debug.Print "Collection[In] :" & GetTickCount - BaseTime
End Sub

1000～5万データで各データ10回テストした平均の時間を記載してあります。
単位は[ms]です。


たった１回でもコレクションオブジェクトに対しインデックスを
使って値を取り出しただけでこれほどの時間がかかるわけです。
例えば、１つのループ内で３回、インデックス経由で呼出を行うと
３倍時間がかかるわけです。

ただし、問題はコレクションオブジェクトに対し、インデックスを利用して
値を取り出すことにあって、キー経由で取り出す分には影響がないということです。

上記コードで、コレクションオブジェクトの値を取り出す部分を
For文からFor Each文に切り替えると以下の通りになります。
単位は[ms]です。


サンプルコード

view plainprint?

1. Option Explicit  
2. Declare Function GetTickCount Lib "kernel32" () As Long  
3.   
4. Sub SampleCode2()  
5.     Dim Temp As Long  
6.     Dim BaseTime As Double, i As Long, v As Variant  
7.     Dim MaxNumber As Long: MaxNumber = 50000  
8.     Dim VarCollection As New Collection  
9.       
10.     '■コレクションオブジェクトに値を格納する  
11.     BaseTime = GetTickCount  
12.     For i = 0 To MaxNumber  
13.         VarCollection.Add i  
14.     Next  
15.     Debug.Print "Collection[In] :" & GetTickCount - BaseTime  
16.       
17.     BaseTime = GetTickCount  
18.     For Each v In VarCollection  
19.         Temp = v  
20.     Next  
21.     Debug.Print "Collection[Out]:" & GetTickCount - BaseTime  
22. End Sub  

Option Explicit
Declare Function GetTickCount Lib "kernel32" () As Long

Sub SampleCode2()
    Dim Temp As Long
    Dim BaseTime As Double, i As Long, v As Variant
    Dim MaxNumber As Long: MaxNumber = 50000
    Dim VarCollection As New Collection
    
    '■コレクションオブジェクトに値を格納する
    BaseTime = GetTickCount
    For i = 0 To MaxNumber
        VarCollection.Add i
    Next
    Debug.Print "Collection[In] :" & GetTickCount - BaseTime
    
    BaseTime = GetTickCount
    For Each v In VarCollection
        Temp = v
    Next
    Debug.Print "Collection[Out]:" & GetTickCount - BaseTime
End Sub

つまり、コレクションオブジェクトはFor Eachで取り
出したほうがパフォーマンスがよいってことですね。

>Blog Top Page >[ExcelVBA] Top Page >[ExcelVBA] ExcelVBA ALL