Excel Developer Tip

Using Object Variables

Writing VBA procedures for Excel essentially involves manipulating objects. Excel itself is an object (called Application) and it contains more than 100 additional objects. Here's an example of using an object (in this case, a range object). This statement assigns the value in a cell to a variable named Dependents.

Dependents = Workbooks("Taxbook"). _
  Worksheets("Sheet1").Range("A5").Value

In this example, Dependents is a "normal" variable (not an object variable). After this assignment is made, your code can use the Dependents variable anyway it chooses. However, if your code changes the value of the Dependents variable, the change will not be made to cell A5. An object variable is a variable that serves as a substitute for the actual object. To create an object variable, use the Set keyword. The statements below create an object variable for the Range object:

Dim Dependents as Range
Set Dependents = Workbooks("Taxbook").Worksheets("Sheet1") _
   .Range("A5")

After this assignment is made, you can use the Dependents variable as if it were the actual object. This includes modifying its properties and using its methods. For example, you can change the value stored in cell A5 with the following statement:

Dependents.Value = 3

Advantages of Using Object Variables

So what is the advantage of using object variables? There are two:

Your code will be easier to understand and write
Your code will run faster

After all, typing:

  Dependents.Value

is much faster than typing:

  Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value

And, you'll probably agree that the code is much more meaningful. The speed advantage of using object variables may not be apparent, but if you perform timed tests you will find that working with object variables is almost always faster than working with the objects themselves. One rule of thumb is to eliminate the number of "dots" processed. The first statement below contains three dot operators. The second statement contains only one dot (and will execute faster).

Workbooks("Taxbook").Worksheets("Sheet1").Range("A5").Value = 3
Dependents.Value = 3

Benchmarking an Object Variable

The two examples below demonstrate that using an object variable is much faster than referring to the object directly. Macro1 (which doesn't use an object variable) loops 5000 times and assigns a value to a range object. Macro2 performs the same operation, but first creates an object variable for the range object.

Both routines display the total time in a message box. On my system, Macro1 took 25.09 seconds. Macro2 required only 7.36 seconds - nearly 3.5 times faster.

Sub Macro1()
'   no object variable
    Dim i As Integer
    StartTime = Timer
    For i = 1 To 5000
        ThisWorkbook.Sheets("Sheet1").Range("A1:B12").Value = i
    Next i
    MsgBox Timer - StartTime
End Sub


Sub Macro2()
'   with object variable
    Dim i As Integer
    Dim MyCell As Range
    StartTime = Timer
    Set MyCell = ThisWorkbook.Sheets("Sheet1").Range("A1:B12")
    For i = 1 To 5000
        MyCell.Value = i
    Next i
    MsgBox Timer - StartTime
End Sub