How to track object index in object collection

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

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
 
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
 
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
 
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
 
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.
 
Back
Top