Dictionary Object Basics ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction to the Dictionary Object.

A Dictionary object is a VBA object that stores data as key-item pairs, where each unique key is associated with a corresponding value or object. Unlike a Collection, which is primarily index-based, a Dictionary is designed for fast retrieval of data using unique keys.

The Dictionary object is provided by the Microsoft Scripting Runtime library (Scripting.Dictionary). It is particularly useful and efficient when data must be searched, updated, or verified based on a unique identifier.

Key Characteristics

Key-item storage – Each entry consists of a unique key and its associated item (value/object).
Fast key lookup – Items are retrieved directly by key without searching sequentially through the collection.
Dynamic size – Entries can be added or removed at runtime.
Unique keys – Duplicate keys are not permitted.
Flexible item types – Items can be simple data types, arrays, or object references.
Modifiable keys – Existing keys can be renamed using the Key property.

By now, I hope you have reviewed the recent articles on using the Collection Object in Microsoft Access. Even if you haven’t, you should still be able to follow along with the Dictionary Object and its usage. Collection and Dictionary Objects share many similarities, but understanding their differences will help you decide which one is best suited for a particular task.

In either case, the links are given below for easy access.

The Dictionary Object is not natively part of Microsoft Access VBA; it originates from VBScript, commonly used on web pages. To use a Dictionary Object in Access, we need to create it in a VBA program. There are two ways to accomplish this in Microsoft Access VBA:

A. With the use of the MS Access function CreateObject().

Dim d As Object

Set d = CreateObject("Scripting.Dictionary")

This approach has a minor drawback for beginners: IntelliSense will not display the Dictionary Object’s methods and properties because the object is declared as a generic Object type.

Dictionary Object Library File.

B. But there is a better method. Add the Microsoft Scripting Runtime Library to the selected existing list of Libraries in Microsoft Access.

When we do that, we can declare and use the Dictionary Object as we did for the Collection Object.

Select the References option from the Tools Menu in the VBA Window.
The sample display of Library Files is given below.

Right-click to open the large image in a New Tab or Window.

The check-marked item (Microsoft Scripting Runtime) is the Library File you need to look for in your System. The unchecked items are in alphabetical order.
Move the Scrollbar down and find the file Microsoft Scripting Runtime, select it, and click the OK Button to exit.

Now, you can declare and instantiate a Dictionary Object with IntelliSense support.

Dim d As Dictionary

Set d = New Dictionary

Dim d As New Dictionary

Dictionary Object has the following List of Methods and Properties:

Method Description

Add - Adds an item to the object with the specified Key. Always adds an Item with a Key-Value.

Exists - Verifies that the specified key exists.

Items Return - Returns an array of Item (Element) Values.

Keys - Returns an array of Keys.

Remove - Removes the Item specified by the Key.

RemoveAll - Removes the Dictionary Object from Memory.

Property Description

Count - Gives the count of Items in the dictionary.

Item - Retrieve/Replace/Add the item with the specified key. If the specified key doesn’t exist, the Item value is added to the Dictionary under the specified key.

Key - Replaces the specified Key with a new Key.

CompareMode - Mode for comparing string keys.

0 - Binary (default): A <> a, A<a

1 - Text: A=a, Aa=aa, AA=aa

The Test Run Code.

Copy and paste the following sample code into your VBA Standard Module:

Public Sub Dict_Test0()
Dim d As Dictionary
Dim mkey, mitem
Dim strKey As String
Dim msg As String
Dim Title As String

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals

d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "Canada", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

For Each mkey In d.Keys
   msg = msg & mkey & vbCr
Next

msg = msg & vbCr & "Select a Country, Q=Quit."
Title = "Dict_Test0()"
strKey = ""

Do While strKey = "" And strKey <> "Q"
   strKey = InputBox(msg, Title, "")
   If strKey = "Q" Then
      Exit Do
   End If

If d.Exists(strKey) Then
mitem=d(strKey)
    MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & " Capital:  " & UCase(mitem), , Title
Else
    MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & "Doesn't exists.", , Title
End If

   strKey = ""
Loop

'Remove Dictionary from memory
d.RemoveAll

End Sub

Viewing the Values in Memory.

