EXCELlent Office Adventures, Part 2
February 1, 2001
In last month's column, I provided an overview of what you need to know to work with the Excel object model. This month I will build on that discussion with information on working with the Range object and some of its properties and methods.
Understanding the Range Object
Excel's Range object is one of the most powerful, dynamic, and often-used objects in the Excel object model. Once you develop a full understanding of the Range object and how to use it effectively in Visual Basic for Applications (VBA) procedures, you will be well on your way to programmatically harnessing the power of Excel.
The Excel Range object is unique among objects. In most cases, an object is a thing with some clearly identifiable corollary in the Excel user interface. For example, a Workbook object is recognizable as an .xls file. In a workbook, the collection of Worksheet objects is represented in the user interface by separate tabbed sheets. But the Range object is different. A range can be a different thing in different circumstances. A Range object can be a single cell or a collection of cells. It can be a single object or a collection of objects. It can be a row or column, or it can represent a three-dimensional collection of cells that span multiple worksheets. In addition, unlike other objects that exist as objects and as members of a collection of objects, there is no Ranges collection that contains all Range objects in a workbook or worksheet. It is probably easiest to think of the Range object as your handle to the thing you want to work with.
Because the Range object is such a fundamental entity within Excel, you will find that many properties and methods return a Range object that you can use to work with the data in your custom solution. The following sections discuss some basic aspects of Range objects and many of the ways you can return a Range object from a built-in property or method.
Working with the Range property
You will use the Range property to return a Range object in many different circumstances. The Application object, the Worksheet object, and the Range object all have a Range property. The Application object's Range property returns the same Range object as that returned by the Worksheet object. In other words, the Application object's Range property returns a reference to the specified cell or cells on the active worksheet. The Range property of the Range object has a subtle difference that it is important to understand. Consider the following example:
Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Application.Range("B5") Set rng2 = Worksheets("Sheet1").Range("B5") Set rng3 = rng2.Range("B5")
These three Range objects do not all return a reference to the same cell. In this example, rng1 and rng2 both return a reference to cell B5. But rng3 returns a reference to cell C9. This difference occurs because the Range object's Range property returns a reference relative to the specified cell. In this case, the specified cell is B5. Therefore, the B means that the reference will be one column to the right of B5, and the 5 means the reference will be the fifth row below the row specified by B5, including the current (fifth) row. In other words, the Range object's Range property returns a reference to a cell that is n columns to the right and y rows down from the specified cell).
Typically, you will use the Range property to return a Range object, then use the properties and methods of that Range object to work with the data in a cell or group of cells. The following table contains several examples that illustrate how you might work with the Range property:
|Description||Range Property Example|
|Set the value of cell A1 on Sheet1 to 100||Worksheets("Sheet1").Range("A1").Value = 100|
|Set the value for a group of cells on the active worksheet||Range("B2:B14").Value = 10000|
|Set the formula for cell B15 on the active worksheet||Range("B15").Formula = "=Sum(B2:B14)"|
|Set the font to bold||Range("B15").Font.Bold = True|
|Set the font color to green||Range("B15").Font.Color = RGB(0, 255, 0)|
|Set an object variable to refer to a single cell||Set rngCurrent = Range("A1")|
|Set an object variable to refer to a group of cells||Set rngCurrent = Range("A1:L1")|
|Format all the cells in a named range||Range("YTDSalesTotals").Font.Bold = True|
|Set an object variable to a named range||Set rngCurrent = Range("NovemberReturns")|
|Set an object variable representing all the used cells on the Employees worksheet||Set rngCurrent = Worksheets("Employees").UsedRange|
|Set an object variable representing the group of related cells that surround the active cell||Set rngCurrent = ActiveCell.CurrentRegion|
|Set an object variable representing the first three columns in the active worksheet||Set rngCurrent = Range("A:C")|
|Set an object variable representing rows 3, 5, 7, and 9 of the active worksheet||Set rngCurrent = Range("3:3, 5:5, 7:7, 9:9")|
|Set an object variable representing multiple noncontiguous groups of cells on the active sheet||Set rngCurrent = Range("A1:C4, D6:G12, I2:L7")|
|Remove the contents for all cells within a specified group of cells (B5:B10) while leaving the formatting intact||Range("B5", "B10").ClearContents|
As you can see from the examples, the Cell argument of the Range property can be either an A1-style string reference or a string that represents a named range within the current workbook.
You can also use the Range property to return Range objects in arguments for other methods in the Excel object model. When you use the Range property in this way, make sure you fully qualify the Worksheet object to which the Range property applies. Failing to use fully qualified references to the Range property in arguments for Excel methods is one of the most common sources of error in range-related code.
Working with the active cell or the current selection
The ActiveCell property returns a Range object that represents the currently active cell. When a single cell is selected, the ActiveCell property returns a Range object that represents that single cell. When multiple cells are selected, the ActiveCell property represents the single active cell within the current selection. When a cell or group of cells is selected, the Selection property returns a Range object representing all the cells within the current selection.
To understand how the ActiveCell and Selection properties relate, consider a case in which a user selects cells A1 through F1 by clicking cell A1 and dragging until the selection extends over cell F1. In this case, the ActiveCell property returns a Range object that represents cell A1. The Selection property returns a Range object that represents cells A1 through F1.
When you work with Excel's user interface, you typically select a cell or group of cells and then perform an action on the selected cell or cells, such as entering a value for a single cell or formatting a group of cells. When you use VBA to work with cells, you don't need to make a selection before performing an action on a cell or group of cells. Instead, you need only return a Range object that represents the cell or cells you want to work with. For example, to enter January as the value for cell A1 by using the user interface, you would select cell A1 and type January. The following sample performs the same action in VBA:
ActiveSheet.Range("A1").Value = "January"
Using VBA to work with a Range object in this manner does not change the selected cells on the current worksheet. However, you can make your VBA code act upon cells in the same way as a user working through the user interface by using the Range object's Select method to select a cell or range of cells, then using the Range object's Activate method to activate a cell within the current selection. For example, the following code selects cells A1 through A6, then makes cell A3 the active cell:
With ActiveSheet .Range("A1:A6").Select .Range("A3").Activate End With
When you use the Select method to select multiple cells, the first cell referenced will be the active cell. For example, in the preceding sample, after the Select method is executed, the ActiveCell property returns a reference to cell A1, even though cells A1 through A6 are selected. After the Activate method is executed in the next line of code, the ActiveCell property returns a reference to cell A3 while cells A1 through A6 remain selected. The next example illustrates how to return a Range object by using the ActiveCell property or the Selection property:
Dim rngActiveRange As Excel.Range ' Range object returned from the Selection property. Set rngActiveRange = Selection Call PrintRangeInfo(rngActiveRange) ' Range object returned from the ActiveCell property. Set rngActiveRange = ActiveCell Call PrintRangeInfo(rngActiveRange)
The PrintRangeInfo custom procedure called in the preceding example prints information about the cell or cells contained in the Range object passed in the argument to the procedure.
Sub PrintRangeInfo(rngCurrent As Excel.Range) Dim rngTemp As Excel.Range Dim strValue As String Dim strRangeName As String Dim strAddress As String Dim strFormula As String On Error Resume Next strRangeName = rngCurrent.Name.Name _ & " (" & rngCurrent.Name.RefersTo & ")" strAddress = rngCurrent.Address For Each rngTemp In rngCurrent.Cells If IsEmpty(rngTemp) Then strValue = strValue & "Cell(" & rngTemp.Address _ & ") Is empty." & vbCrLf Else strValue = strValue & "Cell(" & rngTemp.Address _ & ") = " & rngTemp.Value _ & " Formula " & rngTemp.Formula & vbCrLf End If Next rngTemp Debug.Print IIf(Len(strRangeName) > 0, "Range Name = " _ & strRangeName, "Range not named") & vbCrLf & "Address = " _ & strAddress & vbCrLf & strValue Debug.Print "**********************************" Debug.Print If Err > 0 Then Err = 0 End Sub
Working with cells and groups of cells
You will often have to write code to work against a range of cells, but at the time you write the code you will not have information about the range. For example, you may not know the size or location of a range or the location of a cell in relation to another cell. You can use the CurrentRegion and UsedRange properties to work with a range of cells whose size you have no control over. You can use the Offset property to work with cells in relation to other cells where the cell location is unknown.
The Range object's CurrentRegion property returns a Range object that represents a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet. For example, if you had a table of data within cells D3 through E12 and the focus was in cell D3, then the CurrentRegion property would return a Range object that represents cells D3 through E12.
The CurrentRegion property can return many ranges on a single worksheet. This property is useful for operations where you need to know the dimensions of a group of related cells, but all you know for sure is the location of a cell or cells within the group. For example, if the active cell were inside a table of cells, you could use the following line of code to apply formatting to the entire table:
You could also use the CurrentRegion property to return a collection of cells. For example:
Dim rngCurrentCell As Excel.Range For Each rngCurrentCell In ActiveCell.CurrentRegion.Cells ' Work with individual cells here. Next rngCurrentCell
Every Worksheet object has a UsedRange property that returns a Range object representing the area of a worksheet that is being used. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right cells that contain data on a worksheet, and includes all cells in between regardless of whether they contain data. For example, imagine a worksheet with entries in only two cells: A1 and G55. The worksheet's UsedRange property would return a Range object that contains all the cells from A1 to G55.
You might use the UsedRange property together with the SpecialCells method to return a Range object that represents all cells of a specified type on a worksheet. For example, the following code returns a Range object that includes all the cells in the active worksheet that contain a formula:
Dim rngFormulas As Excel.Range Set rngFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
You can use the Cells property to loop through a range of cells on a worksheet or to refer to a range by using numeric row and column values. The Cells property returns a Range object that represents all the cells, or a specified cell, in a worksheet. To work with a single cell, you use the Item property of the Range object returned by the Cells property, and specify the index of a specific cell you want to work with. The Item property accepts arguments that specify the row or the row and column index for a cell.
Since the Item property is the default property of the Range object, it is not necessary to explicitly reference it. For example, the following Set statements both return a reference to cell B5 on Sheet1:
Dim rng1 As Excel.Range Dim rng2 As Excel.Range Set rng1 = Worksheet("Sheet1").Cells.Item(5, 2) Set rng2 = Worksheet("Sheet1").Cells(5, 2)
The row and column index arguments of the Item property return references to individual cells, beginning with the first cell in the specified range. For example, the following message box displays G11 because that is the first cell in the specified Range object:
The following procedure illustrates how you would use the Cells property to loop through all the cells in a specified range. The OutOfBounds procedure looks for values that are greater than or less than a specified range of values, and changes the font color for each cell with such a value:
Function OutOfBounds(rngToCheck As Excel.Range, _ lngLowValue As Long, _ lngHighValue As Long, _ Optional lngHighlightColor As Long = 255) As Boolean ' This procedure illustrates how to use the Cells property ' to iterate through a collection of cells in a range. ' For each cell in the rngTocheck range, if the value of the ' cell is numeric and it falls outside the range of values ' specified by lngLowValue to lngHighValue, the cell font ' is changed to the value of lngHighlightColor ' (default is red). Dim rngTemp As Excel.Range Dim lngRowCounter As Long Dim lngColCounter As Long ' Validate bounds parameters. If lngLowValue > lngHighValue Then Err.Raise vbObjectError + 512 + 1, _ "OutOfBounds Procedure", _ "Invalid bounds parameters submitted: " _ & "Low value must be lower than high value." Exit Function End If ' Iterate through cells and determine if values ' are outside bounds parameters. If so, highlight value. For lngRowCounter = 1 To rngToCheck.Rows.Count For lngColCounter = 1 To rngToCheck.Columns.Count Set rngTemp = rngToCheck.Cells(lngRowCounter, _ lngColCounter) If IsNumeric(rngTemp.Value) Then If rngTemp.Value < lngLowValue Or _ rngTemp.Value > lngHighValue Then rngTemp.Font.Color = lngHighlightColor OutOfBounds = True End If End If Next lngColCounter Next lngRowCounter End Function
You can also use a For Each…Next statement to loop through the range returned by the Cells property. The following code could be used in the OutOfBounds procedure to loop through cells in a range:
' Iterate through cells and determine if values ' are outside bounds parameters. If so, highlight value. For Each rngTemp in rngToCheck.Cells If IsNumeric(rngTemp.Value) Then If rngTemp.Value < lngLowValue Or _ rngTemp.Value > lngHighValue Then rngTemp.Font.Color = lngHighlightColor OutOfBounds = True End If End If Next rngTemp
You can use the Offset property to return a Range object with the same dimensions as a specified Range object, but offset from the specified range. For example, you could use the Offset property to create a new Range object adjacent to the active cell to contain calculated values based on the active cell.
The Offset property is useful when you do not know the specific address of the cells you will need to work with, but you do know where the cell is located in relation to other cells you need to work with. For example, you may have a command bar button in your custom solution that fills the active cell with the average of the values in the two cells immediately to the left of the active cell:
ActiveCell.Value = (ActiveCell.Offset(0, -2) + ActiveCell.Offset(0, -1)/2)
Where to Get More Info
For additional information about working with Excel, check out the following resources:
- For an overview of working with the Excel object, see last month's column.
- For more information on the Excel object model see http://msdn.microsoft.com/library/default.asp?URL=/library/officedev/odeomg/deovrmicrosoftexcel2000.htm.
- For more information on working with Office application object models, see Understanding Office Objects and Object Models.
- As always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.