Dictionary Object Basics

Introduction to the Dictionary Object.

A Dictionary object is a VBA object that stores data as key-item pairs, where each unique key is associated with a corresponding value or object. Unlike a Collection, which is primarily index-based, a Dictionary is designed for fast data retrieval using unique keys.

The Dictionary object is provided by the Microsoft Scripting Runtime library (Scripting.Dictionary). It is particularly useful and efficient when data must be searched, updated, or verified based on a unique identifier. 

Key Characteristics

1. Key-item storage – Each entry consists of a unique key and its associated item (value/object).

2. Fast key lookup – Items are retrieved directly by key without searching sequentially through the collection.

3. Dynamic size – Entries can be added or removed at runtime.

4. Unique keys – Duplicate keys are not permitted.

5. Flexible item types – Items can be simple data types, arrays, or object references.

6. Modifiable keys – Existing keys can be renamed using the Key property.

By now, I hope you have reviewed the recent articles on using the Collection Object in Microsoft Access. Even if you haven’t, you should still be able to follow along with the Dictionary Object and its usage. Collection and Dictionary Objects share many similarities, but understanding their differences will help you decide which one is best suited for a particular task.

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

• MS Access and Collection Object Basics.
• MS Access Class Module and Collection Object.
• Table Records in Collection Object and Form.

The Dictionary Object is not natively part of Microsoft Access VBA; it originates from VBScript, commonly used on web pages. To use a Dictionary Object in Access, we need to create it in a VBA program. There are two ways to accomplish this in Microsoft Access VBA:

A.   With the use of the MS Access function CreateObject().

Dim d As Object

Set d = CreateObject("Scripting.Dictionary")

This approach has a minor drawback for beginners: IntelliSense will not display the Dictionary Object’s methods and properties because the object is declared as a generic Object type.

Dictionary Object Library File.

B.  But there is a better method. Add the Microsoft Scripting Runtime Library to the selected existing list of Libraries in Microsoft Access.

After 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 the VBA Window. 

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



Right-click to open the large image in a New Tab or Window.

3. The check-marked item (Microsoft Scripting Runtime) is the Library File you need to look for in your System.  The unchecked items are in alphabetical order.

4. Move the Scrollbar down and find the file Microsoft Scripting Runtime, select it, and click the OK Button to exit.

Now, you can declare and instantiate a Dictionary Object with IntelliSense support.

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

Exists      -    Verifies that the specified key exists.

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

Item     -        Retrieve/Replace/Add the item with the specified key.  If the specified key doesn’t exist, the Item value is added to the Dictionary under 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. Insert a Stop statement immediately below the text USA, Washington, D.C. Add a statement.

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

4. Click anywhere in the Code, and press the F5 key to run the Code.

The Program pauses at the Stop statement.

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

Press the F5 Key again to continue executing the Code.

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

You can type the country name in uppercase, lowercase, or in mixed form.  The compare mode setting at the beginning compares the Key value entered with the list of keys in the Dictionary Object.

Enter the letter Q to exit 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 to 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 to retrieve, replace an Item, or a Key-Value.

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

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

In the 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, display 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 three names are different Keys.

When the Add method finds that the specified Key value doesn’t match any existing Keys, the new Key is added to the Item Value in the Dictionary Object.

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

Take a Listing of All Items.

Let us create a list of all the Items, with the method explained above.  We will copy the code above 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 list 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

Read More

Wrapper Class Functionality Transformation

Wrapper Class and Creation of New Objects.

Last week, we created a new Wrapper Class named ClsTiles, which used the ClsArea Class twice within the same Class Module—one instance to store the room’s floor dimensions and another to store the tile dimensions—to calculate the number of tiles needed for the room.

The Wrapper Class Module ClsVolume2 and transform it into a new ClsSales Class. With a complete facelift to ClsVolume2 and repurposing it to calculate the product's selling price with a discount.

