VBA Dictionary: How to create a Dictionary


VBA Dictionary Create

To create a dictionary, two lines of code are needed.

The first line declares a variable of the type dictionary.

Image showing to declare a dictionary

The second line assigns an object reference to the variable. Now, you can use the dictionary.

Image showing to assign object reference

Dictionaries are not native to VBA. Dictionaries are part of the Microsoft Scripting Runtime library. The source file of the Microsoft Scripting Runtime library is scrrun.dll.

Actually, there are two ways to create a dictionary: through late binding and through early binding. The machine does't care. If you replace late binding by early binding or vice versa, the code should behave exactly the same.

Late binding

Early binding

Or equivalently.

Adding the reference to 'Microsoft Scripting Runtime'

In the VBA editor, click 'Tools', then click 'References...'.

Image showing how to open the add reference window

In the windows that pops up, check if 'Microsoft Scripting Runtime' is available. If yes, click the checkbox next to 'Microsoft Scripting Runtime' and then OK.

Image showing how to check a reference

This has added the reference. Early binding of a dictionary is possible now.

Early binding activates intellisense. This means

  • that "Dictionary" will be in the list of the available types.
    Image showing intellisense 1
  • that the public methods of the class show as you type.
    Image showing intellisense 2
  • that the public methods of the class show as you type.
    Image showing intellisense 3
  • ...

After declaration, there are no key/item pairs in the dictionary. The method Count will return 0.

If you forget the Set ... = New ... statement, the dictionary will be Nothing, and, cannot be used meaningfully. The VBA editor will not warn you that you forgot the second line. The code will compile. Intellisense will work.

Image showing VBA error object not set

A dictionary can also be declared in one line.

Or equivalently.

Is there is a difference , compared to the two lines declaration?

Yes.

In the one line declaration, the object reference will be assigned in the next line with executable code that involves D. In other words, the object reference will be assigned the first time you use the variable.

The line D.Add "Key", "Item" assigns the object reference and executes the Add method. If the object gets destroyed, it will be reassignedin the next line with executable code that involves D.

Hard to remember, no? We never create using the Dim ... As New ... statement.