Add Class Objects as Dictionary Items.
We have learned the fundamentals of the Dictionary Object, experimented with sorting simple items, and displayed Access table records on an MS Access Form from the Dictionary Object.
Now, let us move a step further to learn how to add MS Access Class Objects to a Dictionary, retrieve each object item, and display their Property values and Method outputs in the Debug Window.
If you would like to revisit the earlier related articles for reference, their links are provided below.
We need to have the ClsArea class object in the database to try out this example using the Dictionary object.
If it is not already present, you can create it by following these steps:
-
Open the VBA Editor.
-
Insert a new Class Module.
-
In the Properties Window, change the (Name) property of the new class to ClsArea.
-
Copy and paste the following VBA code into the ClsArea class module and save it.
Option Compare Database
Option Explicit
Private p_Desc As String
Private p_Length As Double
Private p_Width As Double
Public Property Get strDesc() As String
strDesc = p_Desc 'copy the value from p_Desc
End Property
Public Property Let strDesc(ByVal strNewValue As String)
p_Desc = strNewValue
End Property
Public Property Get dblLength() As Variant
dblLength = p_Length
End Property
Public Property Let dblLength(ByVal dblNewValue As Variant)
Do While Val(Nz(dblNewValue, 0)) <= 0
dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
Loop
p_Length = dblNewValue
End Property
Public Property Get dblWidth() As Variant
dblWidth = p_Width
End Property
Public Property Let dblWidth(ByVal dblNewValue As Variant)
Do While Val(Nz(dblNewValue, 0)) <= 0
dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
Loop
p_Width = dblNewValue
End Property
Public Function Area() As Double
If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
Area = Me.dblLength * Me.dblWidth
Else
Area = 0
MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
End If
End Function
Private Sub Class_Initialize()
p_Length = 0
p_Width = 0
'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub
Private Sub Class_Terminate()
'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub
The ClsArea Class object has three properties — strDesc, dblLength, and dblWidth — and one method: Area().
We will create multiple instances of this class to represent different rectangular shapes or rooms, and calculate their areas using the Area() method, and add each class object instance as an Item in a Dictionary object.
The value stored in the class object's strDesc property will be used as the Key for each corresponding item in the dictionary.
Since dictionary keys must be unique, make sure that the strDesc property values are not duplicated.
For example, if you have several bedrooms to calculate areas for, you can name them Bedroom1, Bedroom2, Bedroom3, and so on.
The ClassObjInDictionary() Procedure Code.
Let us try the Dictionary with the Class Module Object ClsArea as Items. The sample VBA Code for the Dictionary Object is given below. Copy and paste it into a Standard Module and save the Module.
Public Sub ClassObjInDictionary()
'--------------------------------------------------
'Add Class Object as Items to Dictionary Object
'Retrieve the Class Object from Dictionary Object
'and Print the values in the Debug Window.
'--------------------------------------------------
Dim C As ClsArea
Dim D As Object, Desc As String, mKey
Set D = CreateObject("Scripting.Dictionary")
D.CompareMode = 1
Desc = ""
Do While Not Desc = "Q"
'instantiate Class Object
Set C = New ClsArea
'Get input Values for ClsArea Object\
Do While Len(Desc) = 0
Desc = InputBox("Description or Q=Quit:")
Loop
If Desc = "Q" Then Exit Do
C.strDesc = Desc
C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
'add to Dictionary
'Description is added as Key of Dictionary Object
D.Add Desc, C
Desc = ""
'Clear Class Object
Set C = Nothing
Loop
If D.Count = 0 Then
MsgBox "No Data in Dictionary Object!" & vbCr & "Program Aborted."
Exit Sub
End If
'Output Section
Debug.Print "Key Value", "Description", "Length", "Width", "Area"
For Each mKey In D.keys
Set C = D(mKey)
Debug.Print mKey, C.strDesc, C.dblLength, C.dblWidth, C.Area
Next
End Sub
The VBA Code Line by Line.
At the beginning of the code, we create and instantiate the Dictionary object D.
We use the Desc string variable to capture the description text that will be assigned to the strDesc property of the class object. This same variable also acts as the control for the Do While ... Loop.
The loop continues to run until the user enters the single character Q (for Quit) into the Desc variable.
This trial allows you to add any number of class object instances into the Dictionary. When you are done, simply enter Q in the description prompt to exit the loop.
Next, we create an instance of the ClsArea class object using the object variable C.
Inside this loop, the statement Desc = InputBox() is placed within a second Do While ... loop. This ensures that the user actually enters a value into the Desc variable.
If the user presses Enter, clicks OK, or Cancel without entering any text, the InputBox() function will repeat the prompt until a valid value is entered.
The valid Description Value is assigned to the C.strDesc Property of the Class Object.
Through the next two InputBox() functions, collect the Length and width values of the Room from the user and assign them to C.dblLength and C.dblWidth Properties, respectively.
Now, the ClsArea Class Object is ready to be added to the Dictionary Object.
The statement D.Add Desc, C adds the current instance of the ClsArea Class Object in the Dictionary Object as its first Item to Desc (or C.strDesc Property Value) as the Key of the Dictionary Item.
Next, we clear the ClsArea Class Object instance C from memory.
You might have noticed that the Class Object instance C is created at the beginning of the outer Do While ... Loop, fills up the Class Object Property Values, adds it to the Dictionary Object, and C is Set to Nothing as the last statement within the Loop.
The Class Object C is a temporary Object that captures the data entry values before transferring the valid data into the Dictionary Object. Class Object C is reset to Nothing so it can be reinitialized for another Entry. That means we are creating a New Class Object instance for each Item in the Dictionary Object.
Why it has to be this way- creating new instances of the Class Object for each Item- is an important point to keep in mind.
The Class Object instance is added as an Item to the Dictionary; internally, the Class Object’s Location Address is saved in the Dictionary Object as a Pointer. The actual Class Object Property values are not moved to the Dictionary Object Item.
When we execute the statement Set C = Nothing, the Class Object instance C is cleared, but the instance’s location reference (pointer) is saved in the Dictionary Object Item. The actual ClsArea Class Object remains in that location, and we can retrieve it using the Object Pointer saved in the Dictionary Object Item.
When a new Class Object Instance is created, it is stored in a new memory location, and its reference is added to the Dictionary Object.
Enter a Description for a few bedrooms, Length, and Width Values to test the Code. Enter the letter Q to Quit and complete the Data entry when you are ready to take a listing in the Debug Window.
A sample Listing is given below:
Key Value Description Length Width Area
Bed Room1 Bed Room1 14 15 210
Bed Room2 Bed Room2 12 12 144
Living Room Living Room 23 24 552
Kitchen Kitchen 11 11 121
Store Room Store Room 21 14 294
In the printing code segment, we did not create a new ClsArea object (C) instance to read the class object pointers stored in the dictionary items. Instead, we directly accessed the stored object references from the dictionary and printed their values in the Debug Window.
Note: If you feel more comfortable doing so, you may create an ClsArea object instance (C) and assign the stored dictionary item reference to it. Both approaches work equally well.
The statement
reads the object reference (pointer) of the ClsArea object from the dictionary item into C. Once assigned, you can retrieve its property values and method output and print them to the Debug Window.
If you have already run the sample code and understood how it works, try a small modification:
-
Move the object creation and object cleanup (removal) statements for the ClsArea object outside the Do While...Loop.
-
Then, rerun the code, add a few items to the dictionary, and print their property values to verify the output in the Debug Window.
Take a Trial Run With the Following Changes in the Code
The Do While ... loop segment with suggested changes is below. Check the highlighted statements above and below the Do While ... Loop.
Desc = ""
Set C = New ClsArea
Do While Not Desc = "Q"
'instantiate Class Object
'Get input Values for ClsArea Object
Do While Len(Desc) = 0
Desc = InputBox("Description or Q=Quit:")
Loop
If Desc = "Q" Then Exit Do
C.strDesc = Desc
C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
'add to Dictionary
'Description is added as Key of Dictionary Object
D.Add Desc, C
Desc = ""
'Clear Class Object
Loop
Set C = Nothing
I moved the statement Set C = New ClsArea to a position above the Do While...Loop, and placed the Set C = Nothing statement below the Loop so that it executes only after completing the data entry of Class Objects into the Dictionary within the Do While...Loop.
I entered all five sample items listed earlier, using the same names but different values for Length and Width.
Finally, the printing section listed all five items in the Debug Window.
However, instead of showing the individual values entered for each item, only the last item's values are printed for all five entries.
Key Value Description Length Width Area
Bed Room1 Store Room 12 13 156
Bed Room2 Store Room 12 13 156
Living Room Store Room 12 13 156
Kitchen Store Room 12 13 156
Store Room Store Room 12 13 156
Why has it happened this way?
When we add a Class Object with its Properties to a Dictionary Object, only the reference (memory address) of the Class Object is stored in the Dictionary Item—not its actual Property values.
When an instance of the Class Object is created using the New keyword, that instance is assigned a fixed memory location (address). Any new values entered into its Properties will overwrite the previous values stored in that same instance. Each time this same object reference is added to the Dictionary, the Dictionary stores only the address of the Class Object, not a copy of its current Property values.
By contrast, if we create a new instance of the Class Object during each loop cycle, a fresh object is created, with a different memory address. The Dictionary stores these unique addresses, allowing each Item to retain its own distinct Property values.
When the statement Set C = Nothing is executed, it simply clears the reference from the object variable C so it no longer points to any location. However, the actual object data remains alive in memory because the Dictionary still holds a reference to it.
But when we moved the Set C = New ClsArea and Set C = Nothing statements outside the Do While...Loop, we ended up using only a single instance of the Class Object to input multiple sets of values. Each new set of Property values overwrote the previous ones in that same object. As a result, all the Dictionary Items ended up pointing to a single object instance, which holds only the last set of values entered.
Therefore, during printing, even though the Keys appear correctly in the listing, all the Items show the same (last entered) Property values.
Next week, we will learn how to add, edit, update, and delete Class Objects in the Dictionary through an MS Access Form.
MS-ACCESS CLASS MODULE
- MS Access Class Module and VBA
- MS Access VBA Class Object Arrays
- MS Access Base Class and Derived Objects
- VBA Base Class and Derived Objects-2
- Base Class and Derived Object Variants
- MS Access Recordset and Class Module
- Access Class Module and Wrapper Classes
COLLECTION OBJECT
- MS Access and Collection Object Basics
- MS Access Class Module and Collection Object
- Table Records in Collection Object and Form
DICTIONARY OBJECT
- Dictionary Object Basics
- Dictionary Object Basics-2
- Sorting Dictionary Object Keys and Items
- Display Records from Dictionary to Form
- Add Class Objects as Dictionary Items
- Update Class Object Dictionary Item on Form
