How to track object index in object collection

[ExcelMonkey]

How do you return the object index within a collection. If I have a
collection of ranges and I want to loop thru the collection using a For Each
loop. What property do I track for each rng to obtain the collection number?
Or do I have to use a Counter variable?

Set colRanges = New Collection

For Each rng2 In colRanges

Next

Thanks
EM

 

[Jim Thomlinson]

Perhaps this...

Not sure if this is what you want...

Sub test()
Dim colRanges As Collection
Dim rng As Range
Dim lng As Long

Set colRanges = New Collection
With colRanges
.Add Range("A1:A10")
.Add Range("B1:B10")
.Add Range("C1:C10")
End With

For Each rng In colRanges
MsgBox rng.Address
Next rng

For lng = 1 To colRanges.Count
MsgBox colRanges(lng).Address
Next lng

End Sub

 

[JE McGimpsey]

One way:

Dim colRanges As Collection
Dim i As Long

Set colRanges = New Collection
colRanges.Add Range("A1:A10")
colRanges.Add Range("B1:B10")
colRanges.Add Range("C1:C10")
For i = 1 To colRanges.Count
If colRanges.Item(i).Address(False, False) = "B1:B10" Then _
MsgBox i
Next i

 

[Jim Thomlinson]

Just curious. Is there any advantage to using

colRanges.Item(i)
over
colRanges(i)

I use the .item when I have the object as part of a with but otherwise I
tend to omit it.

with colRanges
.item(i)
end with

 

[JE McGimpsey]

I generally prefer to be explicit.

AFAIK, it compiles the same, so it doesn't matter one way or the other
for efficiency.

However, for my own efficiency in reading code I wrote six months ago,
the more explicit I was, the better.

And for anyone who tries to maintain my code and doesn't use the "col"
convention, it's immediately obvious that the variable refers to a
collection rather than an array.

Just my US$0.02.