Dictionary Object Basics-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Last week’s introduction to the Dictionary Object was, I hope, a useful and informative start for this week’s topic, helping you grasp the fundamentals of its usage in programs. The sample VBA code presented there demonstrated a few of its properties and methods in their simplest form, and I assume you have already tried them out.

If not, I recommend visiting last week’s article, Dictionary Object Basics, before continuing.

The list of properties and methods shown below was introduced in last week’s demonstration programs. You may revisit that page for a quick review or to get a fresh look at it before proceeding further.

The list of Properties and Methods we tried out in last week's VBA programs.

1. Property

CompareMode

2. Methods

Add
RemoveAll
Exists
Keys
Items

Now, let us try out the following Properties and Methods, which remain to be tried out, and find out how they work in the programs:

1. Properties

Item - Retrieve/Replace/Add Item Value of the specified Key.
Key - Replaces the Item Key with the new Key-Value specified.
Count – Returns Item Count

2. Method

Remove – Removes the specific Dictionary Item that matches the Key.

There are some similarities in the property names and methods: Key and Keys, Item and Items, Remove and RemoveAll. They do different things but are easier to remember because of their similarities in names.

**The Item Property.**

First, we will take the Item Property to try out. The item is a multipurpose Property Procedure and works for more than one function.

It retrieves the Item value for output purposes. Key-Value is an Item Parameter.
Example:

Debug.Print d.Item("Canada")ORx = d.Item("Canada")
Result: x = "Ottawa"

It replaces an Item value, matching the specified Item Key.
Example:

d.Item("Canada") = "Alberta" ‘ Ottawa replaced with Alberta

If the specified Key is not found, then add the given Key and Value pair to the Dictionary Object, as a new Item. It works like the d.Add statement if the Key provided doesn’t match any Key in the Dictionary Object.
Example:

d.Item("Kanada") = "Ottawa" ‘the Key misspelled as Kanada

We will bring the sample code used earlier for demo runs here.

The Sample Code with Item Property Procedure.

Public Sub Dict_Test0_3()
Dim d As Dictionary
Dim mkey, j As Long
Dim strheader 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", "ALBERTA"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

strheader = "*** LISTING BEFORE CHANGE ***"
GoSub print_section

'Replace ALBERTA with Ottawa
d.item("Canada") = "Ottawa"

'Trying to replace Item Value of
'non-existend Key: India
'This action will ADD a new Item to Dictionary.
d.item("India") = "New Delhi"

strheader = "*** LISTING AFTER CHANGE ***"
GoSub print_section

d.RemoveAll 'clear Dictionary Object from memory

Exit Sub

print_section:

Debug.Print
Debug.Print strheader
Debug.Print "S.No.", "Country", "Capitals"
Debug.Print "-----", "-------", "---------"
j = 1
For Each mkey In d.Keys
   Debug.Print j, mkey, d.item(mkey) 'RETRIEVES Value
   j = j + 1
Next
Return

End Sub

All three actions: Retrieval, Replace & Add are demonstrated in the above code.

We have added eight Items to the Dictionary Object. I have changed the Capital City of Canada to ALBERTA, in place of the actual Capital, Ottawa, to try out the Replace action.

Immediately after the series of eight Add statements, we call the print_section subroutine to take a listing of the unchanged items in the Debug Window, from Dictionary Object d.

In the print_section subroutine, within the For ... Next Loop, find the expression d.item(mkey) that calls the Item Property Procedure with the Key parameter (Country Name) to retrieve the Capital name of each country and print it, along with the serial number and Country Name, in the Debug Window.

The next executable statement d.Item(“Canada”) = “Ottawa” searches through the list of Keys, for Canada, finds the item, and replaces Alberta with Ottawa. When we call the print_section again, the change will appear in the listing of Countries and Capitals.

The next executable statement d.Item(“India”) = “New Delhi” is to watch for its effect. This statement works similarly to the one we used earlier to replace Canada’s capital, Ottawa. If the search for the key "India" fails, it will not generate an error to indicate the missing key. Instead, it will silently add the unmatched key "India" along with the item value "New Delhi" to the Dictionary object as a new entry.

