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

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

"Todd Huttenstine" wrote in message ...
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

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"), befo="browna"
End Sub

Alan Beban

Jim Cone wrote:

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

"Todd Huttenstine" wrote in message ...

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

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), befo=Indx
End If
End Sub

Alan Beban

Alan Beban wrote:

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"), befo="browna"
End Sub

Alan Beban

Jim Cone wrote:

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

"Todd Huttenstine" wrote in
message ...

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

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

"Alan Beban" wrote in message ...
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"), befo="browna"
End Sub
Alan Beban

Jim Cone wrote:
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

"Todd Huttenstine" wrote in message ...

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

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

Hi Stephen,

Can you post code showing the syntax?

Thanks,
Alan Beban

Stephen Bullen wrote:
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

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

Stephen Bullen wrote:

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


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

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

Stephen Bullen wrote:

Hi Alan,
. . .
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

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