Dictionary Object Basics ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Hope you have gone through the last few Articles on Collection Object usage in Microsoft Access, by now. If not, there will not be any difficulty in understanding Dictionary Object and its usage. But the Collection and Dictionary Objects have a lot of similarities, in their usage, and knowing them better will give you more advantages in deciding which one is suitable for a particular task.

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

The Dictionary Object is not part of Microsoft Access VBA. It is part of the VBScript Language used on Web Pages. To use the Dictionary Object in Microsoft Access we must directly create an object in VBA Program. There are two ways we can do this in Microsoft Access VBA:

A. With the use of Ms-Access Function CreateObject().

Dim d As Object

Set d = CreateObject("Scripting.Dictionary")

This method has a small disadvantage, as far as beginners are concerned, it will not display the Dictionary Object's Methods and Properties by IntelliSense. Because we have declared a generic type Object to hold the Dictionary Object.

Dictionary Object Library File.

B. But, there is a better method. Add the Microsoft Scripting Runtime Library to the existing selected 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 VBA Window.
The sample display of Library Files is given below.

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

Now, you can Declare and Instantiate a Dictionary Object with Intellisense support, like the sample code given below.

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 added an Item with a Key-Value.

Exists - Verifies that the specified key exists.

Items Return - 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 a count of Items in the dictionary.

Item - Retrieve/Replace/Add the item with the specified key. If the specified key doesn’t exist, then the Item value is added to the Dictionary with 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.

Put a Stop statement immediately below the USA, Washington D.C. Add statement.
Select the Locals Window option from the View Menu.
Click somewhere within the Code and press F5 to Run the Code.

The Program pauses at the Stop statement.

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

Press F5 Key again to continue executing the Code.

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

You can type, the country name in Upper-Case/Lower-Case or in mixed form. The compare mode setting at the beginning of the Code will take care of comparing the Key value entered by you with the list of keys in Dictionary Object.

Enter the letter Q to Exit from 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 into 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 for retrieving/replacing Item (Element) or Key-Value.

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

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

In 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, then give out 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 the above three names are different Keys.

When the Add method finds that the Key value given doesn’t match with the existing Keys the new Key is added to the Item Value to the Dictionary Object.

The Key Value can be of any Data Type except Variant, Array, or Object. Stick with one type of key value 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 LBound and UBound Array elements Range 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.

Taking a Listing of All Items.

Let us try to make a listing of all 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 listing 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.