Working with the Range Object


The Range object represents a range of cells in a spreadsheet. A range can contain one cell, multiple contiguous cells, and even multiple discontiguous cells. You can select multiple discontiguous cells by holding down the Ctrl key as you select in Excel.

Getting a Range Object for a Particular Cell or Range of Cells

Excel provides a variety of ways to get a Range object. The Range object is the object you use when you want to work with a cell or range of cells in an Excel worksheet. Two ways to get a Range object were mentioned in the description of the Application object earlier in this chapter. Application.ActiveCell returns the top-left cell of the active selection in the active window. Application.Selection returns an Object that represents the active selection in the active window. If the active selection is a range of cells, you can cast Application.Selection to a Range object. If something else is selected in the active window, such as a shape or a chart, Application.Selection returns that selected object instead.

Worksheet also provides several ways to get a Range object. The Worksheet.Range property is the most common way to get a Range object from a Worksheet. This property takes a required Object parameter to which you can pass a String. It has a second optional parameter to which you can pass a second String. The strings you pass are in what is called A1-style reference format. The easiest way to explain the A1-style reference format is to give several examples.

The reference A1 specifies the cell at row 1, column A. The reference D22 specifies the cell at row 22, column D. The reference AA11 specifies the cell at row 11, column AA (column 27).

The reference $A$1 also refers to the cell at row 1, column A. If you use $ signs in an A1-style reference, they are ignored.

You can use the range operator (:) to specify a range of cells where the first A1-style reference is the top-left corner of the range, followed by a colon operator, followed by a second A1-style reference for the bottom-right corner of the range. The reference A1:B1 refers to the two cells at row 1, column A, and at row 1, column B. The reference A1:AA11 refers to all 297 cells in the block whose top-left corner is at row 1, column A and whose bottom-right corner is at row 11, column AA (column 27).

You can use the union operator (,) to specify multiple cells that could be discontiguous. The reference A1,C4, for example, specifies a range of two cells where the first cell is at row 1, column A, and the second cell is at row 4, column C. Users can select discontiguous ranges of cells by holding down the Ctrl key as they select various cells. The reference A1,C4,C8,C10 is another valid A1-style reference that specifies four different cells.

The intersection operator (a space) lets you specify the intersection of cells. The reference A1:A10 A5:A15, for example, resolves to the intersecting six cells starting at row 5, column A, and ending at row 10, column A. The reference A1:A10 A5:A15 A5 resolves to the single cell at row 5, column A.

You can also use any names you have defined in the worksheet in your A1-style reference. Suppose that you defined a named range called foo that refers to the cell A1. Some valid A1-style references using your name would include foo:A2, which refers to the cells at row 1, column A, and at row 2, column A. The reference foo,A5:A6 refers to the cells at row 1, column A; row 5, column A; and row 6, column A.

As mentioned earlier, the Range property takes a second optional parameter to which you can pass a second A1-style reference string. The first parameter and the second parameter are effectively combined using the range operator. So the range that Range returns when you call Range("A1", "A2") is equivalent to the range you get when you call Range("A1:A2").

A second way to get a Range object is to use the Worksheet.Cells property, which returns a Range for all the cells in the worksheet. Then you can use the same Range property on the returned Range object and pass A1-style references to select cells in the same way you do using Range from the Worksheet object. So Cells.Range("A1:A2") is equivalent to Range("A1:A2"). A more common use of the Cells property is to use it in conjunction with Range's Item property, which takes a row index and an optional column index. Using Item is a way to get to a particular cell without using the A1-style reference. So Cells.Item(1,1) is equivalent to Range("A1").

Another way to get a Range object is by using the Worksheet.Rows or Worksheet.Columns properties. These return a Range that acts differently from other Range objects. If you take the Range returned by Columns and display the count of cells in the range, for example, it returns 256the number of columns. But if you call the Select method on the returned Range, Excel selects all 16,772,216 cells in the worksheet. The easiest way to think of the ranges returned by Rows and Columns is that they behave similarly to how column and row headings behave in Excel.

Listing 5.27 shows several examples of using the Range property and the Cells, Rows, and Columns properties. We use the Value property of Range to set every cell in the range to the string value specified. Figure 5.7 shows the result of running the program in Listing 5.27.

