Page 327 - Microsoft Office Excel 2003 Programming Inside Out

Developing Class Modules

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 state­
ment called the Set statement. The Set statement has two forms. Here’s the first form:
Chapter 14
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.

301
Part 4: Advanced VBA