93.

Referring to a Range

A macro that needs to work with ranges of differing sizes must be flexible. In this section, you'll learn various ways to refer to a range. The examples in this section don't do anything except reference ranges within a list, but these are all techniques you'll use many times as you work with ranges. Later in the chapter, you'll use these expressions in practical contexts.

Refer to a Range by Using an Address

The Range property is a useful and flexible way of retrieving a reference to a range. The Range property allows you to specify the address of the range you want. You can use the Object Browser to see how to use the Range property.

Visual Basic Editor

  1. In the Chapter05 workbook, activate the Data tab. Then click the Visual Basic Editor toolbar button (on the Visual Basic toolbar). Rearrange the Excel and Visual Basic Editor windows so that you can see them side by side.

    Object Browser

  2. In the Visual Basic Editor, click the Object Browser toolbar button.

    The Object Browser appears in the space normally held by the code window. In essence, the Object Browser consists of two lists. The list on the left is a list of object class names. The list on the right is a list of members-methods and properties-available for the currently selected object class. At the top of the list of classes is a special object class named <globals>. The globals class is not a real object class, but it includes in its list of members all the methods and properties you can use without specifying an object. These are the methods and properties you use to start a statement.

    click to expand

  3. In the list of classes, select the <globals> class if it is not already selected. Then click in the list of members and press the letter R to scroll to the first member that begins with the letter R. Then select the Range property. The box at the bottom of the Object Browser displays information about the Range property. This property takes two arguments-the second of which is optional-and it returns a reference to a Range object.

    click to expand

  4. Right-click the Range property name in the Members list, and click Copy on the shortcut menu. Then choose the View menu and click Immediate Window. In the Immediate window, choose the Edit menu and click Paste.

  5. After the Range property, type an opening parenthesis-Visual Basic will display the argument list-and type "B2", followed by a closing parenthesis and a period. Then type Select. The complete statement is Range("B2").Select. You need the quotation marks around the range definition because this is the name of the range, not the item number of a member of a collection.

  6. Press Enter to select cell B2 on the active worksheet.

  7. Type Range("B2:H2").Select and press Enter. The first argument of the range property can be a multicell range. In fact, it can be anything that Excel recognizes.

    click to expand

  8. Type Range("H14").Select and press Enter to select the lower right corner of the list of values. Then type Range(Selection, "B2").Select and press Enter.

    This selects the range from cell H14 (the current selection) to cell B2 (the upper left cell of the list). The arguments to the Range property do not have to be strings; they can also be references to range objects. A common use of the two-argument form of the Range property is to select the range that extends from the currently selected range to some fixed point at the top of the worksheet.

The Range property is a flexible way of establishing a link to an arbitrary Range object. You can use either a single text string that contains any valid reference as an argument to the Range property or two arguments that define the end points of a rectangular range.

Refer to a Range as a Collection of Cells

Multiple worksheets can exist in a workbook, and the Worksheets collection is defined as an object class. A Worksheets object has a list of methods and properties that is separate from a Worksheet object.

Similarly, multiple cells exist on a worksheet. You might expect that Excel would have a Cells collection object. But a collection of cells is more complicated than a collection of worksheets because cells come in two dimensions-rows and columns. For example, you can think of the range A1:B3 as a collection of six cells, as a collection of three rows, or as a collection of two columns.

Excel therefore has three properties that look at a range as a collection. The first of these-the Cells property-returns a collection of cells. This is not a separate class, however. The result of the Cells property is still a Range object, and it can use any of the methods or properties of any other Range object.

  1. In the Object Browser, with the <globals> object selected in the list of classes, select the Cells property from the list of members. The description at the bottom of the Object Browser indicates that the Cells property returns a Range object.

    click to expand

  2. In the Immediate window, type Cells.Select and press Enter.

    This selects all the cells on the worksheet. This is equivalent to clicking the box at the upper left corner of the worksheet, between the column A heading and the row 1 heading.

    click to expand

  3. Type Cells.Item(5).Select and press Enter.

    This selects cell E1, the fifth cell in the first row. The Cells property returns the range of all the cells on the worksheet as a collection. An individual item in the collection is a cell.

    click to expand

  4. Type Cells.Item(257).Select and press Enter.

    This selects cell A2, the first cell in the second row. When you use a single number to select an item in the Cells collection, the number wraps at the end of each row. Each row of the worksheet contains 256 cells, so cell 257 is the first cell on the second row.

    click to expand

  5. Type Cells.Item(3,2).Select and press Enter.

    This selects cell B3, the third row and second column in the worksheet. Unlike most other collections, the Cells collection allows you to specify an item by using both the row and column values.

    click to expand

  6. Type ?Cells.Count and press Enter.

    The number 16777216 appears in the Immediate window. There are 16,777,216 cells in a worksheet.

    Tip 

    Typing a question mark before an expression in the Immediate window allows you to display the value of that expression.

  7. Type Cells.Item(16777216).Select and press Enter.

    This selects cell IV65536, the lower right cell in the worksheet. You could select the same cell by using two arguments with the Cells collection-Cells.Item(65536,256)-or by using the Range property-Range("IV65536").

    click to expand

  8. Type Cells(1).Select and press Enter. This selects cell A1.

    As with other collections, when you use the Cells property to get a collection of cells, you can leave out the Item method, and simply put the argument after the Cells property. The expression Cells(1) is equivalent to Cells.Item(1), which is equivalent to Range("A1"). All these expressions can be used interchangeably.