Listing 5.27. A VSTO Customization That Gets Range Objects

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim r1 As Excel.Range = Me.Range("A1")     r1.Value = "r1"     Dim r2 As Excel.Range = Me.Range("B7:C9")     r2.Value = "r2"     Dim r3 As Excel.Range = Me.Range("C1,C3,C5")     r3.Value = "r3"     Dim r4 As Excel.Range = Me.Range("A1:A10 A5:A15")     r4.Value = "r4"     Dim r5 As Excel.Range = Me.Range("F4", "G8")     r5.Value = "r5"     Dim r6 As Excel.Range = Me.Rows.Item(12)     r6.Value = "r6"     Dim r7 As Excel.Range = Me.Rows.Item(5)     r7.Value = "r7"   End Sub 


Figure 5.7. Result of running Listing 5.27.


Working with Addresses

Given a Range object, you often need to determine what cells it refers to. The Address property returns an address for the range in either A1 style or R1C1 style. You have already learned about A1-style references. R1C1-style references support all the same operators as discussed with A1-style references (colon for range, comma for union, and space for intersection). R1C1-style references have row and column numbers prefaced by R and C, respectively. So cell A4 in R1C1 style would be R4C1. Figure 5.8 shows a range that consists of three areas that we consider in this section.

Figure 5.8. A range with three discontiguous areas.


The address for the range in Figure 5.8 is shown here in A1 style and in R1C1 style:

$A$15:$F$28,$H$3:$J$9,$L$1 R15C1:R28C6,R3C8:R9C10,R1C12 


Another option when getting an address is whether to get an external reference or a local reference. The addresses we have already shown for Figure 5.8 are local references. An external reference includes the name of the workbook and sheet where the range is. Here is the range in Figure 5.8 expressed as an external reference in A1 style and R1C1 style:

[Book1]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1 [Book1]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12 


For our example, the workbook we created the range in was not saved. When we save it as Book1.xls, the addresses look like this:

[Book1.xls]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1 [Book1.xls]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12 


Another option when getting an address is whether to use an absolute address or a relative one. The addresses we have already considered have been absolute. The same addresses in relative format (relative to cell A1) look like this:

R[14]C:R[27]C[5],R[2]C[7]:R[8]C[9],RC[11] A15:F28,H3:J9,L1 


For an R1C1-style address, you can also specify the cell you want your address to be relative to. If we get an R1C1-style for our range in Figure 5.4 relative to cell B2, we get the following result:

R[13]C[-1]:R[26]C[4],R[1]C[6]:R[7]C[8],R[-1]C[10] 


The Address property takes five optional parameters that control the way the reference is returned, as described in Table 5.17.

Table 5.17. Optional Parameters for Address

Parameter Name

Type

What It Does

RowAbsolute

Object

Pass true to return the row part of the address as an absolute reference ($A$1). If you pass False, the row reference will not be absolute ($A1). The default is true.

ColumnAbsolute

Object

Pass true to return the column part of the address as an absolute reference ($A$1). If you pass False, the column reference will not be absolute (A$1). The default is true.

ReferenceStyle

XlReference-Style

Pass xlA1 to return an A1-style reference. Pass xlR1C1 to return an R1C1-style reference.

External

Object

Pass true to return an external reference. The default is False.

RelativeTo

Object

Pass a Range object representing the cell that you want an R1C1-style reference to be relative to. Has no effect when used with A1-style references.


Listing 5.28 shows several examples of using Address with our example range.

Listing 5.28. A VSTO Customization That Uses Address

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range     range1 = Me.Range("$A$15:$F$28,$H$3:$J$9,$L$1")     Dim sb As System.Text.StringBuilder     sb = New System.Text.StringBuilder()     sb.AppendLine("A1Style Addresses:")     sb.AppendFormat("Default: {0}" & vbCrLf, _       range1.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1))     sb.AppendFormat("Relative rows: {0}" & vbCrLf, _       range1.Address(False, _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1))     sb.AppendFormat("Row & Column Relative: {0}" & vbCrLf, _       range1.Address(False, False, _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1))     sb.AppendFormat("External: {0}" & vbCrLf, _       range1.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1, _         External:=True))     sb.AppendLine()     sb.AppendLine("R1C1-Style Addresses:")     sb.AppendFormat("Default: {0}" & vbCrLf, _       range1.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlR1C1))     sb.AppendFormat( _       "Row & Column Relative to C5: {0}" & vbCrLf, _       range1.Address(False, False, _       ReferenceStyle:=Excel.XlReferenceStyle.xlR1C1, _       RelativeTo:=Me.Range("C5")))     sb.AppendFormat("External: {0}", _       range1.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlR1C1, _         External:=True))     MsgBox(sb.ToString())   End Sub End Class 


Creating New Ranges Using Operators

We have discussed several "operators" that can be used in address strings, including the union operator (a comma) and the intersection operator (a space). You can also apply these operators through the Application.Union and Application.Intersection methods.

It is also possible to take a Range and get a new Range that is offset from it by some number of rows and columns by using the Offset property. This method takes a row-and-column value to offset the given range by and returns the newly offset range. So calling Offset(5, 5) on the example range in Figure 5.8 returns a range with this A1-style address:

"$F$20:$K$33,$M$8:$O$14,$Q$6"

Listing 5.29 shows an example of using these operators. Note that Union and Intersection take a lot of optional parameters, allowing you to union or intersect more than just two ranges.

Listing 5.29. A VSTO Customization That Uses Union, Intersection, and Offset

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim app As Excel.Application = Me.Application     Dim range1 As Excel.Range = Me.Range("$A$15:$F$28")     Dim range2 As Excel.Range = Me.Range("$H$3:$J$9")     Dim range3 As Excel.Range = Me.Range("$L$1")     Dim range4 As Excel.Range = Me.Range("$A$11:$G$30")     Dim rangeUnion As Excel.Range     rangeUnion = app.Union(range1, range2, range3)     Dim rangeIntersection As Excel.Range     rangeIntersection = app.Intersect(range1, range4)     Dim rangeOffset As Excel.Range = rangeUnion.Offset(5, 5)     MsgBox(String.Format("Union: {0}", _       rangeUnion.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1)))     MsgBox(String.Format("Intersection: {0}", _       rangeIntersection.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1)))     MsgBox(String.Format("Offset: {0}", _       rangeOffset.Address( _         ReferenceStyle:=Excel.XlReferenceStyle.xlA1)))   End Sub End Class 


Working with Areas

When there are multiple discontiguous ranges of cells in one Range, each discontiguous range is called an area. If there are multiple discontiguous areas in the Range, use the Areas property to access the each area (as a Range) via the Areas collection. The Areas collection has an Areas.Count property and an Areas.Item property that takes an Integer parameter representing the 1-based index into the array. Listing 5.30 shows an example of iterating over our example range (which has three areas) and printing the address of each area.

Listing 5.30. A VSTO Customization That Works with Areas

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _      ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range     range1 = Me.Range("$A$15:$F$28,$H$3:$J$9,$L$1")     MsgBox(String.Format( _       "There are {0} areas", range1.Areas.Count))     Dim area As Excel.Range     For Each area In range1.Areas       MsgBox(String.Format("Area address is {0}", _         area.Address( _           ReferenceStyle:=Excel.XlReferenceStyle.xlA1)))     Next   End Sub End Class 


Working with Cells

The Count property returns the number of cells in a given Range. You can get to a specific single-cell Range within a Range by using the Item property. The Item property takes a required row index and an optional column index. The column index can be omitted when the range is a one-dimensional array of cells because it has cells from only one column or one row; in this case, the parameter called RowIndex really acts like an array index. If the Range has multiple areas, you must get the area you want to work with first; otherwise, Item returns cells out of only the first area in the Range.

Listing 5.31 shows an example of using Item.

Listing 5.31. A VSTO Customization That Uses Item

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range("$A$15:$F$28")     Dim rowCount As Integer = range1.Rows.Count     Dim columnCount As Integer = range1.Columns.Count     Dim i As Integer     Dim j As Integer     For i = 1 To rowCount       For j = 1 To columnCount         Dim cell As Excel.Range = range1.Item(i, j)         Dim address As String = cell.Address( _           ReferenceStyle:=Excel.XlReferenceStyle.xlA1)         cell.Value2 = String.Format("Item({0},{1})", i, j)       Next     Next   End Sub End Class 


Working with Rows and Columns

Given a Range object, you can determine the row and column numbers of the top-left corner of its first area using the Row and Column properties. The row and column numbers are returned as Integer values.