Interestingly, the ClsVolume2 Class already has all the necessary properties to store sales-related data: strDesc, dblLength, dblWidth, and dblHeight can be repurposed to represent Description, Quantity, Unit Price, and Discount Percentage, respectively.

Remember, the ClsVolume2 Class is a derived class, originally built using ClsArea as its base class.ClsVolume2 Class Revisited.

But, first, the VBA Code of the ClsVolume2 Class Module  (the Base Class for our new ClsSales Class Module) is reproduced below for reference:

Option Compare Database
Option Explicit

Private p_Height As Double
Private p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

Private Sub Class_Initialize()
Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub 

The only obstacle preventing us from using the ClsVolume2 Class directly for sales data entry is that its Property Procedure names—dblLength, dblWidth, and dblHeight—do not correspond to the required sales-related properties: Quantity, Unit Price, and Discount Percentage.

Fortunately, all these properties in the ClsVolume2 Class are of the Double Data type, which is suitable for our sales calculations.

Similarly, the public function names Area() and Volume() are not meaningful in the context of sales, but their underlying calculation logic can be repurposed for sales-related computations without modification.

a) Area = dblLength * dblWidth is suitable for TotalPrice = Quantity * UnitPrice

b) Volume = Area * dblHeight is good for DiscountAmount = TotalPrice * DiscountPercentage

Here, there are two choices to repurpose the ClsVolume2 as a ClsSales Class:

1. The easiest approach is to create a copy of the ClsVolume2 Class and save it as a new Class Module named ClsSales. Then, update the Property Procedure names and public Function names to reflect sales-related values and calculations. You can also add any additional functions needed for handling sales operations directly within this new class module.

2. Create a Wrapper Class based on the ClsVolume2 Base Class. In the Wrapper Class, implement corresponding property procedures and public functions that encapsulate and rename the Base Class’s property procedures and functions, effectively masking their original names. Add new functions to the Wrapper Class as needed.

The first option is relatively straightforward and easy to implement. However, we will choose the second option to learn how to access the Base Class’s properties within the new Wrapper Class and how to mask their original names with new property names.

The Transformed ClsVolume2 Class.

1. Open your Database and display the VBA Editing Window (Alt+F11).

2. Select the Class Module from the Insert Menu to insert a new Class Module.

3. Change the Class Module’s Name property Value from Class1 to ClsSales.

4. Copy and Paste the following VBA Code into the Module and Save the Code:

   Option Compare Database
   Option Explicit
   
   Private m_Sales As ClsVolume2
   
   Private Sub Class_Initialize()
       'instantiate the Base Class in Memory
       Set m_Sales = New ClsVolume2
   End Sub
   
   Private Sub Class_Terminate()
       'Clear the Base Class from Memory
       Set m_Sales = Nothing
   End Sub
   
   Public Property Get Description() As String
     Description = m_Sales.CArea.strDesc 'Get from Base Class
   End Property
   
   Public Property Let Description(ByVal strValue As String)
     m_Sales.CArea.strDesc = strValue ' Assign to Base Class
   End Property
   
   Public Property Get Quantity() As Double
   Quantity = m_Sales.CArea.dblLength
   End Property
   
   Public Property Let Quantity(ByVal dblValue As Double)
      If dblValue > 0 Then
        m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
      Else
        MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
        Do While m_Sales.CArea.dblLength <= 0
             m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
        Loop
      End If
   End Property
   
   Public Property Get UnitPrice() As Double
      UnitPrice = m_Sales.CArea.dblWidth
   End Property
   
   Public Property Let UnitPrice(ByVal dblValue As Double)
      If dblValue > 0 Then
         m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
      Else
        MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
          Do While m_Sales.CArea.dblWidth <= 0
             m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
          Loop
      End If
   End Property
   
   Public Property Get DiscountPercent() As Double
      DiscountPercent = m_Sales.dblHeight
   End Property
   
   Public Property Let DiscountPercent(ByVal dblValue As Double)
   ' Assign to Class .dblHeight of ClsVolume2
   Select Case dblValue
       Case Is <= 0
          MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
          Do While m_Sales.dblHeight <= 0
             m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
          Loop
       Case Is >= 1
          m_Sales.dblHeight = dblValue / 100
       Case 0.01 To 0.75
          m_Sales.dblHeight = dblValue
   End Select
   End Property
   
   Public Function TotalPrice() As Double
   Dim Q As Double, U As Double
   Q = m_Sales.CArea.dblLength
   U = m_Sales.CArea.dblWidth
   If (Q * U) = 0 Then
      MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
   Else
      TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
   End If
   End Function
   
   Public Function DiscountAmount() As Double
      DiscountAmount = TotalPrice * DiscountPercent
   End Function
   
   Public Function PriceAfterDiscount()
      PriceAfterDiscount = TotalPrice - DiscountAmount
   End Function
    