Refer to a Range as a Collection of Rows or Columns

In addition to referring to the worksheet range as a collection of cells, you can also think of it as a collection of rows or as a collection of columns. Analogous to the Cells property, the Rows property returns a collection of rows and the Columns property returns a collection of columns. These properties return collections, do not have their own object classes, and return Range objects.

  1. In the Object Browser, with the <globals> object selected in the list of classes, select the Columns property in the list of Members.

    The description shows that this property, similar to the Range property and the Cells property, returns a Range object.

  2. In the Immediate window, type Columns.Select and press Enter.

    This selects all the cells on the worksheet, exactly the same as Cells.Select. The difference between the two properties appears when you use the Item property to index into a single item in the collection.

  3. Type Columns(3).Select and press Enter. This selects column C, the third column on the worksheet.

    click to expand

  4. Type Columns("D").Select and press Enter. This selects column D. When you specify a column by letter, you are giving the name of the item and must enclose it in quotation marks.

    click to expand

  5. Type Columns("B:H").Select and press Enter.

    This selects the range of columns from B through H. The only way to specify a range of columns within the collection is by using the column letter names.

    click to expand

  6. Type Rows(2).Select and press Enter.

    This selects row 2. With rows, the name of an item is also a number. The expressions Rows(2) and Rows("2") are functionally equivalent.

    click to expand

  7. Type Rows("3:14").Select and press Enter.

    This selects a range of rows. The only way to specify a range of rows within the collection is by using the row numbers as a name-that is, by enclosing them in quotation marks.

    click to expand

The globals group in the Object Browser includes three properties that return all the cells of a worksheet-Cells, Columns, and Rows. In each case, you get a reference to a Range object, but the properties return that object as a collection of cells, columns, or rows, respectively. There are no object classes for Cells, Columns, and Rows. These are simply different ways of representing the same Range object.

Refer to a Range Based on the Current Selection

Many times when writing a macro you want to refer to a range that is somehow related to the active cell or to the current selection. The macro recorder uses the Selection property to refer to the selected range and the ActiveCell property to refer to the one active cell. A Range object has useful properties that can extend the active cell or the selection to include particularly useful ranges.

  1. In the Immediate window, type Range("B2").Select and press Enter. This selects the upper left cell of the sample list.

  2. In the Object Browser, with the globals group selected in the Classes list, select the ActiveCell property.

    The description at the bottom of the Object Browser shows that this property returns a Range object.

  3. Activate the Immediate window, choose the Edit menu, and click Complete Word. In the list of members, click ActiveCell.

    Tip 

    At the beginning of a statement-whether in a macro or in the Immediate window-when you use the Complete Word command, the Auto List displays all the members of the globals group.

    click to expand

  4. Type a period and then CurrentRegion.Select to create the statement ActiveCell.CurrentRegion.Select and then press Enter.

    This selects the entire sample list. The CurrentRegion property selects a rectangular range that includes the original cell and is surrounded by either blank cells or the edge of the worksheet. It is hard to overstate the usefulness of the CurrentRegion property.

    click to expand

  5. Type ActiveCell.EntireColumn.Select and press Enter.

    This selects all of column B because the active cell was cell B2. Because the starting range was the active cell-not the entire selection-the EntireColumn property returned a reference to only one column.

    click to expand

  6. In the Object Browser, with the globals group selected, select the Selection property in the list of members. The description at the bottom indicates that the Selection property returns an object, not a Range. The Selection property returns a Range object only when cells are selected. If shapes or parts of a chart are selected, this global property returns a different object type. Because the Selection object can return a different object type at different times, it does not display an Auto List the way the ActiveCell property does.

    click to expand

  7. In the Immediate window, type Selection.CurrentRegion.Select and press Enter. This selects the range B1:H14-the entire sample list plus the one row above it. When you use the CurrentRegion property with a multicell range such as the initial selection in this example, the new range is the current region of the upper left cell of that range.

    click to expand

  8. Type Selection.EntireRow.Select and press Enter. This selects rows 1 through 14. Because the starting range was a multicell range, the EntireRow property returns the range that extends all the rows of that range.

  9. Type Range('B2').Activate and press Enter. Because the specified cell is within the selected range, this statement does not change the selection, but it does move the active cell to a new location within the range. If you activate a cell that is not within the current selection, the Activate method behaves the same as Select.

    click to expand

The Selection and ActiveCell properties are useful as starting points for deriving other ranges. The ActiveCell property always returns a reference to a Range object, and therefore displays a convenient Auto List when you are entering a statement. The Selection property returns a reference to a Range object only when a range is actually selected, and thus it does not display an Auto List.

Refer to a Relative Range

When you use one of the range-returning properties from the globals group-Range, Cells, Columns, or Rows-you get a range based on the entire active worksheet. These same properties also exist as properties of a Range object. The easiest way to work with properties of a Range object is to declare a variable as a Range. Then the Auto List can show you the methods and properties as you type a statement, even if you start with a property such as Selection, which does not display an Auto List.

  1. In the Visual Basic Editor, choose the Insert menu and click Module. Type Sub TestRange and press Enter. Visual Basic adds parentheses and an End Sub statement.

  2. Type Dim myRange As Range and press F8 twice to initialize the variable.

  3. In the Immediate window, type Set myRange = Range("B2") and press Enter. Then type myRange.Select and press Enter. This selects cell B2, confirming that the variable contains a reference to that cell.

    Object Browser

  4. Click the Object Browser button. In the list of classes, select the Range class. Then in the list of members, select the Range property. This Range property appears very similar to the Range property of the globals group. It behaves, however, relative to a starting range.

  5. In the Immediate window, type myRange.Range("A1:G1").Select and press Enter.

    This does not select the range A1:G1. Rather, it selects the range B2:H2. If you think of cell B2 as the upper left cell of an imaginary worksheet, the range A1:G1 of that imaginary worksheet would correspond to the range B2:H2 of the real worksheet.

    click to expand

  6. Type Set myRange = Range("B2").CurrentRegion and press Enter. Then type myRange.Select and press Enter. This selects the entire sample list, confirming that the variable contains a reference to the range.

  7. Type myRange.Cells(2,6).Select and press Enter.

    This selects the first value in the Units column-the sixth column of the second row of the list.

    click to expand

  8. Type myRange.Rows(2).Select and press Enter.

    This selects the second row of values in the list, even though they exist in row 3 of the worksheet. A single row from the collection referenced by the global Rows property includes the entire row of the worksheet; the Rows property of a Range object includes only the cells within the range.

    click to expand

  9. Type myRange.Rows(myRange.Rows.Count).Select and press Enter.

    This selects the last row of the list. Because the Rows property returns a collection, you can use the Count property to find the number of items in the collection. That count can serve as an index into the same collection.

  10. Type myRange.Rows(myRange.Rows.Count+1).Select and press Enter.

    With the Cells, Rows, and Columns properties of a Range object, you can actually use an index that does not fit within the limits of the collection. This is a powerful way to select an appropriate range for entering new values or total formulas.

    click to expand

When you use the Range, Cells, Columns, or Rows properties from the global group-or if you use any of these properties with the Application object or a Worksheet object-the addresses are relative to the upper left cell of the worksheet. If you use any of these properties with a Range object, the addresses are relative to the upper left cell of that range.

Refer to Ranges Within the Current Region

Excel has other properties that can calculate a new range based on an existing range. The Offset property references a range shifted down, up, left, or right from a starting range. The Resize property references a range with a different number of rows or columns from a starting range.

  1. In the Object Browser, select Range in the Classes list. Then, in the list of members, select the Offset property.

    The description indicates that this property has two arguments-both of which are optional-and that it returns a Range object.

    click to expand

  2. In the Immediate window, type myRange.Offset(1).Select and press Enter.

    This selects a range identical in size and shape to the range stored in the variable, but shifted down by one cell. The first argument to the Offset property indicates the number of rows down to shift the range; the second argument indicates how many columns to the right to shift the range. Omitting an argument is the same as using zero and does not shift the range in that direction.

    click to expand

    Tip 

    To understand the Offset property, think of yourself as standing on the upper left cell of the initial range. Face the bottom of the worksheet, and step forward the number of steps specified in the first argument. Zero steps means no movement. Negative steps are backwards. Then face the right side of the worksheet and do the same with the number of steps specified in the second argument. The resulting range is the same size and shape as the original one, but it begins on the cell you end up standing on.

  3. In the Object Browser, select Range in the Classes list. Then, in the list of members, select the Resize property.

    The description indicates that this property has two arguments-both of which are optional-and that it returns a Range object.

  4. In the Immediate window, type myRange.Offset(1).Resize(5).Select and press Enter.

    This selects the first five rows of data. The Offset property shifts the range down to omit the heading row. The Resize function changes the size of the resulting range. The first argument to the Resize property is the number of rows for the result range; the second is the number of columns for the result range. Omitting an argument is the same as keeping the size of the original range for that direction.

    click to expand

  5. Type myRange.Offset(1).Resize(myRange.Rows.Count-1).Select and press Enter.

    This selects the range B3:H14, which is the entire list except the heading row. You often need to manipulate the body of a list separately from the heading.

    click to expand

  6. Type myRange.Offset(1,5).Resize(1,2).Select and press Enter.

    This selects the range G3:H3, which happens to be the data values in the first row of the body of the list.

    click to expand

    Tip 

    The combined functionality of the Offset and Resize properties is equivalent to that of the OFFSET function available on worksheets.

  7. Press F5 to end the macro.

The Offset and Resize properties, along with the EntireRow, EntireColumn, and CurrentRegion properties, provide you with flexible tools for calculating new Range objects based on an original starting range. Often, the easiest way to work within a range is to first use the CurrentRegion property to establish the base range, and then use the Offset and Resize properties to manipulate the range.



Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net