Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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. -- Cheers, Ryan "Rich" wrote: 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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 "Ryan H" wrote: 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. -- Cheers, Ryan "Rich" wrote: 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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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" wrote: 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
I guess I got busted. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Mon, 21 Dec 2009 11:44:01 -0800, Jim Thomlinson wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba collection object in Excel
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" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Collection Object | Excel Programming | |||
Collection Object | Excel Programming | |||
How to track object index in object collection | Excel Programming | |||
Adding Name object to a collection does not add an object | Excel Programming |