PC Review


Reply
Thread Tools Rate Thread

Array vs. Collection

 
 
RyanH
Guest
Posts: n/a
 
      18th Sep 2008
I have a Userform that when it is initialized I need to fill an Array or
Collection with 1 to 8 Cell Values. Is there a difference between Array and
Collection? Differences other than Array's hold values, Collection holds
objects.
--
Cheers,
Ryan
 
Reply With Quote
 
 
 
 
Jim Thomlinson
Guest
Posts: n/a
 
      18th Sep 2008
Collections and Dictionary objects have a key associated with them. You can
directly access an item in the collection (or dictionary) if you know the
key. To find a specific item in an array, unless you happen to know the index
number you need to traverse the array to find the value.

Think of it like worksheets. You can access a worksheet if you know the tab
name. You do not need to know the index number. The tab name is the key value
for the collection.

--
HTH...

Jim Thomlinson


"RyanH" wrote:

> I have a Userform that when it is initialized I need to fill an Array or
> Collection with 1 to 8 Cell Values. Is there a difference between Array and
> Collection? Differences other than Array's hold values, Collection holds
> objects.
> --
> Cheers,
> Ryan

 
Reply With Quote
 
 
 
 
JP
Guest
Posts: n/a
 
      18th Sep 2008
On top of what Jim stated, a Collection object can also be used to
hold a unique list of items (similar to what you can do with Advanced
Filter to eliminate duplicates in a list).

See http://j-walk.com/ss/Excel/tips/tip47.htm for more info.

--JP


On Sep 18, 2:14*pm, RyanH <(E-Mail Removed)> wrote:
> I have a Userform that when it is initialized I need to fill an Array or
> Collection with 1 to 8 Cell Values. *Is there a difference between Array and
> Collection? *Differences other than Array's hold values, Collection holds
> objects.
> --
> Cheers,
> Ryan


 
Reply With Quote
 
Jim Thomlinson
Guest
Posts: n/a
 
      18th Sep 2008
Note to the RyanH... You can also late bind to get a dictionary object with

dim dic as object
set dic = createobject("scripting.dictionary")

While you loose the intellisence doing this your code becomes portable
without having to set library references at design time... Develope with the
reference in place and then modify to late binding... There is a slight
performance hit to late binding but it is pretty small.
--
HTH...

Jim Thomlinson


"Chip Pearson" wrote:

> An array can be used to simply store a series of related values. However,
> the only way of getting a value from an array is to use the positional index
> into the array.
>
> For example, consider the following code
>
> '''''''''''' Array Example
> Dim Arr(1 To 4)
> Arr(1) = "a"
> Arr(2) = "b"
> Arr(3) = "c"
> Arr(4) = "d"
> Debug.Print Arr(3) ' displays "c"
>
> Here, the only way to get an element from the array is to use the index
> number (3 in this example).
>
> '''''''''''''' Collection Example
> Dim Coll As Collection
> Set Coll = New Collection
> Coll.Add 11, "My1" ' value is first parameter, key is second parameter. key
> must be a (unique) string
> Coll.Add 12, "My2"
> Coll.Add 13, "My3"
> Coll.Add 14, "My4"
> ' access by array index only
> Debug.Print Coll(3) ' displays 13
> Debug.Print Coll("My3") 'displays 13
>
> Here, you can access an element in the collection by its sequential index
> number (3 in this example) or by the unique key assigned to the item in the
> collection ("My3" in this example). Sadly, given a collection, there is no
> way to get a list of the keys (e.g., get the list "My1", "My2",....).
> However, the Dictionary object gives you all the functionality of a
> Collection but also allows you to retreive a list of the keys. For example,
>
> Dim Dict As Dictionary
> Dim S() As Variant
> Dim N As Long
> Set Dict = New Dictionary
> Dict.Add "My1", 1 ' order of key and valuie is reversed re: Collection.
> Key first, value second. Key must be string.
> Dict.Add "My2", 2
> Dict.Add "My3", 3
>
> ' access by positional index
> Debug.Print Dict(2) ' returns 2
> ' access by key value
> Debug.Print Dict("My2") ' returns 2
> ' get all keys into an array
> S = Dict.Keys
> For N = LBound(S) To UBound(S)
> ' list all the keys
> Debug.Print "Key: " & S(N)
> Next N
>
> In order to use a Dictionary object, you must set a reference to the
> "Microsoft Scripting Runtime" library. In VBA, go to the Tools menu, choose
> References, and scroll down the list until you find "Microsoft Scripting
> Runtime". Check the box next to that entry.
>
> See also http://www.cpearson.com/Excel/Collec...ctionaries.htm for
> tons of code regarding Dictionaries and Collections and how to convert
> between the two and converting on or the other to arrays and/or ranges.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
>
>
> "RyanH" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have a Userform that when it is initialized I need to fill an Array or
> > Collection with 1 to 8 Cell Values. Is there a difference between Array
> > and
> > Collection? Differences other than Array's hold values, Collection holds
> > objects.
> > --
> > Cheers,
> > Ryan

>

 
Reply With Quote
 
Lonnie M.
Guest
Posts: n/a
 
      19th Sep 2008
