Object Variables

An object variable is a variable that represents an entire object, such as a range or a worksheet. Object variables are important for two reasons:

■ They can simplify your code significantly.

■ They can make your code execute more quickly.

Object variables, like normal variables, are declared with the Dim or Public statement. For example, the following statement declares inputArea as a Range object variable:

Dim InputArea As Range

Use the Set keyword to assign an object to the variable. For example:

Set InputArea = Range("C16:E16" )

To see how object variables simplify your code, examine the following procedure, which does not use an object variable:

Sub NoObjVar()

Worksheets("Sheet1").Range("A1").Value = 124 Worksheets("Sheet1") .Range ("A1") .Font.Bold = True Worksheets("Sheet1").Range("A1").Font.Italic = True End Sub

This routine enters a value into cell A1 of Sheet1 on the active workbook and then bold-faces and italicizes the cell's contents. That's a lot of typing. To reduce wear and tear on your fingers (and make your code more efficient), you can condense the routine with an object variable:

Sub ObjVar()

Dim MyCell As Range

Set MyCell = Worksheets("Sheet1").Range("A1") MyCell.Value = 124 MyCell.Font.Bold = True MyCell.Font.Italic = True End Sub

After the variable MyCell is declared as a Range object, the Set statement assigns an object to it.

Subsequent statements can then use the simpler MyCell reference in place of the lengthy

Worksheets("Sheet1").Range("A1") reference.

Tip After an object is assigned to a variable, VBA can access it more quickly than it can a normal, lengthy reference that has to be resolved. So when speed is critical, use object variables. One way to think about this is in terms of dot processing. Every time VBA encounters a dot, as in Sheets(1).Range("A1"), it takes time to resolve the reference. Using an object variable reduces the number of dots to be processed. The fewer the dots, the faster the processing time. Another way to improve the speed of your code is by using the With-End With construct, which also reduces the number of dots to be processed. I discuss this construct later in this chapter.

The true value of object variables becomes apparent when I discuss looping later in this chapter.



Was this article helpful?

0 0

Post a comment