In This Chapter
Finding out more about Range objects Understanding the various ways of referring to ranges Discovering some of the more useful Range object properties Uncovering some of the more useful Range object methods
In Chapter 4, I run the risk of overwhelming you with an introduction to Excel’s object model. In that chapter, I also run through the basics of properties and methods. Additionally, I dig a bit deeper and take a closer look at Range objects. Why do you need to know so much about Range objects? Because much of the programming work you do in Excel focuses on Range objects. You can thank me later.
A Quick Review
A Range object represents a range contained in a Worksheet object. A Range object can be as small as a single cell or as large as every cell on a worksheet (A1:IV65536 or 16,777,216 cells).
You can refer to a Range object like this:
Range(“A1:C5″)
Or if the range has a name (created using Insert Name Define), you can use an expression like this:
Unless you tell Excel otherwise, it assumes that you’re referring to a range on the active worksheet. If anything other than a worksheet is active (such as a chart sheet), the range reference fails, and your macro displays an error message.
As shown in the following example, you can refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook:
If you need to refer to a range in a different workbook (that is, any workbook other than the active workbook), you can use a statement like this:
A Range object can consist of one or more entire rows or columns. You can refer to an entire row (in this case, row 3) by using syntax like this:
You can refer to an entire column (column 4 in this example) like this:
Range(“D:D”)
To further confuse matters, you can even work with noncontiguous ranges. (In Excel, you select noncontiguous ranges by holding down the Ctrl key while selecting various ranges.) The following expression refers to a two-area noncontiguous range. Notice that a comma separates the two areas.
Finally, recall that Range objects (like all other objects) have properties (which you can examine and change) and methods (which perform actions on the object).
Other Ways to Refer to a Range
The more you work with VBA, the more you realize that it’s a fairly well-conceived language and is usually quite logical (despite what you may be thinking right now). Often, VBA provides multiple ways of performing an action. You can choose the most appropriate method for your problem. This section discusses some of the other ways to refer to a range.
This chapter barely scratches the surface for the Range object’s properties and methods. As you work with VBA you’ll probably need to access other properties and methods. The Help system is the best place to find out about them, but it’s also helpful to simply record your actions and examine the code Excel generates.
The Cells property
Rather than use the VBA Range keyword, you can refer to a range via the Cells property.
Notice that I wrote Cells property, not Cells object. Although Cells may seem like an object, it’s really not. Rather, Cells is a property that VBA evaluates; VBA then returns an object (more specifically, a Range object). If this seems strange, don’t worry. Even Microsoft appears to be confused about this issue. In some earlier versions of Excel, the Cells property was known as the Cells method. Regardless of what it is, just understand that Cells is a handy way to refer to a range.
The Cells property takes two arguments: row and column. For example, the following expression refers to cell C2 on Sheet2:
You can also use the Cells property to refer to a multicell range. The following example demonstrates the syntax you use:
This expression refers to a 100-cell range that extends from cell A1 (row 1, column 1) to cell J10 (row 10, column 10).
The following statements both produce the same result; they enter a value of 99 into a 10-by-10 range of cells. More specifically, these statements set the Value property of the Range object:
The advantage of using the Cells method to refer to ranges becomes apparent when you use variables rather than actual numbers as the Cells arguments. And things really start to click when you understand looping, which I cover in Chapter 10.
The Offset property
The Offset method can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range. The following example refers to cell A1:
And, as you may expect, you can use 0 as one or both of the arguments for Offset. The following expression refers to cell A1:
The Offset method is most useful when you use variables instead of actual values for the arguments. In Chapter 10, I present some examples that demonstrate this.
Referring to entire columns and rows
If you need to refer to a range that consists of one or more entire columns, you can use an expression like the following:
And to refer to one or more complete rows, use an expression like this:
The Offset property provides another handy means for referring to ranges. This property, which operates on a Range object and returns another Range object, lets you refer to a cell that is a particular number of rows and columns away from another cell.
Like the Cells property, the Offset property takes two arguments. The first argument represents the number of rows to offset; the second represents the number of columns to offset.
The following expression refers to a cell one row below cell A1 and two columns to the right of cell A1. In other words, this refers to the cell commonly known as C2:
Some Useful Range Object Properties
A Range object has dozens of properties. You can write Excel programs nonstop for the next 40 years and never use them all. In this section, I briefly describe some of the more commonly used Range properties. For complete details, consult the Help system in the VBE.
Some Range properties are read-only properties, which means you can’t change them. For example, every Range object has an Address property (which holds the range’s address). You can access this read-only property, but you can’t change it.
The examples that follow are typically statements rather than complete procedures. If you’d like to try any of these (which you should), create a Sub procedure to do so. Also, many of these statements work properly only if a worksheet is the active sheet.
The Value property
The Value property represents the value contained in a cell. It’s a read-write property, so your VBA code can either read or change the value.
The following statement displays a message box that shows the value in cell
A1 on Sheet1:
It stands to reason that you would read the Value property only for a single-cell Range object. For example, the following statement generates an error:
You can, however, change the Value property for a range of any size. The following statement enters the number 123 into each cell in a range:
Value is the default property for a Range object. In other words, if you omit a property for a Range, Excel uses its Value property. The following statements both enter a value of 75 into cell A1 on Sheet1:
The Text property
But the next statement displays a message box containing 12.3:
The Count property
The Count property returns the number of cells in a Range (all cells, not just the nonblank cells). It’s a read-only property. The following statement accesses a range’s Count property and displays the result (9) in a message box:
The Column and Row properties
The Column property returns the column number of a single-cell range; the Row property returns the row number of a single-cell range. Both are readonly properties. For example, the following statement displays 6 because the cell is in the sixth column:
The next expression displays 3 because cell F3 is in the third row:
If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row property returns the row number of the first row in the range.
Don’t confuse the Column and Row properties with the Columns and Rows properties (discussed earlier in this chapter). The Column and Row properties return a single value. Columns and Rows properties return a Range object.
The Text property returns a string that represents the text as displayed in a cell — the formatted value. The Text property is read-only. For example, suppose that cell A1 contains the value 12.3 and is formatted to display two decimals and a dollar sign ($12.30). The following statement displays a message box containing $12.30:
The Address property
Figure 8-1:
This message box displays the Address property of a 1-by-5 range.
The Has Formula property
The Has Formula property (which is read-only) returns True if the single-cell Range contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don’t have a formula. The property returns a Null if there is a mixture of formulas and nonformulas.
Be careful with the type of variables you use to maintain the results returned by the Has Formula property. When working with any property that returns a Null, it is easy to generate errors by using the wrong data types.
For example, assume that cell A1 contains a value and cell A2 contains a formula. The following statements generate an error because the range doesn’t consist of all formulas or all nonformulas:
To fix this type of situation, the best thing to do is simply make sure the Formula Test variable is declared as a variant rather than as a Boolean. The following example demonstrates.
Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column letter and before the row number). The following statement displays the message box shown in Fig-ure8-1.
The Font property
As I note earlier in this chapter (see “The Cells property”), a property can return an object. Here’s another example: A Range object’s Font property returns a Font object.
A Font object, as you may expect, has many accessible properties. To change some aspect of a range’s font, you must first access the range’s Font object and then manipulate the properties of that object. This might be confusing at first but it eventually makes sense.
The following expression returns a Font object for a range:
The following statement sets to True the Bold property of the Font object contained in the Range object. In plain English, this makes the cell display in boldface:
To see other examples of manipulating font objects, record your actions while you modify some of a range’s font attributes. See Chapter 6 for more information about recording macros.
The Interior property
Here’s another example of a property that returns an object. A Range object’s Interior property returns an Interior object (strange name, but that’s what it’s called). This type of object referencing works the same way as the Font property (which I describe in the preceding section).
For example, the following statement sets to 3 the Color Index property of the Interior object contained in the Range object:
In other words, this statement changes the cell’s background to red.
The Color Index values correspond to the color palette Excel currently uses. The Color Index value can be any value from 1 to 56. The easiest way to determine the Color Index for a particular color is to record your actions while changing a cell’s color. If you need to use standard colors, use the Color property (instead of Color Index) along with a built-in constant: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, or vbWhite. For example, the following statement makes cell A1 yellow:
The Formula property
The Formula property represents the formula in a cell. This is a read-write property, so you can access it to insert a formula into a cell. For example, the following statement enters a SUM formula into cell A13:
Notice that the formula is a text string and is enclosed in quotation marks. If you are using the Formula property to determine the formula already in a cell and the cell doesn’t have a formula, the Formula property returns the cell’s Value property.
The Number Format property
The Number Format property represents the number format (expressed as a text string) of the Range object. This is a read-write property, so your VBA code can change the number format. The following statement changes the number format of column A to percent with two decimal places:
Follow these steps to see a list of other number formats:
1. Activate a worksheet.
2. Access the Format Cells dialog box by pressing Ctrl+1.
3. Click the Number tab.
4. Select the Custom category to view some additional number format strings.
Some Useful Range Object Methods
As you know, a VBA method performs an action. A Range object has dozens of methods but, again, you won’t need most of these. In this section, I point out some of the more commonly used Range object methods.
The Select method
Use the Select method to select a range of cells. The following statement selects a range on the active worksheet:
Before selecting a range, make sure you’ve activated the range’s worksheet; otherwise, you get an error or the wrong range is selected. For example, if Sheet1 contains the range you want to select, use the following statements to select the range:
Contrary to what you may expect, the following statement generates an error. In other words, you must use two statements instead of just one: one to activate the sheet and another to select the range.
The Copy and Paste methods
You can perform copy and paste operations in VBA by using the Copy and Paste methods. The Copy method is applicable to the Range object, but the Paste method is applicable to the Worksheet object. This short macro copies range A1:A12 and pastes it to the range beginning at cell C1:
Sub CopyRange()
Range(“A1:A12″).Select Selection.Copy Range(“C1″).Select ActiveSheet.Paste
End Sub
Notice that in the preceding example, which the macro recorder generated, the ActiveSheet object is used with the Paste method. This is a special version of the Worksheet object that refers to the presently selected worksheet. Also notice that the macro selects the range before copying it. However, you don’t have to select a range before doing something with it. In fact, the following procedure accomplishes the same task as the preceding example by using a single statement:
This procedure takes advantage of the fact that the Copy method can use an argument that corresponds to the destination range for the copy operation.
The Clear method
The Clear method deletes the contents of a range and all the cell formatting. For example, if you want to zap everything in column D, the following statement does the trick:
You should be aware of two related methods. The Clear Contents method deletes the contents of the range but leaves the formatting intact. The Clear Formats method deletes the formatting in the range but not the cell contents.
The Delete method
Clearing a range differs from deleting a range. When you delete a range, Excel shifts the remaining cells around to fill up the range you deleted.
The following example uses the Delete method to delete row 6:
When you delete a range that’s not a complete row or column, Excel needs to know how to shift the cells. (To see how this works, experiment with the Excel Edit Delete command.)
The following statement deletes a range and then fills the resulting gap by shifting the other cells to the left:
The Delete method uses an argument that indicates how Excel should shift the remaining cells. In this case, I use a built-in constant (xlToLeft) for the argument. I could also use xlUp, another named constant.