So far in the Wrapper Class, we have created an instance of the ClsVolume2 class, renamed its property and function members to more suitable names, and added validation checks with appropriate error messages. We also ensured that invalid input does not trigger the Base Class’s own validation routines, which could otherwise display error messages like “Value of the dblLength property is invalid” from the ClsVolume2 class.

Review the highlighted lines in the code above; they should help you understand how the property values are assigned to and retrieved from the Base Class ClsVolume2.

First, review the ClsArea Class Module, and then examine the ClsVolume2 Class Module, which is derived from ClsArea. After understanding both of these, revisit the code in this Wrapper Class for a better perspective.

Test Program for ClsSales Class in Standard Module.

Let us write a Test Program to try out the Wrapper Class.

1. Copy and Paste the following VBA Code into a Standard Module.

   Public Sub SalesTest()
   Dim S As ClsSales
   
   Set S = New ClsSales
   
   S.Description = "Micro Drive"
   S.Quantity = 12
   S.UnitPrice = 25
   S.DiscountPercent = 0.07
   
   Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
   With S
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
   End Sub

   Run The Code.

2. Keep the Debug Window open (Ctrl+G).

3. Click in the Code, press the F5 key to run the code, and print the output in the Immediate window.

4. You can further test the code by entering negative numbers for any input value and running it to trigger the new error messages. You may also try disabling one or more input lines by placing a comment symbol (') at the beginning of the line, then rerun the code and observe the results.

Calculate Price/Discount for an Array of Products.

The following test code creates an array of three Products and Sales Values by entering directly from the Keyboard. 

Copy and paste the following Code into a Standard Module, and run to test the Wrapper Class.

Public Sub SalesTest2()

Dim S() As ClsSales
Dim tmp As ClsSales
Dim j As Long

For j = 1 To 3
Set tmp = New ClsSales
   tmp.Description = InputBox(j & ") Description")
   tmp.Quantity = InputBox(j & ") Quantity")
   tmp.UnitPrice = InputBox(j & ") UnitPrice")
   tmp.DiscountPercent = InputBox(j & ") Discount Percentage")
 ReDim Preserve S(1 To j) As ClsSales
 Set S(j) = tmp
 Set tmp = Nothing
Next

'Output Section
Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
For j = 1 To 3
   With S(j)
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
Next

For j = 1 To 3
  Set S(j) = Nothing
Next

End Sub

Once the correct values have been successfully entered into the array, the product names and corresponding sales values are displayed in the Debug Window.

CLASS MODULES.

Right-click to open Large Image in New Tab/Window.

Demo Database Download

WrapperClass.zip

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
8. Wrapper Class Functionality Transformation

COLLECTION OBJECT.

1. MS-Access and Collection Object Basics
2. MS-Access Class Module and Collection Object
3. Table Records in Collection Object and Form

DICTIONARY OBJECT.

1. Dictionary Object Basics
2. Dictionary Object Basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary to Form
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item on Form

Read More