In the next step, we call the print_section subroutine again to display the updated list of items in the Debug Window. The d.RemoveAll statement then clears all entries from the Dictionary object and releases it from memory before the program stops.

Look for the following changes in the new listing of items in the Debug Window:

Canada’s Capital has changed from Alberta to Ottawa.
India and the New Delhi pair are added as a new Item in the Dictionary Object, as Key and Item Value, respectively.

Note: The negative aspect of this particular behavior is that if you make a spelling mistake in the Key value, you can easily add a wrong item to the list, and you will not know about it either.

A workaround is a validation check on the Key-Value before attempting to run the value replacement statement, like the sample code below:

strKey="India"
If d.Exists(strKey) Then
   
   d.Item(strKey)="New Delhi"

Else
   MsgBox "Key: " & strKey & " Not Found!"
End If

Remove, Key, and Count Property Procedures.

In the next Program, we will try out the usage of the Remove Method, Key, and Count Property Procedures.

The Remove method deletes an Item from the Dictionary Object. Searches for the Key, finds it, and deletes the Item from the Dictionary.

Usage Example: d.Remove "Canada"

The Key property procedure is useful for editing a key. It accepts a key value as a parameter, locates the existing key, and replaces it with the new one.

Usage Example: d.Key("Kanada")="Canada"

The Count Property Procedure returns a count of the total number of items in the Dictionary Object.

Usage example: i = d.Count

Let us make a copy of the first VBA Code and try out the above method and property procedures in the VBA Code.

Modified Code.

Public Sub Dict_Test0_4()
Dim d As Dictionary
Dim mkey, j As Long
Dim strheader 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 "KANADA", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

'the d.Count statement adds Item count to the header
strheader = "BEFORE CHANGE - " & "TOTAL ITEMS: " & d.Count
GoSub print_section

'Correction to Key value Kanada changes to Canada
   d.key("Kanada") = "Canada"

'With the following two statement
'an Item is replaced with a new Key.
'This has the same effect as deleting an Item
'and Adding another item in the same place

mkey = "France"
If d.Exists(mkey) Then
   d.key(mkey) = "India"
   d.item("India") = "New Delhi"
Else
   MsgBox "Key: " & mkey & " Not Found!"
End If

'Remove the Item Denmark from Dictionary Object
mkey = "Denmark"
If d.Exists(mkey) Then
   d.Remove mkey
Else
   MsgBox "Remove Key: '" & mkey & "' Invalid." & vbCr & "Item not Removed!"
End If

'the d.Count statement adds Item count to the header
strheader = "AFTER CHANGE - " & "TOTAL ITEMS: " & d.Count
GoSub print_section

d.RemoveAll 'clear Dictionary Object from memory

Exit Sub

print_section:

Debug.Print
Debug.Print strheader
Debug.Print "S.No.", "Country", "Capitals"
Debug.Print "-----", "-------", "---------"
j = 1
For Each mkey In d.Keys
   Debug.Print j, mkey, d.item(mkey) 'RETRIEVES Value syntax: d.item(mkey)
   j = j + 1
Next
Return

End Sub

The d.Remove Property Procedure requires a valid Key parameter with/without brackets. If not found among a list of Keys, then it will end up with Errors. We perform a validation check to ensure that the Key exists in the Dictionary Object before attempting to delete the Item.

The d.key statement also requires a valid key value; otherwise, it will result in an error. Therefore, in the following code segment, we first perform a validation check to ensure that the specified key exists in the Dictionary before attempting to replace it with another value. If the key is not found, a message is displayed to inform the user, preventing the program from crashing.

mkey = "France"
If d.Exists(mkey) Then
   d.key(mkey) = "India"
   d.item("India") = "New Delhi"
Else
   MsgBox "Key: " & mkey & " Not Found!"
End If

By using the d.Key() and d.Item() statements together, we can fully replace an unwanted item in the Dictionary by updating both its key and its associated value.

Unlike the Collection Object, the true strength of the Dictionary Object lies in its use of Keys. Keys allow us to retrieve items quickly and at random from the Dictionary. In addition, the Dictionary Object offers a rich set of methods and property procedures to efficiently manage its contents.