Creating a Collection

The next stage is to create the Collection object to hold the individual PName objects. Insert another class module using Insert Class Module from the VBE menu. bar. Change the name to PNames and double-click the class module to enter it.

The first thing to do is to define a private variable to act as the Collection object in the same way you defined a private variable to act as the property for the PName object. This needs to be placed in the declarations section of the class module:

 Private mPnames As Collection 

This must have a unique name, so an m is placed in front of the collection name. The letter m distinguishes the variable as a member variable.

Next, select the Class_Initialize subroutine. This routine runs when you first use the class and determines what the contents will be. To run it, click the Class section in the top-left drop-down and click Initialize in the top-right drop-down. Enter the following code:

 Private Sub Class_Initialize() 
 
 Dim objPname As Pname 
 
 Set mPnames = New Collection 
 For n = 1 To 3 
 
 Set objPname = New Pname 
 
 objPname.Pname = Worksheets("sheet1").Range("a" & n + 1).Value 
 
 mPnames.Add objPname 
 Next n 
 End Sub 

First, you define an object called objPname based on the object you created called PName . Notice that the objects PName and PNames now appear in the drop-down list as you type the code in. You then set the local variable, mPNames , as a new Collection object. At this point, it is empty.

The For..Next loop adds the objects into the collection from a source of data. This could be an external database, or it could just be a local array holding the information. In this example, a part of the spreadsheet is being used, although at this point there is no data in it.

The variable objPname , which was defined as our PName object, is set to a new Pname for each loop. This means create a new instance of PName that can hold data and be added to the PNames collection. The data is then taken from Sheet1, column A, and placed in the Pname property that was created.

The object objPname is then added to the Pnames collection. At the end of this For..Next loop, three objects have been added to the PNames collection, based on data in Sheet1. You also need to add a public function called Item so that you can refer to individual objects within the collection by their index number. Enter this into the same class module as the rest of the code:

 Public Function Item(Index As Integer) As Pname 
 
 Set Item = mPnames.Item(Index) 
 
 End Function 

This defines Item as a function of the object Pnames and sets Item to point to the mPnames collection based on the index number given. Because mPnames was defined as a Collection object, it already has the Item function built into it. This function provides a ‚“mirror ‚½ of what is going on in the mPnames collection in the background.

You will need to add a property called Count so that your code can establish how many PName objects there are in the PName collection:

 Public Property Get Count() As Long 
 Count = mPnames.Count 
 End Property 

In this case, you only need a property Get because this is a read-only property, and it gets updated by the Class_Initialize routine adding in the source data. This acts again as ‚“a mirror ‚½ against the mPnames object, which is defined as a collection and already has the Count property built into it.

You have now set up a collection called PNames of objects called PName , which simply refers to a block of spreadsheet data on Sheet1 in Column A, as shown in Figure 18-2. The collection has a Count property and an Item method.

Figure 18-2: Data used for the Pname collection