Leveraging Dictionaries in VBA

Likely one of the most under-utilized data structures in VBA, Dictionaries include features not found in Collections or Arrays. With their ease of coding and readability, many developers rely heavily on this data structure.


This post within the Getting Started with VBA series completes the focus on Other VBA Data Structures. Many parts of this tutorial draw a comparison between Dictionaries and Collections. If you are not familiar with Collections, it is strongly recommended that you read our post on Collections before reading about Dictionaries.

Within many other programming languages, Dictionaries play a central role and can often be used as an alternative to Collections and Arrays. Dictionaries are not as well known in VBA, as they are very similar to Collections in behavior. They do provide some added features, however, such as the ability to modify values and easily and search for existing keys. Unlike Collections, however, Dictionaries cannot contain a “collection” of custom objects and must always have an assigned custom key.

Sample File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Activate Microsoft Scripting Runtime

Dictionaries are not part of VBA’s core scripting set (DLL file), so a reference to the Microsoft Scripting Runtime is required. This involves a one-time step within the VBA editor.

Click on (1) Tools > (2)  References. A dialog box will appear showing a list of (3) Available References. Scroll down the list to (4) Microsoft Scripting Runtime and check the checkbox. Press OK.

Activate Microsoft Scripting Runtime.

Activate Microsoft Scripting Runtime.

This setting will be tied to the specific Excel file only — it will save the setting on the current open file, but you must go through this step for each separate file for which you plan to use Dictionaries.

Dictionaries vs. Collections vs. Array

A Dictionary is often compared to a Collection in terms of behavior and features. This table also includes a comparison with an Array, which was previously summarized in the post on Collections.

Task Dictionary Collection Array
Adding Values Easy: Add method will automatically add elements to the end of the collection. A numeric or string Key value is required. Easy: Same method, but a Key value is not required (a custom Key value can be assigned) Somewhat Difficult: value must be added when final key (size) is known; must be within array size constraints.
Removing Values Easy: Remove method will remove value; must know key value. Easy: Remove method will remove value and re-index the collection; must know key or index value. Difficult: Array key must be assigned an empty value, or manually re-indexed.
Resizing Easy: Will resize automatically when new values are added or removed. Easy: Will resize automatically when new values are added or removed. Somewhat Difficult: Must use the ReDim or ReDim Preserve statements.
Modifying Values Easy: Item or Key values can be modified by an assignment operator while using the Item or Key methods. Difficult: Must remove and add a new value in the same position. Easy: Assign a new value to the array key.
Printing Individual Values Easy: Use Item method and Key. Easy: Use Item method and Index or Key. Easy: Use key.
Printing All Values Easy: For Each loop or Do loop. Easy: For Each loop, For loop, or Do loop. Easy: For Each loop, For loop, or Do loop.
Searching for Values Easy: Use Exists method and key Somewhat Difficult: Use a For Each loop to test each value. Somewhat Difficult: Use a For Each loop to test each value.

Using a Dictionary

This post will use a Dictionary defined as apartmentData, containing rental details on apartments within a building.

Declaring (Binding)

There are two methods of declaring Dictionaries: early binding and late binding.

Early Binding

Early binding permits an a dictionary to be declared in the declaration area of a module or subroutine/function. This is the most common method of declaration and will allow intellisense to work with the Dictionary. The following is a simple example of early binding:

Late Binding

Late binding permits a dictionary to be initialized at some point outside of the declaration area. Initially, an object must be declared as part of a declaration statement. At any point during the execution of a script, the object can be recast as a Dictionary. The following is a simple example of a late binding:

Late binding does not permit the use of intellisense, making early binding a preferred method. Late binding may be useful if you need to pass an object through a function for some future Dictionary. Keep in mind that it is possible to pass a Dictionary through a function parameter.

Dictionary Keys

Unlike a Collections–where Keys are optional–a custom Key is required for each Item (element) within a Dictionary. In this sense, Dictionaries behave more like one-dimensional arrays.

Dictionary Methods

Many Dictionary methods are similar to its Collection counterpart. A few differences and new methods do exist, which will be discussed below.

If a Dictionary is declared with early binding, intellisense will present a list of available methods after the dot operator.

Intellisense activated for dictionaries.
Intellisense activated for dictionaries.


The Add method will add a new value to a dictionary. The first parameter requires a key value, and the second requires the item (element) value.

Brackets are not required.

The second (no brackets) approach will be used for the remainder of this post. As stated previously, one benefit of a dictionary is the use of non-string numeric values as keys. With that, a numeric value without quotes will work. Even double or long values will qualify as keys.

An error will be returned if an attempt is made at creating a element with a key that already exists. This may be a common occurrence if a Dictionary is declared globally.


The Item (singular) method will call a specific item (element) value by key reference. The following will call the descriptive (item) value.

The Item method can also be used to modify an existing item through the assignment operator.

The key must be known to request an individual item.


They Items (plural) method can be used within a For Each loop to call each item value within a dictionary.


The Key (singular) method can be used to update/modify a specific key value.

Contrary to what may seem possible, the Key method cannot be used to return a key value. It is strictly used for changing a key value for an existing element.


They Keys (plural) method can be used within a For Each loop to call each key value within a dictionary.

Unlike the Key (singular) method, key values will be returned through the assigned “element” of the For Each loop. This is useful for printing both the Dictionary key and the Dictionary item, as the key can be inserted as the Item method parameter for each iteration. This will be presented in an example below.


