Listing Referring to Ranges Using the Application Object

Sub ReferringToRangesI() Dim rg As Range

' ActiveCell is a range representing the ' active cell. There can be one and ' only one active cell.

Debug.Print Application.ActiveCell.Address

' Selection refers to a range representing ' all of the selected cells. There can be ' one or more cells in the range. Debug.Print Application.Selection.Address

' Application.Range works on the active ' worksheet

ThisWorkbook.Worksheets(1).Activate Set rg = App1ication.Range("D5") Debug.Print "Worksheet 1 is active" Debug.Print rg.Address Debug.Print rg.Parent.Name

ThisWorkbook.Worksheets(2).Activate Set rg = App1ication.Range("D5") Debug.Print "Worksheet 2 is active" Debug.Print rg.Address Debug.Print rg.Parent.Name

Set rg = Nothing

End Sub

Listing 8.1 uses the Address property of the Range object to print out the address associated with the range. Keep in mind that there is a significant difference between ActiveCell and Selection. If only one cell is selected, they refer to the same thing. However, if you have a group of cells selected, only one of the cells is the ActiveCell whereas all of the cells are the Selection. You'll find this clearly demonstrated in this example if you run it once with a group of cells selected.

This example also illustrates how Application.Range works. When you use the Range property of the Application object, the Range object is associated with the worksheet that is active when the statement executes. Also, notice how you can specify a range address by supplying the A1 style address of the range, such as the one you'd enter in a worksheet formula. You can enter any valid range using this method. For example, all of the following statements refer to valid ranges.

Application.Range("A1:C5") Application.Range("A:A") Application.Range("3:3") Application.Range("A1:C5, D6:F10")

Cells and Ranges with the Worksheet Object

Most of the time you'll probably use one of the Worksheet object's properties, specifically either the Cells property or the Range property. Let's start with the Cells property because it's probably the easiest to understand. In theory, the Cells property returns a range that represents all of the cells on a worksheet. This is because it's a collection object. Like most collection objects, it has an Item property that allows you to select a specific item in the collection. Also, like most collection objects, the Item property is the default property, so you don't necessarily need to specify it.

Although you'll occasionally need to refer to all of the cells on a worksheet, more often you'll be interested in a specific cell. This is where the Cells property fits in. Unlike most collection objects, the Cells collection object orders its items in a very convenient two-dimensional array. This allows you to easily specify an individual cell by row and column number. Listing 8.2 shows you an example of this.

0 0

Post a comment