You can also determine the total number of rows and columns in the first area using the Rows and Columns properties. These properties return special ranges that you can think of as corresponding to the row or column headers associated with the range. When we get Rows.Count from our example range in Figure 5.8, it returns 14, and Columns.Count returns 6. This makes sense because the first area in our selection (A15:F28) spans 6 columns and 14 rows.

To get the row-and-column position of the bottom-right corner of the first area, you can use the rather awkward expressions shown in Listing 5.32. Listing 5.32 also illustrates the use of Item, which takes the row-and-column index (relative to the top of the given range) and returns the cell (as a Range) at that row-and-column index. When you get a Rows or a Columns range, these ranges are one-dimensional; hence, the parameter called RowIndex acts like an array index in this case.

Listing 5.32. A VSTO Customization That Gets Row and Column Positions

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range( _       "$A$15:$F$28,$H$3:$J$9,$L$1")     Dim area As Excel.Range = range1.Areas.Item(1)     Dim topLeftColumn As Integer = area.Column     Dim topLeftRow As Integer = area.Row     Dim bottomRightColumn As Integer = _       area.Columns.Item(area.Columns.Count).Column()     Dim bottomRightRow As Integer = _       area.Rows.Item(area.Rows.Count).Row()     MsgBox(String.Format( _       "Area Top Left Column {0} and Row {1}", _       topLeftColumn, topLeftRow))     MsgBox(String.Format( _       "Area Bottom Right Column {0} and Row {1}", _       bottomRightColumn, bottomRightRow))     MsgBox(String.Format( _       "Total Rows in Area = {0}", area.Rows.Count))     MsgBox(String.Format("Total Columns in Area = {0}", _       area.Columns.Count))   End Sub End Class 


Working with Regions

The CurrentRegion property returns a Range that is expanded to include all cells up to a blank row and blank column. This expanded Range is called a region. So, for example, you might have a Range that includes several cells in a table. To get a Range that encompasses the entire table (assuming that the table is bordered by blank rows and columns), you would use the CurrentRegion property on the smaller Range to return the entire table.

The End property works against the region associated with a Range. The End property takes a member of the XlDirection enumeration: xlDown, xlUp, xlToLeft, or xlToRight. This property, when passed xlUp, returns the top-most cell in the region in the same column as the top-left cell of the Range. When passed xlDown, it returns the bottom-most cell in the region in the same column as the top-left cell of the Range. When passed xlToLeft, it returns the left-most cell in the region in the same row as the top-left cell of the Range. And when passed xlToRight, it returns the rightmost cell in the region in the same row as the top-left cell of the Range.

Selecting a Range

You can make a range the current selection using the Select method on a Range. Remember that calling Select changes the user's current selection, which is not a very nice thing to do without good reason. In some cases, however, you want to draw the user's attention to something, and in those cases, selecting a Range is reasonable to do.

Editing the Values in a Range

Two methods are typically used to get and set the values in a range. The first way is to use the property Value. The second way is to use the property Value2. Value2 and Value differ in that the Value2 property returns cells that are currency or dates as a Double value. Also, Value takes an optional parameter of type XlRangeValueDataType. If you pass XlRangeValueData.xlRangeValueDefault, you will get back an Object representing the value of the cell for a single cell Range. For both Value2 and Value, if the Range contains multiple cells, you will get back an array of objects corresponding to the cells in the Range.

Listing 5.33 shows several examples of using Value2, including an example of passing an array of values to Value2. Setting the values of the cells in a Range all at once via an array is more efficient than making multiple calls to set each cell individually.

Listing 5.33. A VSTO Customization That Uses Value2

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range("$A$15:$F$28")     range1.Value2 = "Test"     Dim rowCount As Integer = range1.Rows.Count     Dim columnCount As Integer = range1.Columns.Count     Dim array(,) As Object     array = New Object(rowCount, columnCount) {}     Dim i As Integer     For i = 0 To rowCount - 1       Dim j As Integer       For j = 0 To columnCount - 1         array(i, j) = i * j       Next     Next     range1.Value2 = array   End Sub End Class 


Copying, Clearing, and Deleting Ranges

Excel provides a number of methods to copy, clear, and delete a Range. The Copy method takes a Destination parameter that you can pass the destination of the copied range. The Clear method clears the content and formatting of the cells in the range. ClearContents clears just the values of the cells in the range, and ClearFormats clears just the formatting. The Delete method deletes the range of cells and takes as a parameter the direction in which to shift cells to replace deleted cells. The direction is passed as a member of the XlDeleteShiftDirection enumeration: xlShiftToLeft or xlShiftUp.

