Array vs. Collection

[RyanH]

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.

 

[Jim Thomlinson]

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.

 

[JP]

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

 

[Jim Thomlinson]

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

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/CollectionsAndDictionaries.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)

 

[Lonnie M.]

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.

 

[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.

 

[JP]

So what did you end up choosing?

--JP

 

[RyanH]

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

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/CollectionsAndDictionaries.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)