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 Sheet1? The solution is to qualify the reference by adding the object's container, like this:

Workbooks("Book1").Worksheets("Sheet1")

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

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

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

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 Book1 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 Sheet1 is the active worksheet, you can use an even simpler expression:

Range("A1")

Note 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.

4 PREV

NEXT

0 0

Post a comment