The Exists method is one of the unique and most useful methods associated with Dictionaries. Unlike Arrays and Collections–where testing if a key or value exists usually involves error handling or iterating through entire data structures–a dictionary includes a method to test if a key exists with a single line of code.


The Remove method can be used to remove a specific key and item (element) from a Dictionary. They specific element’s key must be specified as the parameter.

The element and key will no longer exist in the Dictionary. Any attempt to refer to the item (element) through the Item method or a For Each loop will return no result.


The RemoveAll method will completely remove all keys and items within a Dictionary.

This method does not completely eliminate the dictionary from memory — it will contain no values immediately after the RemoveAll statement, but new values can be added without redeclaring or initiating a new Dictionary.


The Count method returns the total number of elements within a Dictionary.

Will print the count in cell A1.


The CompareMode method specifies a criteria for duplicate keys. Generally, a unique value is a set of characters which are not repeated at any point. In most situations within VBA, uniqueness is sensitive to case — that is value “1A” is considered identical to value “1a”. However, in a binary sense, upper-case and lower-case characters are considered identical.

By default, Dictionaries are set to TextCompare, meaning that the same value in different cases is considered identical. If you encounter a situation where two “identical” words, but with different cases are required as keys in a dictionary, you can set a dictionary to check binary values instead of text values.

In the event that you want a BinaryCompare dictionary to revert back to its default compare method, you can use TextCompare to return it to that state. Keep in mind that this is the default setting.

CompareMode with Exists

Unfortunately a Dictionary set to BinaryCompare will not permit different cases to be used in a test using the Exists method. Regardless of comparison setting, the case will always need to match for a value of True to be returned.

Using and Printing Dictionaries

Printing Dictionaries generally follows the same principles and methods of Collections and Arrays, with some modest differences.

Explicit Print

Each element within the Dictionary can be accessed individually for printing. This is generally an inefficient way to print all items within a dictionary, but it’s useful for printing one or more specific items when the key is know.

Line 20 has been highlighted to show another feature unique to Dictionaries. Notice how the Item parameter is requesting the item assigned to key 4A. However, Key 4A was never added to the Dictionary. Unlike a Collection, which would return a “subscript out-of-range” error, the Dictionary will simply treat this as an empty value.

While this may be a good feature for avoiding errors, it is the opinion of this author that this is in poor form. It hinders the readability of the code, and could cause issues with future modifications.

Unfortunately it is not possible to print a Key value through a Dictionary method, but since the key is needed to refer to an item in the first place, it can simply be printed next to it as a literal or through a variable value. It is not possible to return a key value through an item reference.

For Loop

Unless Dictionary keys are in a sequential numeric order (no string values), a For Loop is NOT an ideal method of printing values of an entire Dictionary. Given that For Loops will only iterate through numeric values and Dictionary keys often contain string values, elaborate workarounds will be needed for this to work.

In the following script, the Modulus Operator is used to determine if variable i is even or odd. Even numbers will then concatenate the letter “B” to the Item parameter (Key), whereas odd numbers will concatenate “A”.

While this works, it’s a bit cumbersome and not very versatile. This specific approach would not work if one floor had three apartments, say, apartment C. Likewise, the additional counter j (counted once every 2 iterations to change floors), and the conditional statement are inefficient and reduce readability.

If a For Loop is required for your needs, you may want to opt for a Collection as opposed to a Dictionary. While Collections can contain custom key values as strings, each element will also have a numeric (invisible) index number. The lack of an index is a weakness of Dictionaries.

For Dictionaries, a For Loop will work nicely if keys are sequential numeric values. Keep in mind that if a Dictionary item is removed at any point, a gap in the numeric sequence will be created.

For Each Loop

For Each Loops work extremely well with Dictionaries. This presents an instance where both Keys and Items can be easily printed together.

In this example, another unique feature of Dictionaries is used, where the For Each Loop actually returns each Key value within its element. This is done by using the Keys method within the For Each statement — the Key value can be used directly in the loop. In this example, the For Each element keyValue is printed in column A (line 17), then used within the Item method parameter to pull the Item value and print it in Column B (line 18).

Alternatively, the Items method can be used to return each Item value through the For Each element (similar to Collections and Arrays). This is the most effective method of printing or using every Item value in a Dictionary.

Application of Other Methods

As previously explained, Dictionaries contain other methods which permit the modification, searching, and removal of elements. The following simple application conducts all of these actions.

Input box for Dictionary example.This simple application permits the user to modify apartment details or altogether remove an apartment from the list. Upon starting the script, an input form (line 17) will request an apartment number from the user. If the apartment entered does not exist, a message (line 26) will appear telling the user “Apartment cannot be found. Try again.” If the apartment does exist, a second input box will appear (line 19) telling the user to “Update the apartment description, or type DELETE to remove”. If the user types in “DELETE” (all caps), the Remove method (line 21) will remove the apartment. If any other details are changed, the Item method will modify the value (line 23). The script then finishes up by printing each Key and Item with a For Each loop (lines 30-34).

Other Notes

  • Need to sort a dictionary? See our post on using bubble sort with dictionaries and other VBA data structures.
  • The Microsoft Scripting Runtime reference will transfer with the spreadsheet if it is sent to another user on a different computer. In some instances, a dictionary will not work on another computer if the Microsoft Scripting Runtime is not installed, but generally it will be installed in most machines running Windows 7 or greater.
  • Dictionaries behave similar to Collections when an item is removed — all items after the removed item will move up in order. This is unlike arrays, where an item (element) can be converted to a null/blank value, but cannot be completely removed.

Leave a Reply