ACC2000: How to Use Collections to Manage Class Objects in VBA

MORE INFORMATION

• Create an instance of the class
• Set the properties and methods of the class
• Add the class to a public collection
• Unload the instance of the class

Create a Class Module

1. Create a new database called ClassTest.mdb.
2. On the Insert menu, click Class Module.
3. Save the class module as clsTest.
4. Type the following lines in the Declarations section:

     Private This_ClassID As String
     Private This_frm As New Form_frmTest
   
   					

5. Select Class in the Object box of the Module window. "Initialize" is automatically selected in the Procedure box.
6. Type the following procedure:

   Private Sub Class_Initialize()
      On Local Error GoTo Class_Initialize_Err
      Dim Msg As String
      This_frm.Visible = True
      This_ClassID = "Initialized"
      This_frm.Caption = This_ClassID
      MsgBox "Class Initialized", vbInformation, "Class Example"
   Class_Initialize_End:
      Exit Sub
   Class_Initialize_Err:
      Msg = "Error #: " & Format$(Err.Number) & vbCrLf
      Msg = Msg & Err.Description
      Err.Raise vbObjectError, "clsTest.Initialize (Private)", Msg
      Resume Class_Initialize_End
   End Sub
   					

7. On the Insert menu, click Procedure.
8. In the Insert Procedure dialog box, type ClassID in the Name box and click Property in the Type box. Then type the following procedures

     Public Property Get ClassID() As Variant
        ClassID = This_ClassID
     End Property
   
     Public Property Let ClassID(ByVal vNewValue As Variant)
        This_ClassID = vNewValue
        This_frm.ClassID = This_ClassID
        This_frm.Caption = This_ClassID
     End Property
   
   					

9. Save and close the clsTest class module.

Create a Form

1. Create the following form not based on any table or query in Design view:

      Form: frmTest
      -----------------
      Caption: TestForm
   					

2. With the form still open in Design view, click Code on the View menu.
3. Type the following line in the Declarations section of the form's class module:

   Dim This_ClassID As String
   					

4. Add the following event procedure to the form's Unload property:

   Private Sub Form_Unload(Cancel As Integer)
       col.Remove This_ClassID
   End Sub
   					

5. On the Insert menu, click Procedure.
6. In the Insert Procedure dialog box, type ClassID in the Name box and click Property in the Type box. Then type the following procedures. Note that the Get ClassID() function and vNewValue variable in this example are dimensioned as String instead of the default, which is Variant:

   Public Property Get ClassID() As String
      ClassID = This_ClassID
   End Property
   
   Public Property Let ClassID(ByVal vNewValue As String)
      This_ClassID = vNewValue
   End Property
   					

7. Save and close the frmTest form.

Create a Standard Module

1. Create a new standard module and save it as Module1.
2. Type the following line in the Declarations section:

   Public col As New Collection
   					

3. Type the following procedure:

   Function CreateClassTest() As String
      ' Create an instance of the clsTest class module, which creates
      ' an instance of the frmTest form.
      Dim cls As New clsTest
   
      ' Create a unique identifier string and set it to the upper index
      ' of the Public col Collection plus one.
      Dim varClassId As String
      varClassId = "Key_" & CStr(col.Count + 1)
   
      ' Set the clsTest class module's ClassID property to the value of
      ' varClassId, which in turn sets the frmTest.ClassId property to
      ' the same value. This is so the form has a method to track its
      ' relationship to the collection.
      cls.ClassID = varClassId
   
      ' Add the instance of the class object to the collection passing
      ' varClassId as the Key argument.
      col.Add cls, varClassId
   
      MsgBox "Created New Collection Item: " & varClassId, _
           vbInformation, "Class Example"
   
      ' Unload the cls object variable.
      Set cls = Nothing
   
      ' Return the varClassId.
      CreateClassTest = varClassId
   End Function
   					

4. Close and save the module.

Test the Example

1. Create a standard module and type the following procedure:

   Function CreateThreeItems() As Boolean
      Dim strKeys(1 To 3) As String
      Dim i As Integer
      For i = LBound(strKeys) To UBound(strKeys)
         strKeys(i) = CreateClassTest()
      Next i
      For i = LBound(strKeys) To UBound(strKeys)
         MsgBox col.Item(strKeys(i)).ClassID, vbInformation, _
           "Class Test"
      Next i
   End Function
   					

2. To test this function, type the following line in the Immediate window, and then press ENTER:

    ?CreateThreeItems()
   						

   Note that messages boxes are displayed each time the clsTest class module initializes, when each of three instances of the frmTest form is created, and again after all three instances of the form are open.