【エクセルVBA】重複排除処理が速いのはどっち？Dictionaryオブジェクト vs 複数ループ

Sub TestList()
Dim i As Long
Dim j As Long
Dim flgFind As Long
Dim maxRow  As Long
Dim maxRow_l As Long
Dim strMat, lngNum
Dim Start, Finish

Application.ScreenUpdating = False
Start = Time

With ActiveSheet
    maxRow = .Cells(Rows.Count, 2).End(xlUp).Row
    maxRow_l = 1
    
    For i = 2 To maxRow
        flgFind = 0
        For j = 1 To maxRow_l
            strMat = .Cells(i, 2).Value
            lngNum = .Cells(i, 3).Value
        
            If strMat = .Cells(j, 6).Value Then
                .Cells(j, 7).Value = .Cells(j, 7).Value + lngNum
                flgFind = 1
                Exit For
            End If
        Next j
        
        If flgFind = 0 Then
            .Cells(j, 6).Value = strMat
            .Cells(j, 7).Value = lngNum
            maxRow_l = maxRow_l + 1
        End If
    Next i
    
End With

Finish = Time

Application.ScreenUpdating = True
MsgBox "処理時間は" & Format(Finish - Start, "nn分ss秒") & "です"
End Sub

コードは前回の記事のものと全く同じですが、今回は処理時間の計測ということで、処理完了時にメッセージボックスで処理時間を表示するようにしました。

ついでに、画面の更新処理を非表示にするApplication.ScreenUpdating = Falseも加えてあります。

Sub ListWithDictionary()
Dim i As Long
Dim j As Long
Dim maxRow As Long
Dim dic As Dictionary
Dim strMat, lngNum
Dim Start As Variant
Dim Finish As Variant

Application.ScreenUpdating = False
Start = Time
Set dic = New Dictionary

j = 2 'リスト書き出し開始行

With ActiveSheet

    maxRow = .Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To maxRow
        strMat = .Cells(i, 2).Value
        lngNum = .Cells(i, 3).Value
        
        If dic.Exists(strMat) Then
            .Cells(dic.Item(strMat), 7).Value = .Cells(dic.Item(strMat), 7).Value + lngNum
        Else
            dic.Add (.Cells(i, 2).Value), j
            .Cells(j, 6).Value = strMat
            .Cells(j, 7).Value = lngNum
            j = j + 1
        End If
    Next i
    
End With

Finish = Time
Application.ScreenUpdating = True
MsgBox "処理時間は" & Format(Finish - Start, "nn分ss秒") & "です"

End Sub

計測方法