User FAQs

1) What is a Collection object ?
A collection is a built-in object that you can use to represent a group of objects.

Dim MyCollection As VBA.Collection 
Set MyCollection = New VBA.Collection
MyCollection.Add "Item_1"
MyCollection.Add "Item_2"
MyCollection.Add "Item_3"

2) What is a Dictionary object ?
Requires - Microsoft.Scripting.Runtime library.
A dictionary object can contain pairs of objects where each pair consists of an item and a unique key.
The item can be any data type (including objects).
The key can be any data type (except for arrays) although strings or numbers are typically used.
Dictionaries are often compared to associative arrays otherwise known as hash tables.

Dim MyDictionary As Scripting.Dictionary 
Set MyDictionary = New Scripting.Dictionary
MyDictionary.Add "key1", "Item_1"
MyDictionary.Add "key2", "Item_2"
MyDictionary.Add "key3", "Item_3"

3) What are the differences between a Collection and a Dictionary ?
*) When adding items the order of the arguments is different. The Collection.Add arguments are in the order Item, Key. The Dictionary.Add arguments are in the order Key, Item.
*) Unique keys are mandatory in a Dictionary but optional in a Collection.
*) Items can only be returned using the key in a Dictionary but can be returned using the key or the ordinal position (1 based) in a Collection.
*) Invalid keys will generate an error when passed to a Collection but in a Dictionary will return an empty string.
*) Unique keys are case sensitive in a Dictionary (by default, although easily changed) but are not case sensitive in a Collection (by default).
*) The Dictionary has an Exists method to test for a particular key. In a Collection you must try to retrieve the item and then handle if the key cannot be found.
*) A list of keys is available from a Dictionary using the Keys method. In a Collection you cannot get a list of the keys.

Dim vaKeyValues As Variant 
vaKeyValues = MyDictionary.Keys()

*) The items are read/write in a Dictionary but read-only in a Collection. In a Collection you cannot change a particular item you must remove and add a new item.
*) The keys are read/write in a Dictionary but read-only in a Collection.
*) You can remove all the items from a Dictionary in one go without destroying the object itself. In a Collection you can only remove one item at a time, or destroy and recreate the whole collection.
*) Both support the For-Each-Next statement for enumerating through the items. A Dictionary loops through the Keys. A Collection loops through the Items.
*) You can add an item implicitly to a Dictionary using the Item property. In a Collection you can only use the Add method.

MyDictionary.Item("key4") = "item_4"  'doesn't exist but gets added  
myValue = MyDictionary.Item("key5") 'doesn't exist but gets added

4) When would you use a Dictionary instead of a Collection ?
*) Retrieve keys as well as the items.
*) Accommodate changes in items and/or keys.

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext