vba collection object in Excel

R

Rich

I haven't worked with vba collection objects for a while and have sort of
forgotten some of the properties. Here is a code sample using a collection
object and the error I am getting when I try to perform a particular
operation: I am basically assigning values to the given Integer vars below
and then storing these vars (or their values) in the collection object col.
Then I want to change the value of one of the collection items but I get an
error -- as depicted beolw. Is there a way to get around this? Or should I
create class objects with properties instead and create my own collection
class?

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 <--try to change value of this col itme - error here "object
required"
 
C

Chip Pearson

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]
 
R

Ryan H

You are trying to store Inetger Data Types as Objects in a Collection, no
good. Why don't you just put your integers into an Array instead.

Dim MyArray(3) As Variant
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer

i1 = 10
i2 = 20
i3 = 30
i4 = 40

MyArray = Array(i1, i2, i3, i4)

or

MyArray(0) = 10
MyArray(1) = 20
MyArray(2) = 30
MyArray(3) = 40

Hope this helps! If so, click "YES" below.
 
R

Rich

Thanks for the reply. I did try the variant array thing, but what I am
trying to do is to change (reassign) the value of the var through the
collection object:

ex:

Dim col As New Collection, i1 As Integer, i2 As Integer
i1 = 10
12 = 20
col.Add i1, "1"
col.Add i2, "2"

col1("1") = 15
debug.print i1 '--i1 value would be changed from 10 to 15 -- isn't there a
pass by reference thing in VBA where you can change the value of a var this
way?

I want i1 to now contain the value of 15 instead of 10. Is there a way to
achieve this?

Incase you care -- I have a I am creating a graph of dots based on numbers
from a row of cells (in Excel 2010 they have spark lines or something like
that which sort of does the same thing to a degree). If some of my dots
(Excel 2003) are like way off the boundaries that I specify (the boundaries
would be a vertical difference of like 60 units between dots -- if 4 dots are
further than 60 units from the average of the other dots - I exclude those
dots), I want to not show those dots. But a range of dots (12 dots total)
may have 4 dots that are close together (vertically) and then 8 more which
are at a significantly different level (vertically) but are close together.
I want to display the 8 dots that are close together (rather than squishing
together the dots - keep the perspective to actual perspective unlike the
sparklines).

.. . . .


. . . . . . . . <--display these dots
 
J

Jim Thomlinson

I notice you are using auto instancing variables??? Any reason?

Dim coll as Collection

Set Coll = New Collection

You should read this guy's web site he is a genius...
http://www.cpearson.com/excel/DeclaringVariables.aspx
--
HTH...

Jim Thomlinson


Chip Pearson said:
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]


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
Click to expand...
.
Click to expand...
 
R

Rich

Thank you for your reply. This was helpful in giving me some ideas on what
kind of objects to use. But I sort of figured out what I was trying to do.
Here is a sample, and now I could probably encapsulate this in a class object

Sub abc()
Dim i As Integer
i = 10
Debug.Print abd(i)
Debug.Print i
End Sub

Function abd(ByRef j As Integer) As Integer
j = j + 10
abd = j
End Function

this changes the value of the variable i from the original value of 10 to 20.

Chip Pearson said:
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]


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
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Displaing result in Entire worksheet 4
Can this be done with HIDE - UNHIDE ? 4
I need some changes in a code 5
Change a Macro - Copy in Columns instead of copy in Rows 7
Handle a value from a Range instead of value of a Cell 8
UnProtecting Many Worksheets in One Workbook 3
Question about Worksheet Security 4
type mismatch error when passing collection byRef 2

Top