MsgBox pn.Item(n).Pname
Next n
End Sub
This creates a new instance of theMyPnamesobject. When this procedure is run, the first
thing it does isinitializethe class module, which means it picks up the data from the spreadsheet
and adds the objects to the collection.
The variablen, used in the For..Next loop, is defined as anintegerbecause it was defined
in theItemfunction in the collection as aninteger(index). An integer was used because this
is ideal for a For..Next loop variable. If you do not do this, you will get a Type Mismatch
error when the code is run.
You then set up a For..Next loop to work through each object in the collection. This is
based on theCountfunction of the object. Note that as you type the code in, the function
Itemand the propertyCountappear in list boxes, just as they do for built-in Access objects.
Using theCollectionobject, you can now use theItemfunction and the variablento
reference each object in the collection. The propertyPnameis used to display each name.
This is all wrapped into a message box statement so that each name will be displayed in turn.
Run the code in your module by clicking the cursor anywhere in the code and pressingF5
and you will get a message box with the value of the number of objects you entered into the
collection, followed by each name in turn. Next, try changing a value in MyTable, which is
the source for this collection, and then rerun the code.
The name in the collection will also change because when you create theMyPnames
object (pn) on the first line of the code, it reinitializes and takes the new values. If you made
theCollection’s objectStatic, it would not reinitialize but would keep the old values of the
collection:
Sub test_class()
Static pn As New MyPNames, n As Integer
MsgBox pn.Count
For n = 1 To pn.Count
MsgBox pn.Item(n).Pname
Next n
End Sub
Finally, password-protect your object model code. Right-click your project name in the
Project window of the VBE and click the project properties. Click the Protection tab of the
pop-up window and check the box for Lock Project Viewing. Provide a password (make sure
that you remember it!) and click OK.
Chapter 21: Class Modules 271