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)