Object Variables

To access a property of an object, or to invoke a method, we can generally take two approaches: direct or indirect. The indirect approach uses an object variable—that is, a variable that has an object data type—whereas the direct approach does not.

For instance, to set the Bold property of the Font object for the first row in the active worksheet, we can take a direct approach, as in: 

ActiveSheet.Rows(1).Font.Bold = True

Alternatively, we can assign an object variable. Here are two possibilities: 

Dim rng As Range
Set rng = ActiveSheet.Rows(1)
rng.Font.Bold = True

Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
fnt.Bold = True

Object variables are more important than they might seem at first. The most obvious reason for their use is that they can improve code readability when we need to refer to the same object more than once. For instance, instead of writing: 

ActiveSheet.Rows(1).Font.Bold = True
ActiveSheet.Rows(1).Font.Italic = True
ActiveSheet.Rows(1).Font.Underline = False
ActiveSheet.Rows(1).Font.Size = 12
ActiveSheet.Rows(1).Font.Name = "Arial"

we can use a Font variable to improve readability as follows: 

Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
fnt.Bold = True
fnt.Italic = True
fnt.Underline = False
fnt.Size = 12
fnt.Name = "Arial"

The With Statement

In fact, VBA provides a With statement to handle just the situation in the previous example, which could be written as follows: 

Dim fnt As Font
Set fnt = ActiveSheet.Rows(1).Font
With fnt
   .Bold = True
   .Italic = True
   .Underline = False
   .Size = 12
   .Name = "Arial"
End With

The general syntax of the With statement is:

With object
   ' statements go here

End With

where the statements generally refer to the object, but do not require qualification using the object's name, as in the previous example.

Object Variables Save Execution Time

The main reason that objec t variables are important is not to improve readability, but to save execution time. In particular, to execute each of the five lines in the first version of the previous code, VBA needs to resolve the references to the various Excel objects ActiveSheet, Rows(1), and Font. That is, VBA needs to "climb down" the Excel object model. This takes time.

However, in the code that uses an object variable of type Font, VBA only needs to resolve these references once. Therefore, the second version runs much more quickly. This difference can be very noticeable when there are hundreds or thousands of references to resolve.

An Object Variable Is a Pointer

There are some very important differences between object variables and nonobject variables, such as those of type Integer, Single, or String. As we have mentioned, a nonobject variable can be thought of as a name for a location in the computer's memory that holds some data. For instance, in the code: 

Dim iVar As Integer
iVar = 123

the variable iVar is a 4-byte memory location that holds the integer value 123. This can be pictured as in Figure 9-2. (Actually, the 4-byte memory location holds the value 123 in binary format, but that is not relevant to our discussion.)

Integer variables in memory

Figure 9-2. Integer variables in memory

Further, if we were to write:

Dim iVar2 As Integer
iVar2 = iVar
iVar2 = 567

we would not expect the last line of code to have any effect upon the value of the variable iVar, which should still be 123. This is because iVar and iVar2 represent different areas of memory, as pictured in Figure 9-2.

However, an object variable is not the name of a memory location that holds the object. Rather, an object variable is the name of a memory location that holds the address of the memory location that holds the object, as shown in Figure 9-3. Put another way, the object variable holds a reference to or points to the object. For this reason, it is an example of a pointer variable, or simply a pointer. In Figure 9-3, the object variable rng points to an object of type Range, namely, the first column in the active sheet.

An object variable in memory

Figure 9-3. An object variable in memory

The code that goes with Figure 9-3 is:

Dim rng as Range
Set rng = ActiveSheet.Columns(1)

One of the consequences of the fact that object variables are pointers is that more than one object variable can point to (or refer to) the same object, as in: 

Dim rng as Range
Dim rng2 as Range
Set rng = ActiveSheet.Columns(1)
Set rng2 = rng

This code creates the situation pictured in Figure 9-4.

Two object variables referencing the same object

Figure 9-4. Two object variables referencing the same object

We emphasize that while rng and rng2 are different object variables, they hold the same value and so, point to the same object. Thus, we can change the first column using either of these object variables.

