Using Object Variables

Category: General VBA | [Item URL]

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:

  1. Your code will be easier to understand and write
  2. Your code might run slightly 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 a bit 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.

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


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy