Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Sorting Dictionary Object Keys and Items

Introduction.

Sorting/Indexing of Table Records is a frequently used important exercise to organize the data in proper order, for faster retrieval of information, using Index Keys.

As far as Dictionary Object is concerned there is already a perfect built-in mechanism to retrieve information directly, using its unique Keys.

But, if you would like to know how to sort the Dictionary Object values, then let us go for a trial run. We have some sample data created in the earlier demonstration programs and will use those sample values as input to our sort program.  The sample VBA Code with sample data is given below.

The DSort_Test() Main Procedure.

Public Sub DSort_Test()
Dim d As Dictionary
Dim mkey
Dim Title As String
Dim i As Long
Dim vKey() As Variant, vItem() As Variant

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 "Belgium", "Brussels"
d.Add "Italy", "Rome"
d.Add "Canada", "Ottawa"
d.Add "USA", "Washington D.C."
d.Add "Denmark", "Copenhagen"
d.Add "Australia", "Canberra"
d.Add "France", "Paris"
d.Add "Saudi Arabia", "Riyadh"

Title = "UNSORTED LISTING"
GoSub Output_Section

ReDim vKey(1 To d.Count) As Variant
ReDim vItem(1 To d.Count) As Variant

'Load Key,Item pairs into two Variant Arrays
i = 1
For Each mkey In d.Keys
  vKey(i) = mkey
  vItem(i) = d(mkey)
  i = i + 1
Next

'Pass the Array to Bubble Sort Program
Call DBubbleSort(vKey, vItem)

d.RemoveAll 'Remove existing Dictionary Object
Set d = New Dictionary 'instantiate new Dictionary Object

'Re-create Dictionary Object with Sorted Array contents
For i = 1 To UBound(vKey)
   d.Add vKey(i), vItem(i)
Next

Title = "LISTING AFTER SORT"
GoSub Output_Section

Exit Sub

Output_Section:
'Print Sorted Dictionary Object contents
Debug.Print
Debug.Print Title
Debug.Print "---------------------"

For Each mkey In d.Keys
   Debug.Print mkey, d(mkey)
Next
Return

End Sub

In our earlier programs, the Dictionary Keys (Country Names) were manually created in Alphabetical Order.  But, here I have re-arranged them into mixed order.  We will pass these data to the Sorting routine and get them back in Alphabetical order.

Unfortunately, there is no way we can re-arrange data directly within the Dictionary Object.  We have to copy the data (Keys and Item Values) from the Dictionary into two separate Arrays before passing them to the sort routine to get them back in an arranged order.

The Coding Steps.

The Algorithm of the Code segment, after creating the Dictionary Data items in the above program, is given below.

  1. Take a Listing of Unsorted Data from the Dictionary Object.

  2. Define two Array Variables: One for Keys and another for Item Values (if  Items are  Objects then the second declaration must be for an Object of the Item’s Type).

  3. Read Dictionary Keys and Item Values and load them into separate Arrays.

  4. Pass the Arrays to the Sort-Routines as ByRef Parameters.

  5. Remove the existing Dictionary Object and instantiate it as a new dictionary Object, with the same name.

  6. Read the Sorted Keys and Items from Array and Add them to the new Dictionary Object.

  7. Take the listing of sorted data from the recreated Dictionary Object.

BubbleSort() Routine.

The Bubble-Sort VBA Code is given below:

Public Sub DBubbleSort(varKey() As Variant, varItem() As Variant)
Dim j As Long, k As Long
Dim tmp1 As Variant, tmp2 As Variant

For j = 1 To UBound(varKey) - 1
   For k = j + 1 To UBound(varKey)
      If varKey(k) < varKey(j) Then 'change < to > for Descending Order
      
'save first Key, Item value pairs in temporary variable
          tmp1 = varKey(j)
          tmp2 = varItem(j)

'replace first set of values with second value set
          varKey(j) = varKey(k)
          varItem(j) = varItem(k)
          
'replace second value set with saved values
          varKey(k) = tmp1
          varItem(k) = tmp2
      End If
   Next k
Next j

End Sub

The Unsorted and Sorted listing dumped on the Debug window image is given below:

UNSORTED LISTING
---------------------
Belgium       Brussels
Italy         Rome
Canada        Ottawa
USA           Washington D.C.
Denmark       Copenhagen
Australia     Canberra
France        Paris
Saudi Arabia  Riyadh

LISTING AFTER SORT
---------------------
Australia     Canberra
Belgium       Brussels
Canada        Ottawa
Denmark       Copenhagen
France        Paris
Italy         Rome
Saudi Arabia  Riyadh
USA           Washington D.C.

