Understanding the Excel Object Model from a. Net developer's Perspective

Tip   When working with a cell or group of cells, use a range that describes the cells you want to work with, rather than modifying the selection, if at all possible. If your intent is to modify the user's selection, then use the Range.Select method.

Referring to a Range in Code

The Range class is so wildly flexible that you'll find yourself with far too many options when working with ranges, programmatically. Sometimes a Range object is a single object and other times it represents a collection of objects. It has Item and Count members, even though a Range object often refers to a single object, making it sometimes tricky to figure out exactly how to use a Range object.

Tip   Several of the following examples retrieve the Address property of a range. This property returns a string containing a representation of the range's coordinates in one of several formats including "$A$1" (the cell at location A1), "$1" (the first row in the worksheet), and "$A$1:$C$5" (the range consisting of all cells within the rectangle bounded by A1 and C5). The "$" indicates an absolute, as opposed to relative, coordinate. Using the Address property is the simplest way to find out the exact location of a range you've retrieved. For more information on the various ways to refer to a range, see the Excel online help.

In its simplest sense, you can write code as described in the following list to cause a Range object to refer to a single cell or group of cells. Each example assumes the following setup code:

' Visual Basic

Dim ws As Excel.Worksheet = _

DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet)

Dim rng, rng1, rng2 As Excel.Range

// C#

Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.Worksheets[1];

Excel.Range rng, rng1, rng2;

You can use any of the following techniques to refer to a specific range (there are several other ways to obtain a reference to a Range object, as well):

• 
  Refer to the ActiveCell property of the Application object:
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.ActiveCell
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.ActiveCell;
  
• 
  Use the Range property of an object, specifying a range. Because C# doesn't support parameterized non-indexed properties, you must call the get_Range method, which requires two parameters, instead:
  
• 
  ' Visual Basic
  
• 
  rng = ws.Range("A1")
  
• 
  rng = ws.Range("A1:B12")
  
• 
  
  
• 
  // C#
  
• 
  rng = ws.get_Range("A1", Type.Missing);
  
• 
  rng = ws.get_Range("A1:B12", Type.Missing);
  
• 
  Use the Cells property of a worksheet, specifying a single row and column value:
  
• 
  ' Visual Basic
  
• 
  ' The Cells collection returns an Object--
  
• 
  ' Convert it to a Range object explicitly:
  
• 
  rng = DirectCast(ws.Cells(1, 1), Excel.Range)
  
• 
  
  
• 
  // C#
  
• 
  rng = (Excel.Range)ws.Cells[1, 1];
  
• 
  Specify the "corners" of a range; you can also refer directly to the Cells, Rows, or Columns property of the range; in each case, the property returns a range:
  
• 
  ' Visual Basic
  
• 
  rng = ws.Range("A1", "C5")
  
• 
  rng = ws.Range("A1", "C5").Cells
  
• 
  rng = ws.Range("A1", "C5").Rows
  
• 
  rng = ws.Range("A1", "C5").Columns
  
• 
  
  
• 
  // C#
  
• 
  rng = ws.get_Range("A1", "C5");
  
• 
  rng = ws.get_Range("A1", "C5").Cells;
  
• 
  rng = ws.get_Range("A1", "C5").Rows;
  
• 
  rng = ws.get_Range("A1", "C5").Columns;
  
• 
  Refer to a named range. You've seen this technique used throughout this article. Note that because the C# get_Range method requires two parameters and the range name only requires one of the parameters, you must specify Type.Missing for the second parameter:
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.Range("SomeRangeName")
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.Range("SomeRangeName", Type.Missing);
  
• 
  Refer to a particular row or column or range of rows and columns; note that the Rows and Columns properties each return an Object, requiring conversion if you have Option Strict set to On:
  
• 
  ' Visual Basic
  
• 
  rng = DirectCast(ws.Rows(1), Excel.Range)
  
• 
  rng = DirectCast(ws.Rows("1:3"), Excel.Range)
  
• 
  rng = DirectCast(ws.Columns(3), Excel.Range)
  
• 
  
  
• 
  // C#
  
• 
  rng = (Excel.Range)ws.Rows[1, Type.Missing];
  
• 
  rng = (Excel.Range)ws.Rows["1:3", Type.Missing];
  
• 
  rng = (Excel.Range)ws.Columns[3, Type.Missing];
  

Warning   The IntelliSense for the Columns property is misleading—it indicates that you must specify row then column values. In reality, the values are reversed for the Columns property. For both the Rows and Columns properties, the second parameter isn't used.

• 
  Use the Application object's Selection property to return a range corresponding to the selected cell(s); given the situation shown in Figure 20, the following fragment returns the string "$C$3" (using the "$" to represent absolute coordinates):
  
• 
  ' Visual Basic
  
• 
  Debug.WriteLine( _
  
• 
  DirectCast(ThisApplication.Selection, Excel.Range).Address)
  
• 
  
  
• 
  // C#
  
• 
  System.Diagnostics.Debug.WriteLine(
  
• 
  ((Excel.Range)ThisApplication.Selection).
  
• 
  get_Address(Type.Missing, Type.Missing,
  
• 
  Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
  

Tip   The Address property is another of those parameterized properties that C# can't handle directly. Call the get_Address method to retrieve the address corresponding to a Range object. All the parameters of the Address property are optional, but the get_Address method expects to retrieve five parameters—you're only likely to care about the third parameter, which allows you to specify the address formatting.

• 
  Create a range that contains the union of two other ranges (specify two ranges within the quotes, separated with a comma):
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.Range("A1:D4, F2:G5")
  
• 
  ' You can also use the Application object's Union
  
• 
  ' method to retrieve the intersection of two ranges:
  
• 
  rng1 = ThisApplication.Range("A1:D4")
  
• 
  rng2 = ThisApplication.Range("F2:G5")
  
• 
  rng = ThisApplication.Union(rng1, rng2)
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.get_Range("A1:D4, F2:G5", Type.Missing);
  
• 
  // You can also use the Application object's Union
  
• 
  // method to retrieve the intersection of two ranges, but this
  
• 
  // is far more effort in C#:
  
• 
  rng1 = ThisApplication.get_Range("A1", "D4");
  
• 
  rng2 = ThisApplication.get_Range("F2", "G5");
  
• 
  // Note that the Union method requires you to supply thirty
  
• 
  // parameters:
  
• 
  rng = ThisApplication.Union(rng1, rng2,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  
• 
  Create a range which refers to the intersection of two other ranges (specify two ranges within the quotes, with no separator):
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.Range("A1:D16 B2:F14")
  
• 
  ' You can also use the Application object's Intersect
  
• 
  ' method to retrieve the intersection of two ranges:
  
• 
  rng1 = ThisApplication.Range("A1:D16")
  
• 
  rng2 = ThisApplication.Range("B2:F14")
  
• 
  rng = ThisApplication.Intersect(rng1, rng2)
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.get_Range("A1:D16 B2:F14", Type.Missing);
  
• 
  // You can also use the Application object's Intersect
  
• 
  // method to retrieve the intersection of two ranges. Note
  
• 
  // that the Intersect method requires you to pass 30 parameters:
  
• 
  rng1 = ThisApplication.get_Range("A1", "D16");
  
• 
  rng2 = ThisApplication.get_Range("B2", "F14");
  
• 
  rng = ThisApplication.Intersect(rng1, rng2,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  
• 
  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  
• 
  Use the Offset property of a range to retrieve a range relative to the original range; the following example adds content to the area under the cell at row 1, column 1:
  
• 
  ' Visual Basic
  
• 
  rng = DirectCast(ws.Cells(1, 1), Excel.Range)
  
• 
  
  
• 
  Dim i As Integer
  
• 
  For i = 1 To 5
  
• 
  rng.Offset(i, 0).Value = i.ToString
  
• 
  Next
  
• 
  
  
• 
  // C#
  
• 
  rng = (Excel.Range) ws.Cells[1, 1];
  
• 
  for (int i = 1; i <= 5; i++)
  
• 
  {
  
• 
  rng.get_Offset(i, 0).Value2 = i.ToString();
  
• 
  }
  

Tip   The Range.Offset property is a parameterized property, so C# code can't retrieve its value directly. C# developers must call the get_Offset method instead.

• 
  Use the CurrentRegion property of a range to retrieve a range that represents the current region, the region bounded by the nearest empty row and column; for example, in Figure 20, the following expression sets the font of the current region to be bold:
  
• 
  ' Visual Basic
  
• 
  ThisApplication.Range("C3").CurrentRegion.Font.Bold = True
  
• 
  
  
• 
  // C#
  
• 
  ThisApplication.get_Range("C3", Type.Missing).
  
• 
  CurrentRegion.Font.Bold = True;
  

Figure 20. Requesting the CurrentRegion property of cell C3 returns the range A1:E5.

• 
  Use the Areas property of a range to retrieve a collection of ranges, each corresponding to one region of the range's contents. For example, the following fragment displays the addresses for both areas within the named range Test shown in Figure 21, "$B$1:$E$5" and "$C$7:$G$11" (using the "$" to represent absolute coordinates):
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.Range("Test")
  
• 
  Dim i As Integer
  
• 
  For i = 1 To rng.Areas.Count
  
• 
  Debug.WriteLine(rng.Areas(i).Address)
  
• 
  Next
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.get_Range("Test", Type.Missing);
  
• 
  for (int i = 1; i <= rng.Areas.Count; i++)
  
• 
  {
  
• 
  System.Diagnostics.Debug.WriteLine(
  
• 
  rng.Areas[i].get_Address(Type.Missing, Type.Missing,
  
• 
  Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
  
• 
  }
  

Figure 21. Ranges can contain areas that are not contiguous, and you can use the Areas property to retrieve each individually.

• 
  Use the End property, along with a value from the XlDirection enumeration (xlUp, xlToRight, xlToLeft, xlDown), to retrieve a range that represents the cell at the end of the region, as if you'd pressed the key described by the enumerated value; using the selected cell shown in Figure 22, the following fragment ends up with four ranges defined as shown in the code's comments:
  
• 
  ' Visual Basic
  
• 
  Dim rngLeft, rngRight, rngUp, rngDown as Excel.Range
  
• 
  rng = DirectCast(ThisApplication.Selection, Excel.Range)
  
• 
  ' E3
  
• 
  rngRight = rng.End(Excel.XlDirection.xlToRight)
  
• 
  ' A3
  
• 
  rngLeft = rng.End(Excel.XlDirection.xlToLeft)
  
• 
  ' C1
  
• 
  rngUp = rng.End(Excel.XlDirection.xlUp)
  
• 
  ' C5
  
• 
  rngDown = rng.End(Excel.XlDirection.xlDown)
  
• 
  
  
• 
  // C#
  
• 
  Excel.Range rngLeft, rngRight, rngUp, rngDown;
  
• 
  rng = (Excel.Range) ThisApplication.Selection;
  
• 
  // Note that the Range.End property is parameterized, so
  
• 
  // C# developers cannot retrieve it. You must call the
  
• 
  // get_End method, instead:
  
• 
  // E3
  
• 
  rngRight = rng.get_End(Excel.XlDirection.xlToRight);
  
• 
  // A3
  
• 
  rngLeft = rng.get_End(Excel.XlDirection.xlToLeft);
  
• 
  // C1
  
• 
  rngUp = rng.get_End(Excel.XlDirection.xlUp);
  
• 
  // C5
  
• 
  rngDown = rng.get_Down(Excel.XlDirection.xlDown);
  

Figure 22. Use the End property to return ranges corresponding to a range.

• 
  Use the EntireRow or EntireColumn properties to refer to the row(s) or column(s) containing the specified range. For example, the following fragment sets the font to bold for rows 7 through 11, using the example shown in Figure 21:
  
• 
  ' Visual Basic
  
• 
  rng = ThisApplication.Range("Test")
  
• 
  rng.Areas(2).EntireRow.Font.Bold = True
  
• 
  
  
• 
  // C#
  
• 
  rng = ThisApplication.get_Range("Test", Type.Missing);
  
• 
  rng.Areas[2].EntireRow.Font.Bold = true;
  

Using the Technology

Developers commonly request the capability of changing the font of the entire row containing the selected cell so that the text is bold. This feature isn't built into Excel, but isn't terribly difficult to add. The Range Class sheet in the sample workbook contains a range that's handled specially: as you select an item, its row becomes bold. Figure 23 shows this behavior in action.

Figure 23. Select an item to make its entire row bold.

The sample workbook includes the following procedure to take care of the formatting:

' Visual Basic

Private Sub BoldCurrentRow(ByVal ws As Excel.Worksheet)

' Keep track of the previously bolded row.

Static intRow As Integer

' Work with the current active cell.

Dim rngCell As Excel.Range = _

ThisApplication.ActiveCell

' Bold the current row.

rngCell.EntireRow.Font.Bold = True

' Make sure intRow isn't 0 (meaning that

' this is your first pass through here).

If intRow <> 0 Then

' If you're on a different

' row than the last time through here,

' make the old row not bold.

If rngCell.Row <> intRow Then

Dim rng As Excel.Range = _

DirectCast(ws.Rows(intRow), Excel.Range)

rng.EntireRow.Font.Bold = False

End If

End If

' Store away the new row number

' for next time.

intRow = rngCell.Row

End Sub

// C#

private int LastBoldedRow = 0;

private void BoldCurrentRow(Excel.Worksheet ws)

{

// Keep track of the previously bolded row.

// Work with the current active cell.

Excel.Range rngCell = ThisApplication.ActiveCell;

// Bold the current row.

rngCell.EntireRow.Font.Bold = true;

// Make sure intRow isn't 0 (meaning that

// this is your first pass through here).

if (LastBoldedRow != 0)

{

// If you're on a different

// row than the last time through here,

// make the old row not bold.

if (rngCell.Row != LastBoldedRow)

{

Excel.Range rng =

(Excel.Range)ws.Rows[LastBoldedRow, Type.Missing];

rng.Font.Bold = false;

}

}

// Store away the new row number

// for next time.

LastBoldedRow = rngCell.Row;

}

This example takes the following actions in order to make the current row bold, and to make the previously bolded row normal:

• 
  Declares a variable (static, in Visual Basic) to keep track of the previously selected row:
  
• 
  ' Visual Basic
  
• 
  Static intRow As Integer
  
• 
  
  
• 
  // C#
  
• 
  private int LastBoldedRow = 0;
  
• 
  Retrieves a reference to the current cell using the Application.ActiveCell property:
  
• 
  ' Visual Basic
  
• 
  private int LastBoldedRow = 0;
  
• 
  Dim rngCell As Excel.Range = ThisApplication.ActiveCell
  
• 
  
  
• 
  // C#
  
• 
  Excel.Range rngCell = ThisApplication.ActiveCell;
  
• 
  Bolds the current row using the EntireRow property of the active cell:
  
• 
  ' Visual Basic
  
• 
  rngCell.EntireRow.Font.Bold = True
  
• 
  
  
• 
  // C#
  
• 
  rngCell.EntireRow.Font.Bold = true;
  
• 
  Ensures that the current value of intRow isn't 0, which indicates that this is the first time through this code:
  
• 
  ' Visual Basic
  
• 
  If intRow <> 0 Then
  
• 
  ' Code removed here...
  
• 
  End If
  
• 
  
  
• 
  // C#
  
• 
  if (LastBoldedRow != 0)
  
• 
  {
  
• 
  // Code removed here...
  
• 
  }
  
• 
  Ensures that the current row is different from the previous row. The code only needs to modify the state of the row if it's different than the previous row. The Row property returns an integer indicating the row corresponding to the range:
  
• 
  ' Visual Basic
  
• 
  If rngCell.Row <> intRow Then
  
• 
  ' Code removed here...
  
• 
  End If
  
• 
  
  
• 
  // C#
  
• 
  if (rngCell.Row != LastBoldedRow)
  
• 
  {
  
• 
  // Code removed here...
  
• 
  }
  
• 
  Retrieves a reference to a range representing the row selected previously and sets that row to not be bold:
  
• 
  ' Visual Basic
  
• 
  Dim rng As Excel.Range = _
  
• 
  DirectCast(ws.Rows(intRow), Excel.Range)
  
• 
  rng.Font.Bold = False
  
• 
  
  
• 
  // C#
  
• 
  Excel.Range rng =
  
• 
  (Excel.Range)ws.Rows[LastBoldedRow, Type.Missing];
  
• 
  rng.Font.Bold = false;
  

The sample workbook calls the BoldCurrentRow procedure from its SheetSelectionChange event handler. In that procedure, the code verifies that the new selection is within the correct range (using the Intersect method of the Application object), and calls the BoldCurrentRow procedure if it is:

' Visual Basic

Private Sub ThisWorkbook_SheetSelectionChange( _

ByVal Sh As Object, ByVal Target As Excel.Range) _

Handles ThisWorkbook.SheetSelectionChange

If Not ThisApplication.Intersect(Target, _

ThisApplication.Range("BoldSelectedRow")) Is Nothing Then

' The selection is within the range where you're making

' the selected row bold.

BoldCurrentRow(DirectCast(Sh, Excel.Worksheet))

End If

End Sub

// C#

protected void ThisWorkbook_SheetSelectionChange(

System.Object sh, Excel.Range Target)

{

// Don't forget that the Intersect method requires

// thirty parameters.

if (ThisApplication.Intersect(Target,

ThisApplication.get_Range("BoldSelectedRow", Type.Missing),

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Type.Missing)

!= null)

{

// The selection is within the range where you're making

//the selected row bold.

BoldCurrentRow((Excel.Worksheet) sh);

}

}

Working with Ranges

Once you've got a reference to a range, what can you do with it? The list is endless, as you can imagine. This section focuses on a few techniques for working with Range objects and provides simple examples of each technique. All the samples for this section can be found on the Range Class sheet of the sample workbook.

Automatically Filling Ranges

The Range class's AutoFill method allows you to fill a range with values automatically. Most often the AutoFill method is used to store incrementally increasing or decreasing values into a range. You can specify the behavior by supplying an optional constant from the XlAutoFillType enumeration (xlFillDays, xlFillFormats, xlFillSeries, xlFillWeekdays, xlGrowthTrend, xlFillCopy, xlFillDefault, xlFillMonths, xlFillValues, xlFillYears, or xlLinearTrend). If you don't specify a fill type, Excel assumes you want the default fill type (xlFillDefault) and fills the specified range as it sees fit.

The sample worksheet, shown in Figure 24, includes four regions to be auto-filled. Column B is to include five weekdays; Column C is to include five months; Column D is to include dates incrementing by years, over five years; and Column E is to include a series of numbers, incrementing by two for each row. Figure 25 shows the same region after running the sample code.



Figure 24. Before calling the AutoFill method for four sample ranges.



Figure 25. After auto-filling the ranges.

Clicking the AutoFill link runs the following procedure:

' Visual Basic

Private Sub AutoFill()

Dim rng As Excel.Range = ThisApplication.Range("B1")

rng.AutoFill(ThisApplication.Range("B1:B5"), _

Excel.XlAutoFillType.xlFillDays)

rng = ThisApplication.Range("C1")

rng.AutoFill(ThisApplication.Range("C1:C5"), _

Excel.XlAutoFillType.xlFillMonths)

rng = ThisApplication.Range("D1")

rng.AutoFill(ThisApplication.Range("D1:D5"), _

Excel.XlAutoFillType.xlFillYears)

rng = ThisApplication.Range("E1:E2")

rng.AutoFill(ThisApplication.Range("E1:E5"), _

Excel.XlAutoFillType.xlFillSeries)

End Sub

// C#

private void AutoFill()

{

Excel.Range rng = ThisApplication.get_Range("B1", Type.Missing);

rng.AutoFill(ThisApplication.get_Range("B1:B5", Type.Missing),

Excel.XlAutoFillType.xlFillDays);

rng = ThisApplication.get_Range("C1", Type.Missing);

rng.AutoFill(ThisApplication.get_Range("C1:C5", Type.Missing),

Excel.XlAutoFillType.xlFillMonths);

rng = ThisApplication.get_Range("D1", Type.Missing);

rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),

Excel.XlAutoFillType.xlFillYears);

rng = ThisApplication.get_Range("E1:E2", Type.Missing);

rng.AutoFill(ThisApplication.get_Range("E1:E5", Type.Missing),

Excel.XlAutoFillType.xlFillSeries);

}

In each case, you must specify two ranges:

• 
  The range calling the AutoFill method, which specifies the "starting point" of the fill.
  
• 
  The range to be filled, passed as a parameter to the AutoFill method; this destination range must include the source range.
  

The second parameter to the AutoFill method, the XlAutoFillType enumerated value, is optional. Generally, you'll need to supply this value in order to get the behavior you need. For example, try changing this code:

' Visual Basic

rng.AutoFill(ThisApplication.Range("D1:D5"), _

Excel.XlAutoFillType.xlFillYears)

// C#

rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),

Excel.XlAutoFillType.xlFillYears);

so that it looks like this:

' Visual Basic

rng.AutoFill(ThisApplication.Range("D1:D5"))

// C#

rng.AutoFill(ThisApplication.get_Range("D1:D5", Type.Missing),

Excel.XlAutoFillType.xlFillDefault);

Instead of ending up with dates that increment by years, the dates increment by days.