Scripting Dictionary

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

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.
The CompareMode property of the Dictionary is used to determine if the "key" is case sensitive or not.

oDict.CompareMode = vbBinaryCompare   ' is case sensitive  
oDict.Add("Mon", 10)
Debug.Print oDict.Exists("mon") ' = False

oDict.CompareMode = vbTextCompare ' is not case sensitive
oDict.Add("Mon", 10)
Debug.Print oDict.Exists("mon") ' = True

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.

Keys Property

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

Dim arMyArray As Variant 
arMyArray = dicFSODictionary.Keys

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