The Dictionary Keys, with Item Values, are sorted in Ascending Order

Sorting Reverse Order (Z-A).

With a slight change in the Key comparison statement, we can make the program sort the items in Descending Order.  Replace the Less Than Symbol (<) with the Greater Than Symbol (>), in the DBubbleSort program to Sort the items in Descending Order, as shown below.

Existing comparison statement:

If varKey(k) < varKey(j) Then

change to

If varKey(k) > varKey(j) Then

The QuickSort() that Sorts Data Quickly.

If Dictionary Object contains a large volume of data, then Bubble-Sort may not be as efficient as the QuickSort method.  We have the QuickSort Program too for sorting Dictionary Data. 

Sample QuickSort VBA Code is given below:

Public Function DictQSort(DxKey As Variant, DxItem As Variant, lngLow As Long, lngHi As Long)
Dim tmpKey As Variant, tmpItem As Variant, midKey As Variant
Dim t_Low As Long, t_Hi As Long

midKey = DxKey((lngLow + lngHi) \ 2)
t_Low = lngLow
t_Hi = lngHi

While (t_Low <= t_Hi)
   While (DxKey(t_Low) < midKey And t_Low < lngHi)
      t_Low = t_Low + 1
   Wend
  
   While (midKey < DxKey(t_Hi) And t_Hi > lngLow)
      t_Hi = t_Hi - 1
   Wend

   If (t_Low <= t_Hi) Then
      tmpKey = DxKey(t_Low)
      tmpItem = DxItem(t_Low)
      
      DxKey(t_Low) = DxKey(t_Hi)
      DxItem(t_Low) = DxItem(t_Hi)
      
      DxKey(t_Hi) = tmpKey
      DxItem(t_Hi) = tmpItem
      
      t_Low = t_Low + 1
      t_Hi = t_Hi - 1
   End If
   
  If (lngLow < t_Hi) Then DictQSort DxKey, DxItem, lngLow, t_Hi 'recursive call
  If (t_Low < lngHi) Then DictQSort DxKey, DxItem, t_Low, lngHi 'recursive call
Wend
End Function

You may run the DictQSort() Program from the main Program DSort_Test(), by replacing the statement that calls the DBubbleSort() Sub-Routine, with a Call to the DictQSort() Function, as shown below:

Replace:

Call DBubbleSort(vKey, vItem)

with

Call DictQSort(vKey, vItem, LBound(vKey), UBound(vKey))

You may not find any major differences in both program execution times here.  But, when there is a large volume of data in the Dictionary Quick-Sort method takes only a fraction of the time taken by the Bubble Sort program.

For the above sorting procedures, we have loaded the Keys and Item Values into two separate Arrays, before passing them to the sorting program.  After getting the data sorted they are added into a new Dictionary Object and the old one is deleted.

We can do this in a different way and get the same result.  We need only the Keys to be sorted in the required order, either in Ascending or Descending.  With the sorted keys, we can retrieve the Item values from the original Dictionary and write them into a new Dictionary Object in the sorted order and delete the old unsorted Dictionary Object.

The modified version of the top program, with a built-in Bubble Sort Code, is given below.

Public Sub DSort_Test2()
Dim d As Dictionary
Dim y As Dictionary
Dim mkey, j As Long, k As Long
Dim Title As String
Dim i As Long
Dim vKey() As Variant

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 "Belgium", "Brussels"
d.Add "Italy", "Rome"
d.Add "Canada", "Ottawa"
d.Add "USA", "Washington D.C."
d.Add "Denmark", "Copenhagen"
d.Add "Australia", "Canberra"
d.Add "France", "Paris"
d.Add "Saudi Arabia", "Riyadh"

Title = "UNSORTED LISTING"
'Print Unsorted Dictionary Object contents
Debug.Print
Debug.Print Title
Debug.Print "---------------------"

For Each mkey In d.Keys
   Debug.Print mkey, d(mkey)
Next

ReDim vKey(1 To d.Count) As Variant
'Load Keys into Variant Array
i = 1
For Each mkey In d.Keys
  vKey(i) = mkey
  i = i + 1
Next
'Bubble Sort the Keys in Ascending Order
For j = 1 To UBound(vKey) - 1
   For k = j + 1 To UBound(vKey)
       If vKey(k) < vKey(j) Then 'Ascending Order
          mkey = vKey(j)
            vKey(j) = vKey(k)
          vKey(k) = mkey
       End If
    Next k
Next j
'end of Sort

'create sorted Data in a new Dictionary Object
Set y = New Dictionary
For j = 1 To UBound(vKey)
  y.Add vKey(j), d(vKey(j))
