Excel VBA Advanced Training Courses

Excel VBA Advanced

Unit: Class Modules

What can be done with Class Modules?

Class modules allow you to create and use your own object types in your application. This implies the following;

• You can easily write code that works with any workbooks that do not have any code.
• Two or more procedures for the event of a command button can be consolidated in one
• The code is easy to use by concealing logic and data.

Why use Class Modules?

Classes make your code:

• Development simpler
• More manageable
• Self-documenting
• Easier to maintain

What is a Class?

A Class is a Blueprint or template of an Object.

In Excel VBA, an Object can mean Workbooks, Worksheets, User forms and Controls etc. Normally an Object has Properties or Methods. A Property stands for Data that describes the Object, and a Method stands for an action that can be ordered to the object.

Properties and Methods of the Object depend on the kind of Object.
For Example;

Worksheet (1).Select

... selects the first worksheet in the workbook. Select is a method of the worksheet object.

How Does a Class Module Work?

A Class Module is a place where a Class is defined. The procedures in a class module are never called directly from other modules like the procedures placed in the standard modules.

In the view of a standard module, the class module doesn't exist.

The thing that exists in the view of a standard module is an instance of the object generated by the class defined by the class module. The methods and procedures of the class are defined within the class module.

Key Elements in a class module

The class module defines all the properties and methods associated with the class. In the example below the "customer" class has two properties associated properties; Name and Main Address.

These are defined by the Property Get and Property let Procedures (see below).

The Customer ID is calculated by taking the leftmost 3 characters from the customer's Name and concatenating that with the 5 leftmost characters from the main Address. This is the result of the method GetCustomerID, and is defined in a function in the class module

Property Get and Let Procedures

A property is implemented using a property let and a property get procedure. When someone sets a value for a property the property let procedure is called with the new value. When someone reads the value of a property the property get procedure is called to return the value. The value is stored as an internal private variable.

Read only properties can be created by implementing a property get procedure without a corresponding property let procedure.

Example of a Class Module

Referring to user defined Objects in Code

This simply involves creating an instance of the Class in Code and then manipulating it is the way you would any other object.

The following code would be placed in a standard module, and refers to the customer object defined previously.

Line 1 defines an object variable as a Customer variable, and line 2 sets it as a new customer object. Line 3 assigns a value to its name property and line 4 a value to its Main Address property.

Line 4 uses the GetCustomerID Method to generate the CustomerID value and returns it in a message box.

Using IntelliSense™

Microsoft IntelliSense is a convenient way to access descriptions of objects and methods. It speeds up software development by reducing the amount of name memorization needed and keyboard input required. Once a class is defined in a class module, Intellisense will automatically provide drop down lists showing the methods and properties of objects the names of which have been entered into the VBE.

Working with Collections

A class is a blueprint for an object, and individual objects are "instances" of a class. A collection is simply a group of individual objects with which we are going to work.

For example in the code above we have defined a class called customers, and code to generate a single instance of that class; i.e. one individual customer. In practice we will be working with more than one customer and we will wish to define them as being part of a collection object so we can process them using some of the methods and properties of the collection object.

The Collection Object

The collection object has a number of properties and methods associated with it; of which the most important are:

Explicit creation of a collection

We can create a collection in a class module. This simply requires us to define the collections objects and methods in the normal way

The above code simply defines a collection called customers (class module name). The variable FCustomers is defined as a collection object. The various methods and properties are then defined. For example, the remove method is defined in a procedure that uses the remove method of the collection object to remove a specified item from the collection.

Referring to a collection in a standard module

Once defined, a collection can be employed in the same way as any other collection.

The above code simply defines a "customer" variable and a "customers" variable; assigns three objects to the collection and then writes the name and address to a worksheet in the current workbook, using a "FOR EACH" loop.

Using the Collections Object Directly

It is possible to create a collection using the VBA collection class directly. The code below creates a collection called employees and assigns three instances of the custom object employees to it.