Using the PNames Collection

You can now use your Collection object PNames within standard code just as you would any other object. You use the code within a standard Excel VBA module:

 Sub test_class() 
 Dim pn As New Pnames, n As Integer 
 MsgBox pn.Count 
 For n = 1 To pn.Count 
 
 MsgBox pn.Item(n).Pname 
 Next n 
 End Sub 

This creates a new instance of the Pnames object. When this procedure is run, the first thing it does is to initialize the class module, which means it picks up the data from the spreadsheet and adds the objects to the collection.

The variable n , used in the For..Next loop, is defined as an integer because it was defined in the Item function in the collection as an integer (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 the Count function of the object. Note that as you type the code in, the function Item and the property Count will appear in list boxes, just as they do for built-in Excel objects.

Using the Collection object, you can now use the Item function and the variable n to reference each object in the collection. The property Pname is 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 and you will get a message box with the value 3 (3 objects in the collection) followed by each name in turn. Next, try changing a cell value on the spreadsheet and rerun the code. The name in the collection will also change because when you create the Pnames object ( pn ) on the first line of the code, it reinitializes and takes the new values. If you made the Collection 's object Static , it would not reinitialize and would keep the old values of the collection:

 Sub test_class() 
 Static pn As New Pnames, n As Integer 
 MsgBox pn.Count 
 For n = 1 To pn.Count 
 
 MsgBox pn.Item(n).Pname 
 Next n 
 End Sub