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 it’s 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 it 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 Dictionary into two separate Arrays before passing them to the sort routine to get them back in 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 Dictionary Object.
  2. Define two Array Variables: One for the Keys and the other 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 listing of  sorted data from 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 in Reverse Order (Z-A).

With a slight change on 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 large volume of data then Bubble-Sort may not be as efficient as 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’s execution time here.  But, when there is large volume of data in Dictionary Quick-Sort method takes only a fraction of the time taken by 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 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 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.  Bubble Sort procedure rearranges the Keys in required Order.

With the sorted Keys the corresponding Item Values are retrieved from the old Dictionary Object and writes 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 it, 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:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts