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:

Public InputArea As Range

To see how object variables simplify your code, examine the following procedure, which was written without using object variables:

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 boldfaces and italicizes the cell's contents. That's a lot of typing. To reduce wear and tear on your fingers, 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.

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 will become apparent when I discuss looping later in this chapter.

0 0

Post a comment