More about Class Modules

The example in the preceding section demonstrates how to create a new object class with a single property named Value and a single method named Toggle . An object class can contain any number of properties, methods , and events.

Naming the object class

The name that you use for the class module in which you define the object class is also the name of the object class. By default, class modules are named Class1 , Class2 , and so on. Usually, you'll want to provide a more meaningful name for your object class.

Programming properties of objects

Most objects have at least one property, and you can give them as many as you need. After a property is defined and the object is created, you can use it in your code using the standard "dot" syntax:

 object.property 

The VBE Auto List Members option works with objects defined in a class module. This makes it easier to select properties or methods when writing code.

Properties for the object that you define can be read-only, write-only, or read/write. You define a read-only property with a single procedure - using the Property Get keyword. Here's an example of a Property Get procedure:

 Property Get FileNameOnly() As String     FileNameOnly = ""     For i = Len(FullName) To 1 Step -1         Char = Mid(FullName, i, 1)         If Char = "\" Then             Exit Function         Else             FileNameOnly = Char & FileNameOnly         End If     Next i End Property 

You might have noticed that a Property Get procedure works like a Function procedure. The code performs calculations and then returns a property value that corresponds to the procedure's name. In this example, the procedure's name is FileNameOnly . The property value returned is the filename part of a path string (contained in a Public variable named FullName ). For example, if FullName is c:\data\myfile.txt , the procedure returns a property value of myfile.txt . The FileNameOnly procedure is called when VBA code references the object and property.

For read/write properties, you create two procedures: a Property Get procedure (which reads a property value) and a Property Let procedure (which writes a property value). The value being assigned to the property is treated as the final argument (or the only argument) of a Property Get procedure.

Two example procedures follow:

 Dim XLFile As Boolean Property Get SaveAsExcelFile() As Boolean     SaveAsExcelFile = XLFile End Property Property Let SaveAsExcelFile(boolVal As Boolean)     XLFile = boolVal End Property 

Note  

Use Property Set in place of Property Let when the property is an object data type.

A Public variable in a class module can also be used as a property of the object. In the preceding example, the Property Get and Property Let procedures could be eliminated and replaced with this module-level declaration:

 Public SaveAsExcelFile As Boolean 

In the unlikely event that you need to create a write-only property, you create a single Property Let procedure with no corresponding Property Get procedure.

The preceding examples use a Boolean module-level variable named XLFile . The Property Get procedure simply returns the value of this variable as the property value. If the object were named FileSys , for example, the following statement would display the current value of the SaveAsExcelFile property:

 MsgBox FileSys.SaveAsExcelFile 

The Property Let statement, on the other hand, accepts an argument and uses the argument to change the value of a property. For example, you could write a statement such as the following to set the SaveAsExcelFile property to True :

 FileSys.SaveAsExcelFile = True 

In this case, the value True is passed to the Property Let statement, thus changing the property's value.

The preceding examples use a module-level variable named XLFile that actually stores the property value. You'll need to create a variable that represents the value for each property that you define within your class module.

Note  

Normal procedure-naming rules apply to property procedures, and you'll find that VBA won't let you use some names if they are reserved words. So, if you get a syntax error when creating a property procedure, try changing the name of the procedure.

Programming methods for objects

A method for an object class is programmed by using a standard Sub or Function procedure placed in the class module. An object might or might not use methods. Your code executes a method by using standard notation:

 object.method 

Like any other VBA method, a method that you write for an object class will perform some type of action. The following procedure is an example of a method that saves a workbook in one of two file formats, depending on the value of the XLFile variable. As you can see, there is nothing special about this procedure.

 Sub SaveFile()     If XLFile Then         ActiveWorkbook.SaveAs FileName:=FName, _           FileFormat:=xlWorkbookNormal     Else         ActiveWorkbook.SaveAs FileName:=FName, _           FileFormat:=xlCSV     End If End Sub 

The CSVFileClass example in the next section should clarify the concepts of properties and methods for object classes defined in a class module.

Class module events

Every class module has two events: Initialize and Terminate . The Initialize event is triggered when a new instance of the object is created; the Terminate event is triggered when the object is destroyed . You might want to use the Initialize event to set default property values.

The frameworks for these event handler procedures are as follows :

 Private Sub Class_Initialize() '    Initialization code goes here End Sub Private Sub Class_Terminate() '    Termination code goes here End Sub 

An object is destroyed (and the memory it uses is freed) when the procedure or module in which it is declared finishes executing. You can destroy an object at any time by setting it to Nothing . The following statement, for example, destroys the object named MyObject :

 Set MyObject = Nothing