Next

'Delete old unsorted Dictionary Object d
d.RemoveAll

Debug.Print
Title = "LISTING AFTER SORT"
Debug.Print Title
Debug.Print "---------------------"
For Each mkey In y.Keys
   Debug.Print mkey, y(mkey)
Next

End Sub

In this example, the Dictionary Keys are loaded into the vKey() Variant Array.  The Bubble Sort procedure rearranges the Keys in the required Order.

With the sorted Keys the corresponding Item Values are retrieved from the old Dictionary Object and write the Key and Item values into a new Dictionary Object, in the order of the sorted country names.

In the next printing section, the sorted Country names and Capitals are printed on the Debug Window, from the new Dictionary Object.

Do we need to recreate a new Dictionary Object after sorting the Keys? I think it is not necessary. Since we can retrieve Dictionary Items randomly with Keys, we need to sort only the Keys and hold them in an Array. Use the Sorted Keys to retrieve the Items from the Dictionary in the order you want them, in A-Z or Z-A order. I leave that to you, as an exercise, to do it on your own.

MS-ACCESS CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes

    COLLECTION OBJECT

  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form

    DICTIONARY OBJECT

  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form
Share:

Dictionary Object Basics-2

Introduction.

Last week’s introduction to Dictionary Object was, I hope, a good start and informative for this week's topics and to learn the fundamentals of this Object’s usage in Programs.  The sample VBA Code presented there demonstrates the usage of a few Properties and Methods in the simplest form and I presume that you have already tried them out.

If not, visit last week’s page by following this link: Dictionary Object Basics before continuing.

The list of Properties and Methods given below was familiarized through last week’s demonstration programs.  You may click the above link for a review if needed, or for a fresh look at it before continuing with this page.

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 Method, which remains to try 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.  Item is a multipurpose Property Procedure and works for more than one function. 

  1. It retrieves the Item value for output purposes.  Key-Value is Item Parameter.

    Example:

  2. Debug.Print d.Item("Canada")

    OR

    x = d.Item("Canada") Result: x = "Ottawa"
  3. It replaces an Item value, matching the specified Item Key.

    Example:

  4. d.Item("Canada") = "Alberta" ‘ Ottawa replaced with Alberta
  5. 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 the statement, if the Key provided doesn’t match with any Key in Dictionary Object.

    Example:

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

We will bring the sample code, we 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 demonstrates 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 are called the print_section sub-routine to take a listing of the unchanged items in Debug Window, from Dictionary Object d

In the print_section sub-routine, 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 the wrong capital 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 is similar to the replacement statement, we have used for replacing Canada’s capital Ottawa.  When the search for Key India fails, it will not generate any error, indicating that the search for the Key failed,  but it silently adds unmatched Key India and Item value New Delhi to the Dictionary Object as a new item.

In the next step, we call the print_section sub-routine again to take the changed listing of items in the Debug Window.  The d.RemoveAll statement deletes the Dictionary Object from memory before stopping the program.

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

  1. Canada’s Capital is changed from Alberta to Ottawa.
  2. India and the New Delhi pair are added as a new Item, in 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 that run 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 Remove Method, Key, and Count Property Procedures. 

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

Usage Example:  d.Remove "Canada"

The Key Property procedure is good for editing a Key, accepts a key value as Parameter, finds, and replaces the old key 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 needs a valid Key-value, otherwise, it will end up with errors.  So, we perform a validation check in the following Code segment to ensure that the key exists in the Dictionary before attempting to replace the Key with another value.  If the specified key is not found, then gives a message to that effect, to safeguard against the crash of the program.

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

With the combination of d.Key() and d.Item() statements,  in the above code snippet, we can completely replace an unwanted item, by overwriting its Key and Item Value.

Unlike the Collection Object, the power lies in the Keys of the dictionary Object. The Key enables us to retrieve an Item randomly and quickly from Dictionary Object.  Besides that, the Dictionary Object has a rich collection of Methods and Property Procedures to manage its contents.

MS-ACCESS CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes

    COLLECTION OBJECT

  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form

    DICTIONARY OBJECT

  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form
Share:

Dictionary Object Basics

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.

  1. Select the References option from the Tools Menu in VBA Window. 

  2. The sample display of Library Files is given below. 

  3. 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.

  4. 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

OR

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.

  1. 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.

  2. Put a Stop statement immediately below the USA, Washington D.C. Add statement.

  3. Select the Locals Window option from the View Menu.

  4. 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.

MS-ACCESS CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes

    COLLECTION OBJECT

  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form

    DICTIONARY OBJECT

  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code