Chapter 4: Explore Range Objects

This article is an excerpt from Microsoft Office Excel 2007 Visual Basic for Applications Step by Step by Reed Jacobson from Microsoft Press (ISBN 9780735624023, copyright Microsoft Press 2008, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Contents

• Referring to a Range

• Refer to a Range as a Collection of Cells

• Refer to a Range as a Collection of Rows or Columns

• Enhancing Recorded Selections

• Entering Values and Formulas into a Range

• Formatting a Range

• Format the Interior of a Range

• Key Points

In this chapter, you will learn to:

• Use several properties to refer to Range objects from macro statements.

• Put values and formulas into cells.

• Simplify macros that record selections.

• Apply formatting to ranges.

• Use the Object Browser to learn about objects, properties, and methods.

The world would be much simpler if people were all the same size. You wouldn’t need adjustable seats in your car; your head would never get bumped on a door frame; your feet would never dangle from a chair. Of course, you’d have new problems as well: When you went to exchange that hideous outfit you got for your birthday, you wouldn’t be able to claim it was the wrong size.

When using Microsoft Visual Basic for Applications (VBA) to write macros for Microsoft Office Excel, you don’t need to worry about Range objects as long as all your worksheets and data files are the same size. For example, if you never insert new rows into a budget, if you always put yearly totals in column M, and if every month’s transaction file has exactly 12 columns and 120 rows, you can skip this chapter because the macro recorder can take care of dealing with ranges for you.

But in the real-live human world, people are different sizes, and consequently clothes come in different sizes and cars have adjustable seats. And in the real-live worksheet world, models and data files have different—and changing—sizes, and your macros need to fit them. Excel provides many methods and properties for working with Range objects. In this chapter, you’ll explore Range objects and in the process learn how you can use the Object Browser to learn about any new, unfamiliar object.

USE the Ranges.xlsx workbook. This practice fi le is located in the Documents\MSP\ExcelVBA07SBS folder.

BE SURE TO save a macro-enabled copy of the Ranges.xlsx workbook as Chapter4.xlsm in the trusted folder location you created in Chapter 1.

OPEN the Chapter4.xlsm workbook.

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 techniques in more 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.

1. In the Chapter04 workbook, right-click a worksheet tab, and then click View Code on the shortcut menu to display the Visual Basic editor.

   Rearrange the Excel and Visual Basic editor windows so that you can see them side by side.

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

   Figure 1

   

3. In the Classes list, select the <globals> object, click in the Members of '<globals>' list, and press the R key 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 brackets around the second argument indicate that it is optional. The Range property returns a reference to a Range object.

   Figure 2

   

4. Right-click the Range property name in the Members list, and click Copy on the shortcut menu.

5. Click the View menu, and click Immediate Window.

6. Right-click the Immediate window, and click Paste.

   This is equivalent to using the Complete Word command to enter the function name.

7. After the Range property, type an opening parenthesis (Visual Basic will display the argument list), and then 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.

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

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

   Figure 3

   

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

11. Type ?Selection.Count and press Enter.

The number 91 appears in the Immediate window. There are 91 cells in the currently selected range. If you don’t specify otherwise, Excel treats a range object as a collection of cells. If you want to know the number of rows or columns in the range, you can do that by using specialized properties, as you will learn in the section titled “Refer to a Range as a Collection of Rows or Columns,” later in this chapter.

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. Once you have the resulting reference to a range object, you can use any of the members that appear in Object Browser for the Range class.

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. However, this is not a separate class. 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. Because Excel thinks of any range, by default, as a collection of cells, you typically use the Cells property as an alternative to the Range property—using numbers, rather than text strings.

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.

   Figure 4

   

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.

   Figure 5

   

3. Type Cells.Item(5).Select and press Enter. This selects cell E1, the fi fth cell in the firstrow. 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.

   Figure 6

   

4. Type Cells.Item(16383).Select and press Enter. This selects cell XFC1, the next to the last cell in the firstrow. Excel 2007 now allows 16384 cells in a single row.

   Figure 7

   

5. Type Cells.Item(16385).Select and press Enter. This selects cell A2, the firstcell 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. Since each row of the worksheet contains 16384 cells, cell 16385 is the firstcell on the second row.

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

   Figure 8

   

   Important

   In previous versions of Excel, the expression Cells.Item(257) referred to cell A2. In Excel 2007, it now refers to cell IW1, the 257th cell in the firstrow. In order to write macros that work in multiple versions, you should always use the row and column specification in the Cells function. Another consequence of the larger size of the worksheet is that you cannot use the expression Cells.Count to retrieve the number of cells on the worksheet, because the number is too big. This is unlikely to ever be a problem, but it illustrates the expanded size of the worksheet grid.

7. Type Cells**.Item(1048576,16384).Select** and press Enter. This selects cell XFD1048576, the bottom right cell in the worksheet. In case you wonder, these bizarre-looking numbers are really simple powers of 2. You could select the same cell by using the expression Cells.Item(2^20,2^14). You could also use the Range property—Range("XFD1048576").

   Figure 9

   

8. Type Cells(1).Select and press Enter to select 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.

   Figure 10

   

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.

   Figure 11

   

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.

   Figure 12

   

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.

   Figure 13

   

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

   Figure 14

   

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.

   Figure 15

   

The <globals> object 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 Active Cell

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> object 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. In the Immediate window, click the Edit menu, and then click Complete Word. In the list of members, click ActiveCell.

   Tip

   When you use the Complete Word command at the beginning of a statement— whether in a macro or in the Immediate window—the Auto List displays all the members of the <globals> object. If you like using the keyboard, you can press Ctrl+Space to display the list of members, type partial words and use arrow keys to select the desired member, and then press the Tab key to insert the member into the statement.

   Figure 16

   

4. Type a period ( . ). Then type 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.

   Figure 17

   

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. Because the initial active cell—B2—is still within the selection, it is still the active cell.

   Figure 18

   

6. In the Object Browser, with the <globals> object 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.

   Figure 19

   

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. It’s acting the same as if the current selection were only cell B1. When you use the CurrentRegion property with a multicell range as the starting point, it ignores everything except the top-left cell of the range as it calculates the current region.

   Figure 20

   

8. Type Range("A2").Activate and press Enter.

   Because the specified cell is outside of the current selection, the Activate method behaves the same as Select.

   Figure 21

   

9. Type Selection.EntireRow.Select and press Enter.

   This selects all of row 2. Because the selection is a single cell, you would get exactly the same result by using ActiveCell.EntireRow.Select.

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

    Figure 22

    

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 Subsets of a Range

When you reference a range by using a property from the <globals> object—for example, 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 displays the methods and properties as you type a statement, even if you use the Selection property—which does not display Auto Lists—to assign the range to the variable.

1. In the Visual Basic editor, click Insert, and then click Module.

2. Type Sub TestRange and press Enter.

   Visual Basic adds parentheses and an End Sub statement.

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

4. In the Immediate window, type Set myRange = Range("B2") and press Enter. Then type myRange.Select and press Enter again.

   This selects cell B2, confirming that the variable contains a reference to that cell.

5. 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> object. It behaves, however, relative to a starting range.

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

   Figure 23

   

7. Type Set myRange = myRange.CurrentRegion and press Enter. Then type myRange.Select and press Enter again.

   Given that myRange already referred to cell B2, which is inside the sample list, the first statement references the entire sample list, and the second confirms that the variable contains a reference to the appropriate range.

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

   This selects the first data value in the Units column—row 2 and column 6 within the data region.

   Figure 24

   

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

10. 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 then serve as an index into the same collection.

    Figure 25

    

When you use the Range, Cells, Columns, or Rows properties as members of a Range object, the resulting ranges are relative to the upper-left cell of that range. Contrast this to when you use the same functions from the global group—or as members of the Application object or of a Worksheet object. With anything other than a Range object, these functions return ranges that are relative to the upper-left cell of the worksheet.

Refer to a Relative Range

Excel has other properties that can calculate a new range based on one or more existing ranges. Two of these properties do not exist in the list of global members; they exist only as members of a Range object: the Offset property references a range shifted down, up, left, or right from a starting range, and the Resize property references a range with a different number of rows or columns from a starting range. An additional property, the Intersect property, does appear in the list of global members. It is particularly valuable when you need to “trim away” part of a range, such as when you want to remove the header row from the current region.

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

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

   Figure 26

   

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

   Figure 27

   

   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—RowSize and ColumnSize, 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.

   Figure 28

   

5. Type myRange.Offset(1,5).Resize(1,2).Select and press Enter. This selects the range G3:H3, which happens to be the numeric values in the firstrow of the body of the list.

   Figure 29

   

   Tip

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

6. In the Object Browser, with the <globals> object selected in the list of classes, select the Intersect method in the Members list.

   The description shows that this method returns a Range object, but it also shows that it can take up to 30 arguments! In practice, you usually use two arguments, and you can see that the first two arguments are required. The Object Browser shows that the first two arguments must be range objects, but if you use more than two arguments, they do all need to be ranges. You can use the Intersect method in conjunction with the Offset method to remove headings from the current region.

   Figure 30

   

7. In the Immediate window, type Intersect(myRange, myRange.Offset(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. By using a range as the first argument of the Intersect method, and then an offset version of the range as the second argument, you can trim off portions of the range.

   Figure 31

   

8. Press F5 to end the macro.

The Offset and Resize properties, along with the EntireRow, EntireColumn, and CurrentRegion properties and the Intersect method, 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 property and the Intersect method to manipulate the range.

Enhancing Recorded Selections

When you record a macro, the macro recorder dutifully follows all your actions, including selecting ranges before acting on them. You can make a macro do less work—and make it easier to read—by eliminating unnecessary selection changes. A powerful technique for eliminating unnecessary changes to the selection begins with watching for a statement ending in Select followed by one or more statements beginning with Selection or ActiveCell. What you do next depends on whether a single Selection (or ActiveCell) statement follows the Select statement or whether a group of statements follows.

Simplify Select…Selection Pairs

When a single Selection statement follows a Select statement, you can collapse the two statements into one. Record and simplify a macro that puts the names of the months across the top of a worksheet.

1. In Excel, insert a blank worksheet and start recording a macro named LabelMonths. Type the labels January, February, and March in the cells B1, C1, and D1.

   Figure 32

   

2. Turn off the recorder, and then edit the macro. The macro should look similar to the following code. (Your macro might be slightly different, depending on the key you press to enter the values into the cells.)

   Sub LabelMonths()
      Range("B1").Select
      8ActiveCell.FormulaR1C1 = "January"
      Range("C1").Select
      ActiveCell.FormulaR1C1 = "February"
      Range("D1").Select
      ActiveCell.FormulaR1C1 = "March"
      Range("D2").Select
   End Sub
   

   For each cell, the word Select appears at the end of one statement followed by either the word Selection or ActiveCell at the beginning of the next statement. You can delete both words, leaving only a single period. If a Select statement is the last one in a macro, you can delete it entirely.

3. Remove the unnecessary selections from the LabelMonths macro by deleting Select and ActiveCell each time they appear.

   The final macro should look like this:

   Sub LabelMonths()
      Range("B1").FormulaR1C1 = "January"
      Range("C1").FormulaR1C1 = "February"
      Range("D1").FormulaR1C1 = "March"
   End Sub
   

4. Insert a new blank worksheet, and test the macro.

   The labels appear in the cells, and the original selection doesn’t change.

Why should you get rid of Select…Selection pairs? One reason is that doing so does make the macro run faster. Another reason is that running a macro can seem less disruptive if it doesn’t end with different cells selected than when it started. But the most important reason is unquestionably that Select…Selection pairs in a macro are a dead giveaway that you’re a beginner who uses the macro recorder to create macros. It’s OK to use the macro recorder; you just want to cover your tracks.

Simplify Select Groups

When you eliminate a Select…Selection pair, be sure that only a single statement uses the selection. If you have a single Select statement followed by two or more statements that use the selection, you can still avoid changing the selection, but you must do it in a different way.

1. In Excel, select a sheet with labels in the first row, and start recording a macro named MakeBoldItalic.

2. Click cell B1, click the Bold button, click the Italic button, and then click the Stop Recording button.

   Figure 33

   

3. Edit the macro to look like this:

   Sub MakeBoldItalic()
      Range("B1").Select
      Selection.Font.Bold = True
      Selection.Font.Italic = True
   End Sub
   

   Obviously, if you delete the first Select…Selection pair, the macro won’t control which cells will become italicized.

4. Edit the macro to assign the range to a variable named myRange. Then replace the Selection object with the myRange object. The finished macro should look like this:

   Sub MakeBoldItalic()
      Dim myRange As Range
      Set myRange = Range("B1")
      myRange.Font.Bold = True
      myRange.Font.Italic = True
   End Sub
   

5. Change "B1" to "C1" in the macro, and then press F8 repeatedly to step through the macro. Watch how the format of the cell changes without changing which cell is originally selected.

6. Save the Chapter04 workbook.

Eliminating the selection when there’s a group might not seem like much of a simplification. And with only two statements, it probably isn’t. But when you have several statements that use the same selection, storing the range in a variable can make the macro much easier to read.

Tip

You could also replace the Select group with a With structure, like this: Secretly in the background, the With structure really just creates a hidden variable, takes the object from the With statement, and assigns that object to the hidden variable. It then puts the hidden variable in front of each “dangling” period. The End With statement discards the hidden variable. An advantage of using an explicit object variable is that you can declare the variable with a specific object type—for example, Dim myRange as Range—and then VBA checks to make sure any methods or properties you use are appropriate. With an explicitly declared variable, VBA also offers Auto Lists to help you modify a macro.

With Range("B1")
   .Font.Bold = True
   .Font.Italic = True
End With

Entering Values and Formulas into a Range

You may have situations where you want to create a macro that dynamically enters formulas into cells. First you should understand how references work in formulas in Excel, and then you can see how to create formulas in a macro.

Relative References

Most formulas perform arithmetic operations on values retrieved from other cells. Excel formulas use cell references to retrieve values from cells. Imagine, for example, a list of Retail prices and Wholesale costs.

Figure 34

Suppose you want to add a column to the list that calculates the gross margin—the difference between the Retail price and the Wholesale cost—for each item. You would put the label Margin in cell D1 and then enter the first formula into cell D2. The formula subtracts the first Wholesale cost (cell C2) from the first Retail price (cell B2). So you would enter =B2-C2 into cell D2.

Figure 35

For each item in the High group, the gross margin is $2.75. Now you need to copy the formula to the other rows. The formula you typed into cell D2 refers explicitly to cells C2 and B2. When you copy the formula to cell D3, you want the formula to automatically adjust to refer to C3 and B3. Fortunately, when you copy the formulas, Excel adjusts the references because, by default, references are relative to the cell that contains the formula. (The Prices worksheet in the Chapter04 workbook contains these formulas.)

Figure 36

If the reference =C2 is found in cell D2, it really means “one cell to my left.” When you copy the formula to cell D3, it still means “one cell to my left,” but now that meaning is represented by the reference =C3.

Absolute References

Sometimes you don’t want relative references. Imagine, for example, a worksheet that contains various quantities in column B and prices in row 3. (The Revenue worksheet in the Chapter04 workbook contains the prices and quantities.)

Figure 37

Suppose you want to add formulas to calculate the revenue for each combination. To calculate the first revenue value (cell C4), you need to multiply the first quantity (cell B4) by the first price (cell C3). When you type = B4*C3 into cell C4, you get the correct answer: $50.

But if you copy that formula to cell C5, you get the ridiculous answer of $1000. That’s because the cell references are relative. In this version of the formula, you’re not really referring to cells B4 and C3; you’re referring to “one cell to my left” and “one cell above me.” When you put the formula into cell C5, “one cell above me” now refers to cell C4, not cell C3.

Figure 38

In the Revenue table, you want the Quantity cell references to adjust from row to row, and you want the Price cell references to adjust from column to column, but you always want to reference the Quantity from column B and the Price from row 3. The solution in the user interface is to put a dollar sign ($) in front of the B in the first Quantity reference ($B4), and in front of the 3 in the first Price reference (C$3). The formula that should go into cell C4 is =$B4*C$3. The dollar sign “anchors” that part of the formula, making it absolute. When you copy the formula to the rest of the range C4:E8, you get correct answers. (The RevenueFormulas worksheet in the Chapter04 workbook contains the correct formulas.)

Figure 39

The relative portion of the formula changes with the row or column of the cell that contains the formula. The absolute portion remains fixed.

If you want to modify the formula so that it also takes into account the discount value from cell G3, you must make both the row and the column of the discount reference absolute. The correct formula would be =$B4*C$3*(1-$G$3). If you assign a name to a cell—for example, if you assign the name Discount to cell G3—then by default, using the name in the formula acts as a completely absolute reference. (The RevenueFormulas worksheet in the Chapter04 workbook contains these formulas.) Later in this chapter, you will create a macro that will fill the grid with the correct formula, regardless of where it is on the worksheet and how many rows or columns it has.

R1C1 Reference Style

As a default, Excel displays letters for column headings and numbers for row headings. Consequently, the default name for the upper-left cell in the worksheet is cell A1. Referring to cells by letter and number is called A1 reference style. In A1 reference style, however, cell references do not really say what they mean. For example, the reference =C4 says “cell C4,” but if it’s in a formula in cell E4, it really means “two cells to my left,” and if it’s in a formula in cell C5, it really means “one cell above me.” You don’t know what the reference really means until you know which cell contains the reference.

Excel has an alternate reference style that uses numbers for both column and row headings. In this alternate reference style, to refer to a cell you use the letter R plus the row number and C plus the column number. Consequently, the upper-left cell in the worksheet is cell R1C1. Referring to cells by numbers in both rows and columns is called R1C1 reference style. In R1C1 reference style, cell references really do say what they mean. Consequently, in macros, when VBA has to understand and use the formulas, it is usually convenient to use R1C1 reference style. When a human has to understand the formula, it is usually easier to use A1 reference style, which is why A1 reference style is the default.

You can, however, change the user interface to use R1C1 reference style if you want to try it out. To turn on R1C1 reference style, click the Microsoft Office Button and then click Excel Options. On the Formulas page, select the R1C1 Reference Style check box, and click OK. (To turn off R1C1 reference style, clear the check box.) The setting in the Excel Options dialog box does not have any effect on macros: a macro can enter formulas using either reference style.

Figure 40

In R1C1 reference style, to specify a relative reference on the same row or column as the cell with the formula, you simply use an R or a C, without a number. For example, the reference =RC3 means “the cell in column 3 of the same row as me,” and the reference =R2C means “the cell in row 2 of the same column as me.” To specify a relative reference in a different row or column, you indicate the amount of the difference, in square brackets, after the R or the C. For example, the reference =R5C[2] means “two columns to my right in row 5,” and the reference =R[-1]C means “one cell above me.”

The correct formula for calculating the gross margin on the Prices worksheet was =B2-C2, but only if the formula was entered into cell D2. In R1C1 reference style, the equivalent formula is =RC[-2]-RC[-1] , and it doesn’t matter which row contains the formula. The formula to calculate the discounted price on the Revenue worksheet was =$B4*C$3*(1-$G$3), at least for cell C4. In R1C1 reference style, the same formula is =RC2*R3C*(1-R3C7), again, regardless of which cell contains the formula.

Important
When you use A1 reference style, the formula changes depending on which range you copy the formula into. When you use R1C1 reference style, the formula is the same, regardless of which cell it goes into. The reference style only makes a difference when you put the same formula into multiple cells.

Put Values and Formulas into a Range

You can explore the properties for putting values and formulas into a range by creating a simple list of incrementing numbers.

1. In the Visual Basic editor, activate the Immediate window, type Worksheets.Add, and press Enter to create a new, blank worksheet in the active workbook.

2. Type Range("B2:B6").Select and press Enter to select a sample starting range of cells.

   Figure 41

   

3. Type Selection.Value = 100 and press Enter to fill all the cells of the selection with the number 100.

   Value is a property of the range. When you set the Value property in conjunction with a multicell range, you change all the cells in the range.

   Tip

   When assigning a constant value to a range, the Formula property is equivalent to the Value property, so Selection.Formula = 100 is the same as Selection.Value = 100. The Formula property is equivalent to whatever you see in the formula bar when the cell is selected. The formula bar can contain constants as well as formulas, and so can the Formula property. When you assign a value to a cell, the Formula property and the Value property have the same effect.

4. Type ActiveCell.Value = 0 and press Enter to change cell B2 to 0.

   Only the active cell changes, not the selected cells. Entering a value in the active cell is equivalent to typing a value and pressing Enter. Entering a value in the selection is equivalent to typing a value and pressing Ctrl+Enter.

   Figure 42

   

   Suppose you want to enter a value in the cell above the active cell, whatever the active cell might be.

5. Type ActiveCell.Offset(-1).Value = 1 and press Enter to change the value in cell B1 to 1.

   This statement starts with the active cell, uses the Offset property to calculate a new cell one up from that starting cell, and then sets the Value property for the resulting cell.

   Figure 43

   

6. Type Selection.FormulaR1C1 = "=R[-1]C*5" and press Enter.

   Figure 44

   

   Now each of the selected cells contains a formula, not a constant. The FormulaR1C1 property expects a formula in R1C1 reference style. The reference R[-1]C always means “one cell above” regardless of which cell gets the formula.

7. Type ?ActiveCell.Value and press Enter.

   This statement displays the value 5 in the Immediate window. The Value property retrieves the result of any formula in a cell. When you retrieve the contents of the cell that contains a formula, the Value property gives you the result of the formula.

8. Type ?ActiveCell.Formula and press Enter. This statement displays the formula =B1*5 in the Immediate window. When you retrieve the contents of a cell that contains a formula, the Formula property gives you the formula using A1 reference style. The setting in the Excel Options dialog box is ignored. If you want to retrieve the formula using R1C1 reference style, use the FormulaR1C1 property.

All cells have Formula, FormulaR1C1, and Value properties. The Value property and the Formula property behave the same when you’re writing to the cell. When you read the value of a cell, the Value property gives you the value, and the Formula property gives you the formula using A1 reference style. The FormulaR1C1 property is the same as the Formula property, except that it uses R1C1 reference style for all references, whether assigning a formula to the cell or reading the formula from a cell.

Tip

The Value property always gives you the unformatted value of the number in a cell. A cell also has a Text property, which returns the formatted value of the cell. The Text property is read-only because it’s a combination of the Value property and the NumberFormat property. A range also has a Value2 property. The difference between Value and Value2 has to do with dealing with very large, very precise numbers—as in banking. The Value property uses a data type (double-precision floating point) that can handle either very large numbers or very precise numbers, but not at the same time. The Value2 property uses a data type (currency) that can handle the large-scale precision needed in financial summaries.

Construct Formulas to Fill a Grid

Sometimes you need a macro to create formulas that contain references. For example, suppose want to create a macro that will enter the appropriate formulas into the Revenue grid. You could just record a macro, but a recorded macro will use specific cell addresses. Suppose that the grid could be anywhere on the worksheet—not just starting in cell A1—and that it could be of any size. Your recorded macro can’t handle that kind of variation.

If you can make a few simple assumptions, you can create a macro that will find a grid, select the current region to find the size of the grid, and then add the correct formula. You can even have the macro automatically find the location of the Discount cell and assign a name to it so that the formula can reference the cell by name. The assumptions you need to make are very useful for most simple macros:

• Always use the consistent words as labels so you can have the macro search for them.

• Always keep the same number of header columns and rows.

• Separate ranges are separated by at least one empty row or one empty column so that the CurrentRegion method can detect the rectangle.

On the Revenue worksheet, the searchable labels are Price and Discount, the grid has two header rows and one header column, and the ranges are separated by column F. With those simple assumptions, you can create a macro that will automatically create the right formula and put it into the correct range.

1. Make a copy of the Revenue worksheet in the Chapter04 workbook, with cell A1 selected.

   Copying the worksheet will give you a chance to test the macro, moving and resizing the revenue grid.

2. In the Visual Basic editor, enter the following macro shell, and press F8 twice to step to the End Sub statement.

   Sub FillFormulas()
      Dim myOuter as Range
      Dim myInner as Range
      Dim myFormula as String
   End Sub()
   

   Declaring variables at the top will make it easier to work with different ranges. The myOuter range will refer to the entire current region of the Revenue grid, including the headings. The myInner range will refer to the empty cells in the middle that need formulas. The myFormula string will contain the formula so that you can construct the formula piece by piece in the macro.

3. In the Object Browser, select Range in the Classes list. Then, in the list of members, select the Find method.

   The description indicates that this property has one required argument—the string you’re searching for—and that it returns a reference to a range.

   Figure 45

   

4. In the Immediate Window, type Set myOuter = Cells.Find("Price").CurrentRegion and then press Enter. When you can confirm the correct range by entering myOuter.Select, copy the statement into the macro.

   Figure 46

   

   See Also The Intersect and Offset functions are described in the section titled “Refer to a Relative Range” earlier in this chapter.

   For the myInner range, you need to remove two header rows at the top and the one header column at the left. You can do that by using the combination of Intersect and Offset.

5. In the Immediate window, type Set myInner = Intersect(myOuter,myOuter.Offset(2,1)) and press Enter. When you can confirm the correct range by entering myOuter.Select, copy the statement into the macro.

   Figure 47

   

   For the Discount range, you can use the label in the top cell to define the name for the lower cell. This creates a name in the worksheet, rather than a variable in Visual Basic. You assign the name by using the CreateNames method. The CreateNames method has four arguments, Top, Bottom, Left, and Right, respectively. These identify which side of the range contains the labels you should use as names. The Discount label is above the discount value, so Top is the only one you need to designate as True. Since Top is the first argument, you can simply omit the others.

6. In the Immediate window, type Cells.Find("Discount").CurrentRegion.CreateNames True and press Enter. When you can confirm the correct range by entering Range("Discount").Select, copy the statement into the macro.

   Figure 48

   

   For the first part of the formula, you need a reference to the first Price cell, which is currently cell C3. If you think of myOuter as if it were a worksheet, you want cell “B2” of that imaginary worksheet, and you want the address in R1C1 notation, with an absolute row number and a relative column number, from the point of view of the first formula cell. The Address method gives you the address of a cell, with arguments to control what it looks like. Visual Basic prompts you for each of the arguments.

7. In the Immediate window, type myFormula = myOuter.Range("B2").Address(True,False,xlR1C1,False,myInner) and press Enter. Move the mouse pointer over the word myFormula to confirm that the address is R3C, and then copy the statement into the macro.

   Figure 49

   

   Tip

   When you use the Range method, Visual Basic shows you tips for the methods and properties that follow. When you use the Cells method, Visual Basic does not show tips. Even though Range("B2") and Cells(2,2) are functionally equivalent, using the Range method makes the statement easier to type. If you find the Cells method easier to understand, you can make the change after you have successfully created the statement.

8. In the Immediate window, type myFormula = "=" & myFormula & "*". Move the mouse pointer over the word myFormula to confirm that the value is =R3C*, and then copy the statement into the macro.

9. Enter the following three statements into the macro, optionally testing each one first in the Immediate window.

   myFormula = myFormula & _
      myOuter.Range("A3").Address(False, True, xlR1C1, False, myInner)
   myFormula = myFormula & " * ( 1 - Discount ) "
   myInner.FormulaR1C1 = myFormula
   

   There is nothing fundamentally new in these statements. The first one appends the quantity address, with relative column and absolute row. The second one adds the Discount portion of the formula. The Discount portion doesn’t need to be converted to an address because it’s already a name in the worksheet. The third statement assigns the finished formula to the inner range.

10. Create a new copy of the Revenue worksheet, and test the macro. Then make another copy, change the size and location of the revenue grid, and test the macro again.

    Figure 50

    

Filling ranges of variable sizes with formulas is a powerful technique. You can use the methods and properties of the Range object to create the formula and to find the correct range to fill.

Formatting a Range

Formatting contributes much to the usability of a worksheet. Borders and background colors can emphasize parts of a report, and conditional formatting can highlight exceptions within a range. Cell formatting can be combined into cell styles to make the same formatting combinations easy to reuse.

Add Borders to a Range

Borders help to demarcate regions with a block of cells. Sometimes you want to put borders around every cell within a range. Sometimes you want to put a single border around an entire range of cells. Sometimes you want a different border along one side of a range. A Range object has methods and properties to allow you to completely control whatever type of border you need.

1. In Excel, make a copy of the RevenueFormulas worksheet. In the Visual Basic editor, copy the TestRange macro, give the new one the name AddBorders, and press F8 twice to initialize the myRange variable.

   Caution
   If you don’t have a TestRange macro, see the first two steps of the “Refer to a Relative Range” section earlier in this chapter.

2. In the Immediate window, type Set myRange = Range("B2").CurrentRegion and press Enter to assign the range containing the revenue calculations to the variable

3. In the Immediate window, type myRange.Borders.LineStyle =.

   As you type each period in the statement, an Auto List displays the available members. After you type the equal sign, no Auto List appears, but you can use the Object Browser to find the available options.

4. In the Object Browser, in the Search Text box above the Classes list, type LineStyle, and click the Search button.

5. In the Search Results pane that appears, select XlLineStyle in the Class list.

   The Member list shows all the possible constants you can use for the LineStyle property.

   Figure 51

   

   XlLineStyle is not really a class, even though it shows up in the list of classes in the Object Browser. There is no such thing as an XlLineStyle object. It is, rather, an enumerated list. An enumerated list is used when a property or argument can accept only certain values. An enumerated list allows the object model designer to give each of those values a special name—for example, xlContinuous. Enumerated lists are included in the list of Classes, but with a special icon.

6. In the Immediate window, type xlContinuous to finish the statement, and then press Enter.

   This adds a continuous border around each cell in the range. When you assign a value to the LineStyle property of the Borders object, the property changes for the border of each cell in the entire range.

   Figure 52

   

7. In the Immediate window, type myRange.Borders.LineStyle = xlNone and press Enter to remove the borders.

   The value xlNone does not appear in the enumeration list for LineStyle because it is a global constant that is used by many Excel objects. You can search for it in the Object Browser if you want to see the complete list of global constants.

   The Borders object is actually a collection, and you can select specific borders within that collection. In principle, you could change cell borders one at a time, but because putting a border around an entire range is a common operation, there is a special method just for doing that.

8. In the Immediate window, type myRange.BorderAround Weight:=xlThick and press Enter.

   This changes the edges of the range to a thick border. Because Weight is not the first argument, you have to type its name if you leave out LineStyle. Setting the border weight to Thick implies that the line will be continuous.

   Figure 53

   

   Suppose that you want a border on the right side of the quantities. To specify a single border, you can use an enumerated name in conjunction with the Borders collection. Auto Lists can help you with the syntax, but you have to be a little tricky

9. In the Immediate Window, type myRange.Borders(xlEdgeRight).LineStyle = xlContinuous, but do not press Enter. Immediately after myRange, type a period ( . ), type Columns(1), and then press Enter.

   Once you use the Columns property in a statement, you don’t see any more Auto Lists, but if you temporarily leave out the Columns property, you get Auto Lists for everything else except the line style. Then, after you get the syntax correct for the statement, you can go back and add the Columns property.

   Figure 54

   

10. In the Immediate window, type myRange.Rows(2).Borders(xlEdgeBottom).LineStyle = xlContinuous and press Enter. This adds a border under the row of prices.

11. Press F5 to end the macro. Copy the statements from the Immediate window into the AddBorders macro, and delete the two statements that fill and remove all the borders.

    The finished macro should look like this:

    Sub SetBorders()
       Dim myRange As Range
       Set myRange = Range("B2").CurrentRegion
       myRange.BorderAround Weight:=xlThick
       myRange.Columns(1).Borders(xlEdgeRight).LineStyle = xlContinuous
       myRange.Rows(2).Borders(xlEdgeBottom).LineStyle = xlContinuous
    End Sub
    

12. Create a new copy of RevenueFormulas and test the finished macro.

Borders can emphasize parts of a report. The Borders collection allows you to change all the borders at one time or choose a particular type of border to modify. The BorderAround method is a convenient shortcut for assigning a border to all the edges of a multicell range.

Format the Interior of a Range

To enhance the readability of a worksheet, you might want to apply different background colors to various parts. For example, you might apply one format to all the cells that contain values that a user can input, and a different format to all cells that contain formulas.

1. In Excel, create another copy of the RevenueFormulas worksheet. In Visual Basic, copy the TestRange macro, name the new one AddColors, and press F8 twice to initialize the myRange variable.

   Note
   If you don’t have a TestRange macro, see the first two steps of the “Refer to a Relative Range” section earlier in this chapter.

2. In the Immediate window, type Set myRange = Range("B2").CurrentRegion and press Enter to assign the range containing the revenue calculations to the variable.

3. In the Immediate window, type myRange.Interior.Color =.

   As you type each period in the statement, an Auto List displays the available members. After you type the equal sign, however, no Auto List appears. For the Color property, there is no enumerated list. You can enter any number between 0 (which equals black) and 16777215 (which equals white), so there are literally more than 16 million possible values. This is a major change from previous versions of Excel, where colors in a worksheet were limited to a palette of only 56 colors.

   Colors on a computer correspond to the red, green, and blue guns of a cathode ray tube. (Liquid crystal displays use a different technology, but the same component colors.) Visual Basic has an RGB function you can use to specify precise red, green, and blue components, but Excel provides an easier way to specify the color you want: it includes an enumerated list that gives meaningful names to about 140 of the most common colors.

4. In the Immediate window, type rgbMediumVioletRed to complete the statement, and press Enter. (Once you get past rgbM, press Ctrl+Space to get to the middle of the rgb color values.)

   The background color of the entire range changes to a medium violet red.

   Figure 55

   

   Now that Excel can handle millions of colors, it has a new capability to change how light (the tint) or dark (the shade) a color is without changing the actual color (the hue).

5. In the Immediate window, type myRange.Interior.TintAndShade = -0.2 and press Enter. The color changes to a slightly darker shade of violet red.

   A range object has a special method called SpecialCells that isolates cells within the range based on various attributes. For example, you can reference all the formula cells within the range.

6. In the Immediate window, type myRange.SpecialCells(xlCellTypeFormulas).Interior.TintAndShade = 0.3 and press Enter.

   The block of formulas changes to a lighter tint of violet red. In this range, the formulas form a contiguous block, but SpecialCells can return a range of discontiguous cells as well.

   Figure 56

   

   In Excel, you can give a name to a set of formatting characteristics. This is called a cell style. There are several built-in styles in a workbook. One of them is named Input, so that you can use it to format cells that can accept user input—typically cells that contain constants that are numbers.

7. In the Immediate window, type myRange.SpecialCells(xlCellTypeConstants, xlNumbers).Style = "Input" and press Enter.

   The cells with prices and quantities change to a light tan, with borders around each cell. The constant xlNumbers doesn’t appear in an Auto List, but you can find the list in the Object Browser by searching for SpecialCells.

   Figure 57

   

   You can modify the style format in the same way that you can modify a range format directly.

8. Enter the following two statements in the Immediate window:

   ActiveWorkbook.Styles("Input").Interior.Color = rgbMediumVioletRed
   ActiveWorkbook.Styles("Input").Interior.TintAndShade = 0.5
   

   This changes the Input style so that it has a lighter version of the same violet red shade as the rest of the cells. When applying a style to cells that can take input values, you may want to search the entire worksheet for the numeric constants. To do that, you just start with the global Cells property.

9. In the Immediate window, type Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Style = "Input" and press Enter.

   This adds the Input style to the Discount cell value. If you had hundreds of input cells scattered all over the worksheet, this statement would still find them all. The text labels in the Revenue range are hard to read, with the black text on a dark background. You can use SpecialCells to isolate all the cells that contain text constants.

10. In the Immediate window, type myRange.SpecialCells(xlCellTypeConstants, xlTextValues).Font.Color = rgbWhite and press Enter.

    This changes the font color for the labels to white, but they would look better bold as well. In fact, all the constants within the formula range would look better if they were bold.

11. In the Immediate window, type myRange.SpecialCells(xlCellTypeConstants).Font.Bold = True and press Enter.

    This changes all the constants within the range store in myRange to bold. By leaving out the second argument to SpecialCells, you get everything that matches the general type. You can also use a special style to clear all the formatting.

    Figure 58

    

12. In the Immediate window, type Cells.Style = "Normal" and press Enter.

    This clears all the formatting, including the number and formats. When you clear formats from a worksheet, what it really does is apply the Normal style to all the cells. By changing the Normal style, you change the default appearance of cells in the workbook.

    Figure 59

    

13. Press F5 to end the macro. Copy the statements from the Immediate window into the AddColors macro, and delete the statement that clears all the formatting.

    The finished macro, ignoring optional line breaks, should look like this:

    Sub SetColors()
    Dim myRange As Range
    Set myRange = Range("B2").CurrentRegion
    myRange.Interior.Color = rgbMediumVioletRed
    myRange.Interior.TintAndShade = -0.2
    myRange.SpecialCells(xlCellTypeFormulas).Interior.TintAndShade = 0.3
    myRange.SpecialCells(xlCellTypeConstants, xlNumbers). Style = "Input"
    ActiveWorkbook.Styles("Input").Interior .Color = rgbMediumVioletRed
    ActiveWorkbook.Styles("Input").Interior .TintAndShade = 0.5
    Cells.SpecialCells(xlCellTypeConstants, xlNumbers) .Style = "Input"
    myRange.SpecialCells(xlCellTypeConstants, xlTextValues) .Font.Color = rgbWhite
    myRange.SpecialCells(xlCellTypeConstants).Font.Bold = True
    End Sub
    

14. Create a new copy of RevenueFormulas and test the finished macro.

Ranges are powerful objects. They are the essence of Excel. With ranges you can organize information, create formulas, and apply formatting. And you can do all of that with under the control of VBA macros.

CLOSE the Chapter04.xlsm workbook.

Key Points

• Use the Object Browser to find out what members—methods and properties—are available for an object, and what each method or property returns.

• Avoid changing the selection during your macros. A macro runs faster and appears more professional if it doesn’t have to repaint the screen.

• While debugging, use the Immediate window to test the current reference of a range object.

• Many range-related functions start with one range and return another range. These functions are invaluable for navigating from one range to another. The most important one is CurrentRegion.

• Always use R1C1 references when constructing formulas from macros, and take advantage of the many options—relative, absolute, internal, external—that the Address property gives you.

• Use the Borders collection to simultaneously control the borders of each cell within a range. Use the BorderAround method to treat the range as a single unit.

• Use either the RGB function or the enumerated list of RGB constants to select a color. To create smooth gradations of the shades and tints of a color, take advantage of the TintAndShade property.