How to find the type of VBA collection?

[CPPlus]

Hello everyone!

We know how to iterate a Range object because of Google (A range has "Cell" children).
We know how to iterate an array of strings because of Google (It has "String" children).

But how do we iterate something if we don't know the type of it's children? How do we find it out? I thought the Object Browser would help, but it only lists properties and not children type.

 

[shg]

See the functions IsObject(), IsArray(), TypeName(), VarType(), and the Type Of ... Is operator.

 

[CPPlus]

They are indeed useful, but they give me the type of an object that I already have. I don't even know how to begin iterating without knowing the children's type beforehand.

 

[Trebor76]

Can you just explain in very simple terms what you're trying to do?

 

[CPPlus]

I am trying to extract data from powerpivot. There is a ModelTableColumns object that i tried to iterate. Naturally its going to contain a ModelTableColumn children, so that was the easy part. Now i suppose that ModelTableColumn has children too, but whats their type? I want to see if there are children and iterate them with for each.

 

[mikerickson]

Arrays are not collections. To loop through an array of strings, one must use a Variant Data type regardless of the type of the array

Dim myArray(1 To 3) As String
Dim oneString As [U]String[/U]
    
myArray(1) = "A": myArray(2) = "B": myArray(3) = "C"

For Each oneString In myArray ' this line throws a compile error <<<<<<<<<<
    MsgBox oneString
Next oneString

' this works

Dim myArray(1 To 3) As String
Dim oneString As [U]Variant[/U] 
    
myArray(1) = "A": myArray(2) = "B": myArray(3) = "C"

For Each oneString In myArray
    MsgBox oneString
Next oneString

Also when looking at the Object Browser, if you are looking at a collection and you don't know the data type of the member of that collection you could look at the .Item property of that collection object.

 

[CPPlus]

Thanks. I had to look at the .Item property