You can't do what you are trying to do with value type variables. You
need to use object variables. The best way is to create a class to box
the Integer value type. Insert a class module named CInteger with the
following single line of code

Public Value As Integer

Then, use code like the following in a regular code module:

Sub AAA()

Dim Coll As New Collection
Dim I As CInteger

''''''''''''''''''''''''''''''''''
' Create objects with values
' and keys.
''''''''''''''''''''''''''''''''''
Set I = New CInteger
I.Value = 1
Coll.Add I, "a"

Set I = New CInteger
I.Value = 2
Coll.Add I, "b"

Set I = New CInteger
I.Value = 3
Coll.Add I, "c"

' get the value of one of the elements
Debug.Print Coll("b").Value

' change the value of one of the elements
Coll("b").Value = 2345
Debug.Print Coll("b").Value

End Sub

If you want to make Value the default member of CInteger, so that you
can use code like


I = 123
' rather than
I.Value =123

see http://www.cpearson.com/Excel/DefaultMember.aspx .

In addition, you can do what you want, without using a class to box
the Integer, by using a Dictionary object rather than a Collection
object. In VBA, go to the Tools menu, choose References, and scroll
down to and check "Microsoft Scripting RunTime". Then, use code like

Sub BBB()
Dim Dict As New Scripting.Dictionary
Dict.Add "a", 11
Dict.Add "b", 22
Dict.Add "c", 33
Debug.Print Dict("b")
Dict("b") = 2345
Debug.Print Dict("b")
End Sub

Note that the parameter order for Item and Key is reversed between a
Collection and a Dictionary. As a general coding practice, I always
use Dictionaries rather than Collection because a Dictionary has a
number of methods (such as gettign a list of keys) not available to
Collections, and Dictionaries are faster than Collections.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]






Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]


On Mon, 21 Dec 2009 10:16:02 -0800, Rich
wrote:

Dim col As New Collection
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer
i1 = 10
i2 = 20
i3 = 30
i4 = 40

col.Add i1, "1"
col.Add i2, "2"
col.Add i3, "3"
col.Add i4, "4"

col("1") = 15