Part VI: Programming Excel with VBA
814
You can take it to still another level and refer to a specific cell as follows:
Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”). _
Range(“A1”)
l If you omit specific references, Excel uses the active objects. If Book1.xlsx is the
active workbook, the preceding reference can be simplified as follows:
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the reference even more:
Range(“A1”)l (^) Objects have properties. A property can be thought of as a setting for an object. For
example, a Range object has properties, such as Value and Address. A Chart object
has properties such as HasTitle and Type. You can use VBA both to determine object
properties and to change them.
l (^) You refer to properties by combining the object with the property, separated by a
period. For example, you can refer to the value in cell A1 on Sheet1 as follows:
Worksheets(“Sheet1”).Range(“A1”).Value
l You can assign values to variables. 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
l (^) Objects have methods. A method is an action that is performed with the object. For
example, one of the methods for a Range object is ClearContents. This method clears
the contents of the range.
l You specify methods by combining the object with the method, separated by a
period. For example, to clear the contents of range A1:C12, use the following statement:
Worksheets(“Sheet1”).Range(“A1:C12”).ClearContents
l (^) VBA also includes all the constructs of modern programming languages, including
variables, arrays, looping, and so on.
The preceding describes VBA in a nutshell. Now you just have to learn the details, some of which
are covered in the rest of this chapter.
Objects and collections
VBA is an object-oriented language, which means that it manipulates objects, such as Ranges,
Charts, Shapes, and so on. These objects are arranged in a hierarchy. The Application
object (which is Excel) contains other objects. For example, the Application object contains a
number of objects, including the following:
l (^) AddIns (a collection of AddIn objects)
l Windows (a collection of Window objects)
l (^) Workbooks (a collection of Workbook objects)