Retrieve the key (strings) in a Collection

[Guest]

Hi,

If i add items to a Collection using a key String identifers, but then loop
thru items in that Collection using an index, is there any way to retrieve
the corresponding key String associated with that index, or more generally is
it possible to retrieve the key Strings at all?

Thanks

 

[Norman Jones]

Hi BW,

If i add items to a Collection using a key String identifers, but then
loop
thru items in that Collection using an index, is there any way to retrieve
the corresponding key String associated with that index, or more generally
is
it possible to retrieve the key Strings at all?

As a collection object only has has the a single (Count) property and the
Add, Remove and Items methods, this is not possible.

You could, however, use a scripting dictionary which could return the
information you seek. For example:

'==========================>>
Public Sub DictionaryDemo()

Dim MyDic ' Create a variable.
Dim MyItems As Variant
Dim myKeys As Variant
Dim Rng As Range
Dim rCell As Range
Dim i As Long

Set Rng = ActiveSheet.Range("A1:A10")

Set MyDic = CreateObject("Scripting.Dictionary")

For Each rCell In Rng.Cells
On Error Resume Next
MyDic.Add rCell.Value, rCell(1, 2).Value
On Error GoTo 0
Next rCell

MyItems = MyDic.items
myKeys = MyDic.keys

For i = 1 To MyDic.Count
Debug.Print MyItems(i - 1), myKeys(i - 1)
Next

End Sub
'<<==========================

 

[Guest]

thanks, I think i will try that.

Hi BW,


As a collection object only has has the a single (Count) property and the
Add, Remove and Items methods, this is not possible.

You could, however, use a scripting dictionary which could return the
information you seek. For example:

'==========================>>
Public Sub DictionaryDemo()

Dim MyDic ' Create a variable.
Dim MyItems As Variant
Dim myKeys As Variant
Dim Rng As Range
Dim rCell As Range
Dim i As Long

Set Rng = ActiveSheet.Range("A1:A10")

Set MyDic = CreateObject("Scripting.Dictionary")

For Each rCell In Rng.Cells
On Error Resume Next
MyDic.Add rCell.Value, rCell(1, 2).Value
On Error GoTo 0
Next rCell

MyItems = MyDic.items
myKeys = MyDic.keys

For i = 1 To MyDic.Count
Debug.Print MyItems(i - 1), myKeys(i - 1)
Next

End Sub
'<<==========================