Just to chime in, I recomend giving it a shot and use a collection or
dictionary, even if an array will work. There will come a time when
you will need to use one of these techniques and the more you get
comfortable with them now the better. Another thing that is worth
looking into is using ADO recordsets stored in memory. They are easily
sorted and have number of useful methods, like 'Find', and many other
useful techniques that are good to have ready in your back-pocket. ADO
recordsets require you to reference the appropriate Microsoft ADO
Extensibility library.

Good luck—Lonnie M.

 
Reply With Quote
 
Lonnie M.
Guest
Posts: n/a
 
      19th Sep 2008
Must be getting tired:
> ADO recordsets require you to reference the appropriate Microsoft ADO
> Extensibility library.


Should reference the appropriate Microsoft ActiveX Data Objects
Library (usually 2.1).

Good luck—Lonnie M.

 
Reply With Quote
 
JP
Guest
Posts: n/a
 
      19th Sep 2008
So what did you end up choosing?

--JP

On Sep 18, 2:14*pm, RyanH <(E-Mail Removed)> wrote:
> I have a Userform that when it is initialized I need to fill an Array or
> Collection with 1 to 8 Cell Values. *Is there a difference between Array and
> Collection? *Differences other than Array's hold values, Collection holds
> objects.
> --
> Cheers,
> Ryan


 
Reply With Quote
 
RyanH
Guest
Posts: n/a
 
      24th Oct 2008
Chip, you said:
>However, the Dictionary object gives you all the functionality of a
>Collection but also allows you to retreive a list of the keys.


I'm trying to understand the difference in Dictionary Object and Collection.
Can't you use this code to retrieve the list of keys in a collection as you
did with the Dictionary Object?

Sub GetKeys()

For i = 0 to colMyCollection
Debug.Print "Key" & i & ": " & colMyCollection(i)
Next i

End Sub

--
Cheers,
Ryan


"Chip Pearson" wrote:

> An array can be used to simply store a series of related values. However,
> the only way of getting a value from an array is to use the positional index
> into the array.
>
> For example, consider the following code
>
> '''''''''''' Array Example
> Dim Arr(1 To 4)
> Arr(1) = "a"
> Arr(2) = "b"
> Arr(3) = "c"
> Arr(4) = "d"
> Debug.Print Arr(3) ' displays "c"
>
> Here, the only way to get an element from the array is to use the index
> number (3 in this example).
>
> '''''''''''''' Collection Example
> Dim Coll As Collection
> Set Coll = New Collection
> Coll.Add 11, "My1" ' value is first parameter, key is second parameter. key
> must be a (unique) string
> Coll.Add 12, "My2"
> Coll.Add 13, "My3"
> Coll.Add 14, "My4"
> ' access by array index only
> Debug.Print Coll(3) ' displays 13
> Debug.Print Coll("My3") 'displays 13
>
> Here, you can access an element in the collection by its sequential index
> number (3 in this example) or by the unique key assigned to the item in the
> collection ("My3" in this example). Sadly, given a collection, there is no
> way to get a list of the keys (e.g., get the list "My1", "My2",....).
> However, the Dictionary object gives you all the functionality of a
> Collection but also allows you to retreive a list of the keys. For example,
>
> Dim Dict As Dictionary
> Dim S() As Variant
> Dim N As Long
> Set Dict = New Dictionary
> Dict.Add "My1", 1 ' order of key and valuie is reversed re: Collection.
> Key first, value second. Key must be string.
> Dict.Add "My2", 2
> Dict.Add "My3", 3
>
> ' access by positional index
> Debug.Print Dict(2) ' returns 2
> ' access by key value
> Debug.Print Dict("My2") ' returns 2
> ' get all keys into an array
> S = Dict.Keys
> For N = LBound(S) To UBound(S)
> ' list all the keys
> Debug.Print "Key: " & S(N)
> Next N
>
> In order to use a Dictionary object, you must set a reference to the
> "Microsoft Scripting Runtime" library. In VBA, go to the Tools menu, choose
> References, and scroll down the list until you find "Microsoft Scripting
> Runtime". Check the box next to that entry.
>
> See also http://www.cpearson.com/Excel/Collec...ctionaries.htm for
> tons of code regarding Dictionaries and Collections and how to convert
> between the two and converting on or the other to arrays and/or ranges.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
>
>
> "RyanH" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I have a Userform that when it is initialized I need to fill an Array or
> > Collection with 1 to 8 Cell Values. Is there a difference between Array
> > and
> > Collection? Differences other than Array's hold values, Collection holds
> > objects.
> > --
> > Cheers,
> > Ryan

>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) Keith R Microsoft Excel Programming 3 13th Nov 2007 05:08 PM
Collection problems (create Collection object, add data to collection, bind collection to datagrid) Řyvind Isaksen Microsoft ASP .NET 1 18th May 2007 10:24 AM
Collection problems (create Collection object, add data to collection, bind collection to datagrid) Řyvind Isaksen Microsoft Dot NET 1 18th May 2007 10:24 AM
meaning of : IF(Switch; Average(array A, array B); array A) =?Utf-8?B?RFhBVA==?= Microsoft Excel Worksheet Functions 1 24th Oct 2006 06:11 PM
Viewing contents of collection in collection pane =?Utf-8?B?amV0bWVjaDIxNw==?= Windows XP MovieMaker 8 28th Nov 2004 01:21 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:56 AM.