Introducing Class Modules

---

Class modules are among the most important tools in a VBA programmer’s tool chest. A class module allows you to create your own objects, which you can manipulate just like objects already supplied with Microsoft Excel. And just like the objects available in Excel, a class module can have properties, methods, and events.

Accessing Objects

There’s one big difference between a simple variable and an object variable. The object variable is merely a pointer in memory. You must explicitly create the object and save its location in the object variable. This process is known as creating a new instance of an object or instantiating an object.

Because objects are different from variables, Visual Basic for Applications uses a special statement called the Set statement. The Set statement has two forms. Here’s the first form:

Set ObjectVariable = New ClassName 

In this form, the Set statement creates a new object based on ClassName. This means that Visual Basic will allocate memory for the object and save the memory location in the ObjectVariable class.

Set ObjectVariable = ObjectExpression 

In the second form, the Set statement does two things. The statement first releases the object that it was pointing to, and then it saves a pointer to an already existing object in ObjectVariable.

When Are Objects Really Created?

The New keyword in a Dim, a Public, or a Private statement doesn’t create a new instance of an object. Instead, Visual Basic adds code in front of every reference to the object to see if a new instance of the class has been created. If a new instance of the class hasn’t been created, the object will automatically be created before it’s used.

For the most part it really doesn’t matter if you use a Dim statement or a Set statement to create a new instance of the class. However, using the Set New statement is slightly more efficient than using a Dim New statement because Visual Basic doesn’t generate the extra code to verify that a new instance of the class has been created.

Using a Set New statement instead of a Dim New statement also prevents some debugging problems. Suppose you have a situation where you believe that you have created a new instance of a class, but for some reason the object wasn’t created. With the Dim New approach, the object will automatically be created and your program might try to use the object expecting it to hold certain information, but it won’t because the object was just created.

Creating the object with the Set New statement means that the object couldn’t be created on the fly, and your program would get some run-time error if it tried to access an object that hasn’t been created yet. Although the run-time error wouldn’t be pretty, it would let you know that there’s a problem somewhere in your code. Otherwise, you might not even realize there was a bug.

Declaring Objects

You can declare an object using a Dim, a Public, or a Private statement using two different forms. Here’s the first form:

Dim ObjectVariable As ClassName

This statement simply reserves space for ObjectVariable and the variable now has a type of ClassName.

Dim ObjectVariable As New ClassName

This second form does everything the previous form did, but will automatically create a new object the first time ObjectVariable is referenced.

Objects and Nothing

Visual Basic for Applications includes a special value called Nothing. You can use this value only with objects. Nothing is the value associated with an object variable that doesn’t currently point to an instance of a class. An object variable declared with a Dim statement will initially be set to Nothing.

You can determine if a new instance of a class has been created by using Is Nothing in an If statement like this:

If ObjectVariable Is Nothing Then

Warning 

Although the expression ObjectVariable Is Not Nothing might make perfect sense in English, Visual Basic for Applications doesn’t understand it. If you need to verify that an object variable refers to an instance of an object, you should use the expression Not ObjectVariable Is Nothing.

The Is Nothing test results in a Boolean value and can be used anywhere you can use a Boolean expression.

You can use the following statement to destroy an object:

Set ObjectVariable = Nothing

This statement will release the reference to the object and set the object variable to its uninitialized state. Assuming that there was only one object variable that pointed to the object, this statement will also destroy the object and release all the resources associated with it.

However, if multiple object variables point to this object, all of them must be set to Nothing before the object is destroyed. For example, in the following code fragment, the object created from MyClass continues to exist, even though ObjectVariable1 was set to Nothing.

Set ObjectVariable1 = New MyClass
Set ObjectVariable2 = ObjectVariable1
Set ObjectVariable1 = Nothing

Objects with Multiple Object Variables

It’s important to keep in mind that an object is not the same thing as an object variable. For example, the following code creates an object, which has two variables pointing to it:

Set ObjectA = New MyClass
Set ObjectB = ObjectA

The first Set statement creates a new instance of MyClass, whereas the second Set statement merely creates a second pointer to the same object created by the first statement.

This means that the following statements will do the same thing because both ObjectA and ObjectB point to the same object:

ObjectA.Name = "Roses"
ObjectB.Name = "Roses" 

If this isn’t confusing enough, executing the following statement will not destroy the object.

Set ObjectA = Nothing

Because ObjectB still points to the object, it will remain in memory until ObjectB is also set to Nothing.

---