Datasheet

 You refer to an object by specifying its position in the object hierarchy,
using a dot (that is, a period) as a separator. For example, you can refer
to the workbook Book1.xlsx as
Application.Workbooks(“Book1.xlsx”)
This refers to the workbook Book1.xlsx in the Workbooks collection.
The Workbooks collection is contained in the Application object (that
is, Excel). Extending this to another level, you can refer to Sheet1 in
Book1.xlsx as
Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1
”)
As shown in the following example, you can take this to still another
level and refer to a specific cell (in this case, cell A1):
Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1
”).Range(“A1”)
 If you omit specific references, Excel uses the active objects. If
Book1.xlsx is the active workbook, you can simplify the preceding
reference as follows:
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the
reference even more:
Range(“A1”)
 Objects have properties. You can think of a property as a setting for
an object. For example, a Range object has such properties as Value
and Address. A Chart object has such properties as HasTitle and Type.
You can use VBA to determine object properties and also to change
properties.
 You refer to a property of an object by combining the object name
with the property name, separated by a dot. For example, you can
refer to the Value property in cell A1 on Sheet1 as follows:
Worksheets(“Sheet1”).Range(“A1”).Value
 You can assign values to variables. A variable is a named element that
stores information. You can use variables in your VBA code to store
such things as values, text, or property settings. To assign the value
in cell A1 on Sheet1 to a variable called Interest, use the following VBA
statement:
Interest = Worksheets(“Sheet1”).Range(“A1”).Value
17
Chapter 1: What Is VBA?
05_046746 ch01.qxp 1/12/07 6:16 PM Page 17