At the top of the heap, the Dictionary object is a data structure that contains what could be described as an index. Dictionary objects store pairs of data. Very similar to an array.
One half of the pair is an item of any data type.
The second half is a key that consists of a unique string that identifies the item.
Each item is associated with a unique key. This key is then used to retrieve an individual item. Keys are usually Integers or Strings
In a way, this structure is similar to a VBA collection, with a few more bells and whistles.
The primary purpose of a Dictionary object is to create a collection of related files, which you can then search or otherwise manipulate.

Dim objFSODictionary As New Scripting.Dictionary 


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


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

Dim vaArray As Variant 
vbArray = objFSODictionary.Keys

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

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 Dictionary object is similar to a VBA Collection object however the Dictionary object offers certain advantages:

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.
For example if you are creating a custom object model, you can use a Collection to store a reference to a custom collection but you can't use a Dictionary object to do this.

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 strore a 1 dimensional array.

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

Set pbjFSOUnique = New Scripting.Dictionary 

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


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 objFSODictionary.Exists(key) = False Then 

End If

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