Sorting Dictionary Object Keys and Items ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, February 21, 2019

Sorting Dictionary Object Keys and Items

Introduction.

Sorting or indexing table records is a commonly used and important task of organizing data in the correct order, enabling faster retrieval of information through index keys.

In the case of the Dictionary Object, however, there is already a built-in mechanism to directly retrieve information using its unique keys.

Still, if you would like to learn how to sort the values stored in a Dictionary Object, let’s try a simple demonstration. We will reuse the sample data created in earlier example programs as input for our sorting routine. The sample VBA code with the test data is shown 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 entered in alphabetical order. However, in this example, we have intentionally mixed up their order. We will pass this unsorted data to a sorting routine and retrieve it back in alphabetical order.

Unfortunately, the Dictionary Object does not provide a built-in way to rearrange its data directly. To achieve sorting, we must first copy the keys and their corresponding item values from the Dictionary into two separate arrays. These arrays can then be passed to a sorting routine to return the data in the desired order.

The Coding Steps.

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

Take a Listing of Unsorted Data from the Dictionary Object.
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).
Read Dictionary Keys and Item Values and load them into separate Arrays.
Pass the Arrays to the Sort Routines as ByRef Parameters.
Remove the existing Dictionary Object and instantiate it as a new dictionary Object, with the same name.
Read the Sorted Keys and Items from the Array and Add them to the new Dictionary Object.
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() Sorts The Data Quickly.

If the 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 notice any significant difference in execution time between the two programs with this small dataset. However, when working with a large volume of data, the QuickSort method completes the task in only a fraction of the time taken by the Bubble Sort program.

In these sorting procedures, the Keys and their corresponding Item values are first copied into two separate arrays before being passed to the sorting routine. Once the data is sorted, it is added back into a new Dictionary Object, and the old one is removed.

We can achieve the same result using a simpler approach. We only need to sort the Keys in the desired order—either ascending or descending. Using these sorted Keys, we can retrieve the corresponding Item values from the original Dictionary and add them to a new Dictionary Object in the sorted order. Finally, the old unsorted Dictionary Object can be discarded.

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 first loaded into the vKey() Variant Array. The Bubble Sort procedure then rearranges the Keys in the desired order.

Using these sorted Keys, the corresponding Item values are retrieved from the original Dictionary Object and written into a new Dictionary Object, maintaining the order of the sorted country names.

In the subsequent printing section, the sorted country names and their capitals are printed in the Debug Window from the new Dictionary Object.

However, do we really need to recreate a new Dictionary Object after sorting the Keys? Not necessarily. Since Dictionary Items can be accessed randomly using their Keys, it is sufficient to sort only the Keys and hold them in an Array. You can then use the sorted Keys to retrieve the Items from the existing Dictionary in the desired order (A–Z or Z–A). I’ll leave this approach as an exercise for you to try on your own.

LEARN MS-ACCESS TIPS AND TRICKS

Thursday, February 21, 2019