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

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

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 <Ry...@discussions.microsoft.com> 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

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:1CA923D7-2FF2-42EE-8C63-(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
>

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.

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.

So what did you end up choosing?

--JP

On Sep 18, 2:14*pm, RyanH <Ry...@discussions.microsoft.com> 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

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:1CA923D7-2FF2-42EE-8C63-(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
>