Object Variables


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.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net