It is important when programming with objects to keep very careful track of all object variables and what they are referencing. Furthermore, it is generally not a good idea to have more than one object variable pointing to the same object (as in Figure 9-4) unless there is a compelling reason to do so. It is very easy to change the object using one object variable (say rng) and then later use the other variable (rng2), thinking it refers to the unchanged object.

Freeing an Object Variable: the Nothing Keyword

To free an object variable so that it no longer points to anything, we use the Nothing keyword, as in: 

Set rng2 = Nothing

It is good programming practice to free object variables when they are no longer needed, since this can save resources. An object variable is also set to Nothing automatically when its lifetime expires.

Note that once an object no longer has any references to it, the object will automatically be destroyed by VBA, thus freeing up its resources (memory). However, all references to the object must be freed before the object is destroyed. This is another reason not to point more than one object variable at the same object if possible.

The Is Operator

To compare the values of two ordinary variables, Var1 and Var2, we would just write: 

If Var1 = Var2 Then . . .

However, the syntax for comparing two object variables to see if they refer to the same object is special (as is the syntax for setting the value of an object variable—using the Set statement). It is done using the Is operator: 

If rng Is rng2 then . . .

Similarly, to test whether or not an object variable has been set to Nothing, we write: 

If rng Is Nothing Then . . .

Be advised that there is a problem with the Is operator in the current version of VBA. This problem exists in the version of VBA used by Office 97 and Office 2000. (Microsoft has acknowledged the problem.) For example, the code: 

Dim Wks As Worksheet
Dim Wks2 As Worksheet

Set Wks = ActiveSheet
Set Wks2 = ActiveSheet

MsgBox Wks Is Wks2

will correctly display the value True. However, the analogous code: 

Dim rng As Range
Dim rng2 As Range

Set rng = ActiveSheet.Rows(1)
Set rng2 = ActiveSheet.Rows(1)

MsgBox rng Is rng2

incorrectly displays the value False. If we change the penultimate line to: 

Set rng2 = rng

then the message box correctly displays True.

Default Members

In most object models, many objects have a default member (property or method) that is invoked when a property or method is expected but we do not specify one. For instance, in the Microsoft Word object model, the default member for the Range object is the Text property. Hence, the VBA Word code: 

Dim rng As Range
Set rng = ActiveDocument.Words(1)
rng = "Donna"

sets the first word in the active document to Donna, since Word applies the default property in the last line, effectively replacing it with: 

rng.Text = "Donna"

Unfortunately, neither the Excel VBA documentation nor the Excel object model make an effort to identify the default members of Excel objects. Accordingly, my suggestion is to avoid the issue when programming Excel.

In any case, default members tend to make code less readable, and for this reason, I generally avoid them. One notable exception is for a collection object. It is generally the case that the default member of a collection object is the Item method. Hence, for instance, we can refer to the fourth cell in the current selection by: 

Selection.Cells(4)

rather than by the more clumsy:

Selection.Cells.Item(4)

Since this use of the default member is not likely to cause any confusion, we will use it.

Global Members

Many of the properties and methods of the Application object can be used without qualifying them with the word Application. These are called global members . For instance, the Selection property is global, and so we can write: 

Selection.Cells.Count

instead of:

Application.Selection.Cells.Count

To identify the global members, the Excel object model has a special object called the Global object. This object is not used directly—its purpose is simply to identify the global members of the object model. Note that the members of the Global object form a proper subset of the members of the Application object (which means that not all of the members of the Application object are global).

Table 9-2 lists the (nonhidden) global members of the Excel object model

Table 9-2. Excel global members

_Evaluate

CommandBars

Parent

_Run2

Creator

Range

ActiveCell

DDEAppReturnCode

Rows

ActiveChart

DDEExecute

Run

ActiveDialog

DDEInitiate

Selection

ActiveMenuBar

DDEPoke

SendKeys

ActivePrinter

DDERequest

Sheets

ActiveSheet

DDETerminate

ShortcutMenus

ActiveWindow

DialogSheets

ThisWorkbook

ActiveWorkbook

Evaluate

Toolbars

AddIns

Excel4IntlMacroSheets

Union

Application

Excel4MacroSheets

Windows

Assistant

ExecuteExcel4Macro

Workbooks

Calculate

Intersect

WorksheetFunction

Cells

MenuBars

Worksheets

Charts

Modules

Columns

Names