Insert a Stop statement immediately below the text USA, Washington, D.C. Add a statement.
Select the Locals Window option from the View Menu.
Click anywhere in the Code, and press the F5 key to run the Code.

The Program pauses at the Stop statement.

Check the Locals Window, click the plus symbol in [+] d, and view the contents. It shows only the Key Values, and the Item values are not visible.

Press the F5 Key again to continue executing the Code.

Enter a Country Name from the displayed list and then press the Enter Key or click the OK Command Button to display the selected Country’s Capital.

You can type the country name in uppercase, lowercase, or in mixed form. The compare mode setting at the beginning of the code takes care of comparing the Key value entered with the list of keys in the Dictionary Object.

Enter the letter Q to exit the Do ... Loop and stop the Program.

How it Works.

Let us review the code. Since I have already added the Microsoft Scripting Runtime file to my selected list of VBA Library files, I could declare the variable d as a Dictionary Object, as we did with the Collection Object. Declared a few other required Variables as well.

The statement Set d = New Dictionary instantiates the Dictionary in memory as Object d.

The d.CompareMode determines how the given Key Value is compared with the existing list of Keys in memory to retrieve, replace an Item, or a Key-Value.

The Syntax Comment line indicates how to add an item to the Dictionary Object as its Element.

In the Dictionary Object, the Key is the first parameter and the Item is the second. Both Key and Item Parameters are mandatory and separated by a Comma.

In the Collection Object, the order of both these parameters is reversed. The first Parameter is Item, and the second Parameter, Key, is Optional.

The d.Add the statement, and check whether the given key already exists in the Dictionary Object first; if it does, display an error message: ‘This key is already associated with an element of this Collection’. The key values must be unique.

If CompareMode=1, then the variants of the name ‘Nancy’, ‘nancy’, ‘NaNcY’ are all referring to the same Key NANCY or nancy.

If CompareMode=0 (Binary Compare), then all three names are different Keys.

When the Add method finds that the specified Key value doesn’t match any existing Keys, the new Key is added to the Item Value in the Dictionary Object.

The Key Value can be of any Data Type except Variant, Array, or Object. Stick with one Key value Type for all Items, not a mix of different data types.

We have added eight country names and their capitals.

The For Each … Next statement reads the list of Keys from the Dictionary Object and prepares a menu for the InputBox () Function.

The conditional Do While ... Loop runs until the User enters the letter Q or q (Quit) in the InputBox() function.

The user types a Country name through the InputBox() function to display the Country’s capital in a message box.

The entered country name in the strKey variable is validated using the d.Exists() method to ensure that the entered Key exists in the Dictionary, reads the corresponding Item value, and displays it in the Message Box.

When the user enters the letter Q in the InputBox function, the program stops executing the statement d.RemoveAll That clears the Dictionary Object from memory.

We have read the Key Values alone using the For Each mKeys In d.Keys statement to create a list of Keys for the InputBox Menu. The d.Keys statement creates a 0-based Array of Key Values. You can create a separate Array of Key Values with the following statement:

myKeys = d.Keys

Determine the Array elements' LBound and UBound values to work with the list.

In the same way, we can read all Items (elements) into an Array, away from the Dictionary Object, to work with it if needed.

Take a Listing of All Items.

Let us create a list of all the Items, with the method explained above. We will make a copy of the above Code and make some changes to retrieve the Items into an Array and print them into the Debug Window.

Here is the Code:

Public Sub Dict_Test0_1()
Dim d As Dictionary
Dim mitem, j As Long

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals
d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "Canada", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

mitem = d.Items

Debug.Print "Country Capitals"
Debug.Print "----------------"
For j = LBound(mitem) To UBound(mitem)
   Debug.Print j, mitem(j)
Next

'Remove the Dictionary from memory
d.RemoveAll

End Sub

Copy and paste the code into a Standard Module. Display the Debug Window (CTRL+G).

Run the code to get a list of Country Capitals in the Debug Window as shown below.

Country Capitals
----------------
 0            Canberra
 1            Brussels
 2            Ottawa
 3            Copenhagen
 4            Paris
 5            Rome
 6            Riyadh
 7            Washington D.C.

You may modify the item = d.Items statement to mitem = d.Keys to take a listing of all Countries.

We will continue this discussion Next Week.