Collection Object Keys

[Todd Huttenstine]

Hey guys

Is there anyway you can change the value of a key that is
assigned to an item that is already in a collection?


Thanks
Todd Huttenstine

 

[Jim Cone]

Todd,

No, but you can remove a member from a collection.
Note that all members of the collection that follow the item removed are moved
downward by one position. No gaps are left in the collection.
You can then add the replacement item which will be at the end of the collection.

Regards,
Jim Cone
San Francisco, CA

 

[Alan Beban]

Well, actually you *can* do it through removal and replacement. For
example, the following will change the key of the item "yellow" from
"yellowa" to "yellowb", leaving the item "yellow" in the 4th position in
the collection:

Sub abc()
Dim x As New Collection
arr = Array("red", "blue", "green", "yellow", "brown")
On Error Resume Next
For Each Elem In arr
x.Add Item:=Elem, key:=CStr(Elem & "a")
Next
x.Remove "yellowa"
x.Add Item:="yellow", key:=CStr("yellowb"), before:="browna"
End Sub

Alan Beban

 

[Alan Beban]

Here is a more generalized procedure to change the key of a collection
member.

Just curious: Todd, in what circumstances would one want to change a key?

Sub ChangeKey(CollectionName As Collection, OldKey, NewKey)
Dim k As Variant
Dim Indx As Long
k = CollectionName(OldKey)
For Indx = 1 To CollectionName.Count
If CollectionName(Indx) = CollectionName(OldKey) Then Exit For
Next
CollectionName.Remove OldKey
If Indx = CollectionName.Count + 1 Then
CollectionName.Add Item:=k, key:=CStr(NewKey), after:=Indx - 1
Else
CollectionName.Add Item:=k, key:=CStr(NewKey), before:=Indx
End If
End Sub

Alan Beban

 

[Jim Cone]

Alan,
"Before" does work.
Thanks for pointing that out.
Regards,
Jim Cone
San Francisco, CA

 

[Stephen Bullen]

Hi Todd,

Is there anyway you can change the value of a key that is
assigned to an item that is already in a collection?

The Dictionary object, found in the Windows Scripting runtime library,
allows keys to be changed directly.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

Hi Stephen,

Can you post code showing the syntax?

Thanks,
Alan Beban

 

[Stephen Bullen]

Hi Alan,

Can you post code showing the syntax?

Sure, it's the Key property of the Dictionary object:

Sub Test()

Dim i As Integer
Dim dicItems As Dictionary

Set dicItems = New Dictionary

For i = 1 To 10
dicItems.Add "Key" & i, "Item" & i
Next

'Prints "Item4"
Debug.Print dicItems("Key4")

'Change the key
dicItems.Key("Key4") = "Hello"

'Prints "Item4"
Debug.Print dicItems("Hello")

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

Hi Alan,




Sure, it's the Key property of the Dictionary object:

Sub Test()

Dim i As Integer
Dim dicItems As Dictionary

Set dicItems = New Dictionary

For i = 1 To 10
dicItems.Add "Key" & i, "Item" & i
Next

'Prints "Item4"
Debug.Print dicItems("Key4")

'Change the key
dicItems.Key("Key4") = "Hello"

'Prints "Item4"
Debug.Print dicItems("Hello")

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks, Stephen. But suppose I'm starting with a collection rather than
a Dictionary Object; say a 10-member collection named colItems whose
keys are iKey1, iKey2...iKey10, and I want to change the key of the 4th
item to newKey. I take it the above code is not of any help(?) Is there
any generic reason to avoid Collections and use Dictionary Objects instead?

Thanks again,
Alan Beban

 

[Stephen Bullen]

Hi Alan,

But suppose I'm starting with a collection rather than
a Dictionary Object; say a 10-member collection named colItems whose
keys are iKey1, iKey2...iKey10, and I want to change the key of the 4th
item to newKey. I take it the above code is not of any help(?)

Not much, no, but if the ability to change the key is a significant
requirement (i.e. you're doing it lots in a time-critical part of the app),
you might want to think about using Dictionary objects instead of Collections

Is there
any generic reason to avoid Collections and use Dictionary Objects instead?

It's a matter of personal preference. I find Dictionaries to be generally
faster and more flexible than Collections, mainly because:
- It has an Exists property to quickly determine if an element exists
- You can easily change the keys
- It has an Items property to retrieve the entire set of items as an array
- It has a Keys property to retrieve the entire set of keys as an array

The biggest thing it can't do is insert items within the list, so it's no
good if you need a set that can be reordered. Using them also requires a
reference to the Microsoft Scripting Runtime, but I've not found that to be a
problem.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Alan Beban]

Hi Alan,
. . .


It's a matter of personal preference. I find Dictionaries to be generally
faster and more flexible than Collections, mainly because:
- It has an Exists property to quickly determine if an element exists
- You can easily change the keys
- It has an Items property to retrieve the entire set of items as an array
- It has a Keys property to retrieve the entire set of keys as an array

The biggest thing it can't do is insert items within the list, so it's no
good if you need a set that can be reordered. Using them also requires a
reference to the Microsoft Scripting Runtime, but I've not found that to be a
problem.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks very much, Stephen; I haven't worked much with collections and
until now not at all with dictionaries, so this is quite helpful.

Two other differences I noted: The Dictionary Object seems to be 0-based
(at any rate, myArray = x.Keys seems to be a 0-based array
notwithstanding an Option Base 1 Statement); and the Dictionary Object
seems to be case sensitive while the Collection Object is not, which can
in fact be a helpful distinction--See the procedure in my contemporary
posting "To declare or not to declare".

Thanks again,
Alan Beban