For Each Loop

[tiredofit]

Following from this thread:

https://www.mrexcel.com/forum/excel-questions/1114405-collections.html

I was told this piece of information:

Syntax
...
For Each element In group
...
element      ... For ​
collections
, element can only be a Variant variable, a generic object variable, or any specific object variable.

​

So here's my code:

Dim Coll As Collection
    
    Set Coll = New Collection
    
    Coll.Add "Apple"
    Coll.Add "Pear"
    Coll.Add "Orange"
    
    Dim i As Collection
    For Each i In Coll
    
        Debug.Print i
    
    Next i

but it generates an object required error.

According to the article, i can be a Variant, generic object or specific object, so I have declared it here as a Collection (ie a specific object), yet it stil fails.

Why is that?

I've even tried:

Dim Coll As Collection
    
    Set Coll = New Collection
    
    Coll.Add "Apple"
    Coll.Add "Pear"
    Coll.Add "Orange"
    
    Dim i As Collection
    Set i = New Collection
    
    For Each i In Coll
        
        
        Debug.Print i
    
    Next i

but get the same error.

Thanks


<strike></strike>

<strike></strike><strike></strike>
​

 

[Yongle]

Dim i As Variant

and REMOVE line Set i = New Collection

some useful reading for you
https://excelmacromastery.com/excel-vba-collections/

 

[DanteAmor]

Maybe

Sub test()
  Dim [COLOR=#008000]Coll As New Collection[/COLOR],[B][COLOR=#0000ff] i As Variant[/COLOR][/B]
  Coll.Add "Apple"
  Coll.Add "Pear"
  Coll.Add "Orange"
  For Each i In Coll
    Debug.Print i
  Next i
End Sub

Or

Sub test_2()
  Dim [COLOR=#008000]Coll As New Collection[/COLOR], [B][COLOR=#0000ff]i As Long[/COLOR][/B]
  Coll.Add "Apple"
  Coll.Add "Pear"
  Coll.Add "Orange"
  For i = 1 To Coll.Count
    Debug.Print Coll(i)
  Next i
End Sub

 

[tiredofit]

Thanks but why can't I be declared as a collection because according to the article, it can be a variant, generic object or specific object?

 

[DanteAmor]

The article refers to the elements:
"element ... For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. "

But the group can be declared as a collection