There is an additional library that offers a data structure that is similar to the built-in Collection data type but with a few advantages.
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.
This object is not part of the Office library and requires you to add an additional reference to your project.
SS - References dialog
The Microsoft Scripting Runtime is a library that can be added to a Project using (Tools > References).
This library is located in C:\Windows\System32\scrrun.dll

Dim dicFSODictionary As Scripting.Dictionary 
Set dicFSODictionary = New Scripting.Dictionary

Instead of a Collection

The Dictionary object is a data structure that can contain sets of pairs, where each pair consists of an item, which can be any data type, and a key, which is a unique string value that identified the item.
The main advantage of the Dictionary over a Collection is the fact that it is easier to search the Dictionary for a given item.
Despite this advantage the Dictionary object does not entirely replace the Collection object.
The Collection object is useful in many situations where the Dictionary is not.
A complete list of all the differences can be found on the Dictionary vs Collection page.


Dim dicFSODictionary As New Scripting.Dictionary 


Dim dicFSODictionary As Scripting.Dictionary 
Set dicFSODictionary = New Scripting.Dictionary


Dim dicFSODictionary As Object 
Set dicFSODictionary = CreateObject("Scripting.Dictionary")

CompareMode Property

Settings this property specifies the text-comparison mode for the Dictionary object so that you can search for keys either with a case sensitive or case insensitive search.
By default this is set to BinaryCompare which means that the Exists method will be true only if it finds a binary match, ie which is case sensitive.
There is no way of performing an case insensitive search with a collection.

Key Property

This property enable you to return the key for a particular item in the dictionary.
An item in a Collection object also has a key, which you can use to retrieve the item, however there is no way to retrieve the key itself.


You don't have to add a Range object, you can add Range.Value to store a 1 dimensional array.

dicFSODictionary.Keys - this returns a 1 dimensional array - zero based array of all the unique keys.

Set dicFSOUnique = New Scripting.Dictionary 

The dictionary item is created with a default single element ??
somehow elements were getting automatically added when using Cells(,) = objFSOUnique(item)


Dim arMyArray As Variant 
arMyArray = dicFSODictionary.Keys

This returns a 1 dimensional array of all the unique identifiers.

RemoveAll Method

This method removes all items in the Dictionary object
A collection object on the other hand has no method for quickly removing all the items at once.
This can be achieved by setting the Collection to Nothing.

Exists Method

You can use this method to determine whether a particular key and its corresponding item exist in a Dictionary object
The Exists method makes it simpler and more efficient to search a Dictionary object than to search a Collection object.

?? Don't need to force/generate an error.

If (dicFSODictionary.Exists(key) = False) Then 

End If

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