Looping for speed

[tiredofit]

I read that purely for speed purposes, you should use a For Each loop when looping through a collection.

For arrays, use a For Next loop.

What about for a dictionary? Which is faster?

Thanks

 

[offthelip]

Which type of loop you do doens't make that much difference, the commonest reason for slow loops is multiple access to the worksheet in a loop. See my signature

 

[Kyle123]

You can't use a For Each loop on a dictionary. You can only access the members through the key.

 

[Fluff]

@Kyle123
Not sure if I've misunderstood what you're saying, but you can use a for each loop on dictionary items.
Along the lines of

      For Each Itm In Dic.Items
         Ws1.Range("A" & Itm.Row).EntireRow.Insert
         Itm.EntireRow.Copy Ws1.Range("A" & Itm.Row)
      Next Itm

 

[Kyle123]

@Fluff

Items returns an array, your code is simply looping through the array returned by the items property.


It's shorthand for:

Sub tes()

    Dim dic As Object
    Set dic = CreateObject("scripting.dictionary")
    
    dic.Add "a", 1
    dic.Add "b", 2
    dic.Add "c", 3
    
    Dim items() As Variant
    items = dic.items
    
    For Each itm In items
        Debug.Print itm
    Next itm

End Sub

So a dictionary is not like a collection, one cannot enumerate its values as one can in a collection

 

[tiredofit]

@Kyle123
Not sure if I've misunderstood what you're saying, but you can use a for each loop on dictionary items.
Along the lines of

      For Each Itm In Dic.Items
         Ws1.Range("A" & Itm.Row).EntireRow.Insert
         Itm.EntireRow.Copy Ws1.Range("A" & Itm.Row)
      Next Itm

@Fluff
Items returns an array, your code is simply looping through the array returned by the items property.

It's shorthand for:

Sub tes()
    Dim dic As Object
    Set dic = CreateObject("scripting.dictionary")
    
    dic.Add "a", 1
    dic.Add "b", 2
    dic.Add "c", 3
    
    Dim items() As Variant
    items = dic.items
    
    For Each itm In items
        Debug.Print itm
    Next itm
End Sub

So a dictionary is not like a collection, one cannot enumerate its values as one can in a collection

Gents, I'm confused!

This code is taken from another thread I posted:

https://www.mrexcel.com/forum/excel-questions/1055806-pass-more-than-one-argument.html

Is this NOT using a For Each loop, looping through a dictionary?

Dim DIC As Scripting.Dictionary
    Set DIC = New Scripting.Dictionary
    
    DIC.Add Key:="Apple", Item:="Large"
    DIC.Add Key:="Orange", Item:="Small"
    
    Dim DICElement As Variant
    
    For Each DICElement In DIC
    
        Call SomeSub(Arg1:=DICElement, Arg2:=DIC.Item(DICElement))
    
    Next DICElement

 

[Kyle123]

No, it's not. What you have written is shorthand for:

    Dim DIC As Scripting.Dictionary
    Set DIC = New Scripting.Dictionary
    
    Dim keys() As Variant
    
    DIC.Add Key:="Apple", Item:="Large"
    DIC.Add Key:="Orange", Item:="Small"
    
    keys = DIC.keys
    
    Dim DICElement As Variant
    
    For Each DICElement In keys
    
        Call somesub(Arg1:=DICElement, Arg2:=DIC.Item(DICElement))
    
    Next DICElement

The Keys is implicit as it's the default member. So your code is looping through the array of keys returned by the key property.

You are then accessing the dictionary's values using the key.

 

[tiredofit]

No, it's not. What you have written is shorthand for:

    Dim DIC As Scripting.Dictionary
    Set DIC = New Scripting.Dictionary
    
    Dim keys() As Variant
    
    DIC.Add Key:="Apple", Item:="Large"
    DIC.Add Key:="Orange", Item:="Small"
    
    keys = DIC.keys
    
    Dim DICElement As Variant
    
    For Each DICElement In keys
    
        Call somesub(Arg1:=DICElement, Arg2:=DIC.Item(DICElement))
    
    Next DICElement

The Keys is implicit as it's the default member. So your code is looping through the array of keys returned by the key property.

You are then accessing the dictionary's values using the key.

Thanks for the explanation.

 

[Kyle123]

No problem