Referring to objects

When you refer to an object using VBA, you often must qualify the object by connecting object names with a period (also known as a dot operator). What if you had two workbooks open and they both had a worksheet named Sheetl? The solution is to qualify the reference by adding the object's container, like this:

Workbooks("Bookl").Worksheets("Sheetl")

Without the workbook qualifier, VBA would look for Sheetl in the active workbook.

To refer to a specific range (such as cell A1) on a worksheet named Sheetl in a workbook named Bookl, you can use the following expression:

Workbooks("Bookl").Worksheets("Sheetl").Range("Al")

The fully qualified reference for the preceding example also includes the Application object, as follows:

Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1")

Most of the time, however, you can omit the Application object in your references; it is assumed. If the Bookl object is the active workbook, you can even omit that object reference and use this:

Worksheets("Sheet1").Range("A1")

And —I think you know where I'm going with this —if Sheetl is the active worksheet, you can use an even simpler expression:

Range("A1")

Contrary to what you might expect, Excel does not have an object that refers to an individual cell that is called Cell .A single cell is simply a Range object that happens to consist of just one element.

Simply referring to objects (as in these examples) doesn't do anything. To perform anything meaningful, you must read or modify an object's properties or else specify a method to be used with an object.

0 0

Post a comment