Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 175

An object variable is one 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 Private 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. Here's an example:

Set InputArea = Range("C16:E16")

To see how object variables simplify your code, examine the following procedure, which doesn't 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 Worksheets("Sheet1").Range("A1").Font.Size = 14 Worksheets("Sheet1").Range("A1").Font.Name = "Cambria" End Sub

This routine enters a value into cell A1 of Sheet1 on the active workbook, applies some formatting, and changes the fonts and size. 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 MyCell.Font.Size = 14 MyCell.Font.Name = "Cambria" 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.