Creating an Object

You need to set up the actual object for items within the collection first because this has to be used in the construction of the Collections object. The PName object will have one property, the name of the object called Pname . Remember what a property is: it holds a value for something within that object, in this case, the actual name.

You first need to create a variable to hold the property value, which you do in the declarations section of the module:

 Private mPname As String 

This sets up a string variable called mPname . Notice that it is private and cannot be seen as part of the object. This is because it is a working variable within the class module and is not there for use by the user. Also, the name must be different from the actual property name the user can see, which is why the m is added at the front.

Next, you need to set up code to control access to the property. You need a Property Let and a Property Get statement, and the statements must be public ‚ otherwise , you will not be able to view them within the object. These statements effectively read and write values to the property that you are setting up:

 Public Property Let Pname(vdata As String) 
 mPname = vdata 
 End Property 

The parameter vdata represents the value being passed to the property within the Let statement.

 Public Property Get Pname() As String 
 Pname = mPname 
 End Property 

It is very important that the Let and Get property statements have the same name; otherwise, you will be able to read a property ( Get ) but not write back to it ( Let ), or you will be able to write to it ( Let ) but not read it back ( Get ).

The Let property uses a string variable to transfer the data into your mPname variable. The Get property sets the property value to whatever is held in your mPname variable.

You can use Insert Procedure from the code menu to create a code skeleton for a property, which opens the Add Procedure dialog shown in Figure 18-1. This automatically writes the Let and Get property statements and ensures that they both have the same name.

Figure 18-1: Using the Add Procedure dialog to enter a property

You have now set up an object called Pname . This object sits in a collection called PNames , just as the Worksheet object sits in a collection called Worksheets .