Finding Text in a Range

The Find method allows you to find text in a Range and return the cell within the Range where the text is found. The Find method corresponds to the Find and Replace dialog box, shown in Figure 5.9. If you omit parameters when calling the Find method, it uses whatever settings were set by the user the last time the Find and Replace dialog box was used. Furthermore, when you specify the parameters, the settings you specified appear in the Find dialog box the next time the user opens it.

Figure 5.9. The Find and Replace dialog box.


The Find method takes a number of parameters, described in Table 5.18. Find returns a Range object if it succeeds and Nothing if it fails to find anything. You can find the next cell that matches your find criteria by using the FindNext method. FindNext takes an optional After parameter to which you need to pass the last found Range to ensure that you do not just keep finding the same cell over and over again. Listing 5.34 shows an example of using the Find and FindNext method where we search for any cells containing the character "2" and bold those cells.

Table 5.18. Parameters for the Find Method

Parameter Name

Type

What It Does

What

Object

Pass the data to search for as a required String.

After

Object

Pass a single cell after which you want the search to begin as a Range. The default is the top-left cell if this is omitted.

LookIn

Object

Pass the type to search.

LookAt

XlLookAt

Pass xlWhole to match the whole cell contents, xlPart to match parts of the cell contents.

SearchOrder

XlSearchOrder

Pass xlByRows to search by rows, xlByColumns to search by columns.

SearchDirection

XlSearchDirection

Pass xlNext to search forward, xlPrevious to search backward.

MatchCase

Object

Pass true to match case.

MatchByte

Object

Pass true to have double-byte characters match only double-byte characters.

SearchFormat

Object

Set to TRue if you want the search to respect the FindFormat options. You can change the FindFormat options by using the Application.FindFormat.


Listing 5.34. A VSTO Customization That Uses Find and FindNext

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range("$A$15:$F$28")     Dim rowCount As Integer = range1.Rows.Count     Dim columnCount As Integer = range1.Columns.Count     Dim array(,) As Object = New Object(rowCount, columnCount) {}     Dim i As Integer     Dim j As Integer     For i = 0 To rowCount - 1       For j = 0 To columnCount - 1         array(i, j) = i * j       Next     Next     range1.Value2 = array     Dim foundRange As Excel.Range = range1.Find("2", _       range1.Item(1, 1), LookAt:=Excel.XlLookAt.xlPart, _       SearchDirection:=Excel.XlSearchDirection.xlNext)     While foundRange IsNot Nothing       foundRange.Font.Bold = True       foundRange = range1.FindNext(foundRange)     End While   End Sub End Class 


Formatting a Range of Cells

Excel provides several methods and properties to format a range of cells. Among the most useful is the NumberFormat property, which you can set to format strings corresponding to the strings in the Custom category of the Format Cells dialog box. You can set NumberFormat to General to set no specific number format, for example. Setting NumberFormat to m/d/yyyy sets a date format, and 0% sets the format to a percentage format. When using NumberFormat, be sure to consider the locale issue discussed in the section "Special Excel Issues" later in this chapter if you are building a console application or an add-in, because reading and setting this string can cause problems when running in different locales. If you are using a VSTO Excel Workbook or Template project you do not have to worry about the locale issue.

The Font property returns a Font object that can be used to set the Font to various sizes and styles. Listing 5.34 showed an example of the Font object used to bold the font of a cell.

Excel also enables you to create styles associated with a Workbook and apply those styles to a Range. You can create styles using Workbook.Styles. Listing 5.35 shows an example of creating a style and applying it to a Range.

Listing 5.35. A VSTO Customization That Creates and Applies Styles

Public Class Sheet1   Private Sub Sheet1_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim range1 As Excel.Range = Me.Range("$A$15:$F$28")     range1.Value2 = "Hello"     Dim style As Excel.Style     style = Globals.ThisWorkbook.Styles.Add("My Style")     style.Font.Bold = True     style.Borders.LineStyle = Excel.XlLineStyle.xlDash     style.Borders.ColorIndex = 3     style.NumberFormat = "General"     range1.Style = "My Style"   End Sub End Class 





Visual Studio Tools for Office(c) Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
ISBN: 0321411757
EAN: 2147483647
Year: N/A
Pages: 221

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