VBA – Dictionary

VBA has a dictionary structure. Dictionary is an object, and it can be referenced either with early binding or with a late binding, referring to Microsoft Scripting Runtime:

Adding the reference to the VBEditor, through Extras>Libraries

What is a dictionary? This is the Microsoft definition of the VBA dictionary:

Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

With other words, we have some kind of object, that maps values to keys. What does this mean? Let’s imagine we have a dictionary object, to which we want to save some information. E.g., information about two other people and salary. Like this:

There “Peter” is a key and “Peter is a friend” is a value. At the end of the program, we request to see the value of the key “Peter”, which is printed in the immediate window. The dictionary object has methods and properties. This is a short presentation of them.


Add Adds a new key/item pair to a Dictionary object.
Exists Returns a Boolean value that indicates whether a specified key exists in the Dictionary object.
Items Returns an array of all the items in a Dictionary object.
Keys Returns an array of all the keys in a Dictionary object.
Remove Removes one specified key/item pair from the Dictionary object.
RemoveAll Removes all the key/item pairs in the Dictionary object.


CompareMode Sets or returns the comparison mode for comparing keys in a Dictionary object.
Count Returns the number of key/item pairs in a Dictionary object.
Item Sets or returns the value of an item in a Dictionary object.
Key Sets a new key value for an existing key value in a Dictionary object.

What can we do with them? This is an example with all the methods and properties:

The trick with .CompareMode is that by default its value is set to vbTextCompare, thus “PeTeR” and “PETeR” would not be allowed as different keys. However, once the value is reset to “BinaryCompare”, this is allowed.

True story…


A VBA Developer.

Tagged with: , , , , ,