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

Searching within Ranges

The Range class's Find method allows you to search for text within the range. This flexible method mimics the behavior of the Find and Replace dialog box in Excel shown in Figure 26—as a matter of fact, it interacts directly with this dialog box. That is, the Range.Find method uses either parameters you pass it to determine the search behavior, or, if you don't pass parameters, it uses the values it finds in the Find and Replace dialog box. Table 4 lists the parameters for the Range.Find method, all but the first of which are optional.

Figure 26. Selections on this dialog box affect the behavior of the Find method.

Warning   Because almost all the parameters to Range.Find are optional, and because the user might change values from the Find and Replace dialog box, it's up to you to make sure you actually do pass all values to the Find method, unless your goal is to take the user's selections into account. Of course, C# developers needn't worry about this problem, because they must supply all the parameters on each method call.

Table 4. Parameters to the Range.Find method

Parameter	Type	Description
What (required)	Object	The data to find; can be a string or any Excel data type.
After	Range	The range after which you want the search to start (this cell won't be included in the search); if you don't specify this cell, the search begins in the upper-left corner of the range.
LookIn	XlFindLookin (xlValue, xlComments, xlFormulas)	The type of information to be searched; cannot be combined using the Or operator.
LookAt	XlLookAt (xlWhole, xlPart)	Determines whether the search matches entire cells, or partial cells.
SearchOrder	XlSearchOrder (xlByRows, xlByColumns)	Determines the order for the search; xlByRows (the default) causes the search to go across and then down, and xlByColumns causes the search to go down and then across.
SearchDirection	XlSearchDirection (xlNext, xlPrevious)	Determines the direction of the search; the default is xlNext.
MatchCase	Boolean	Determines whether the search is case-sensitive.
MatchByte	Boolean	Determines whether double-byte characters match only double-byte characters (True) or equivalent single-byte characters (False); only applies if you've installed double-byte support.

The following example from the sample workbook searches a range (named "Fruits") and modifies the font for cells containing the word "apples" (Figure 27 shows the results of the search). This procedure also uses the FindNext method, which uses the previously set search settings to repeat the search. (The Range.FindPrevious method works much the same as the Range.FindNext method, but won't be used in this example.) You specify the cell after which to search, and the FindNext method handles the rest.



Figure 27. The results of searching for cells containing the word "apples"

Tip   The FindNext (and FindPrevious) method's search wraps back to the beginning of the search range once it has reached the end of the range. It's up to your code to make sure that you don't wrap around forever, in an infinite loop. The sample procedure shows one way to handle this. If you want to avoid this endless wrapping altogether, or if you want to perform a search that's too complex for the Find/FindNext/FindPrevious methods, you can also loop through all the cells in a range, using a For Each loop.

Clicking the Find link on the Range Class sheet of the sample workbook runs the following procedure:

' Visual Basic

Private Sub DemoFind()

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

Dim rngFound As Excel.Range

' Keep track of the first range you find.

Dim rngFoundFirst As Excel.Range

' You should specify all these parameters

' every time you call this method, since they

' can be overriden in the user interface.

rngFound = rng.Find( _

"apples", , _

Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _

Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,

False)

While Not rngFound Is Nothing

If rngFoundFirst Is Nothing Then

rngFoundFirst = rngFound

ElseIf rngFound.Address = rngFoundFirst.Address Then

Exit While

End If

With rngFound.Font

.Color = ColorTranslator.ToOle(Color.Red)

.Bold = True

End With

rngFound = rng.FindNext(rngFound)

End While

End Sub

// C#

private void DemoFind()

{

Excel.Range rng = ThisApplication.

get_Range("Fruits", Type.Missing);

Excel.Range rngFound;

// Keep track of the first range you find.

Excel.Range rngFoundFirst = null;

// You should specify all these parameters

// every time you call this method, since they

// can be overriden in the user interface.

rngFound = rng.Find("apples", Type.Missing,

Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,

Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,

false, Type.Missing, Type.Missing);

while (rngFound != null)

{

if (rngFoundFirst == null )

{

rngFoundFirst = rngFound;

}

else if (GetAddress(rngFound) == GetAddress(rngFoundFirst))

{

break;

}

rngFound.Font.Color = ColorTranslator.ToOle(Color.Red);

rngFound.Font.Bold = true;

rngFound = rng.FindNext(rngFound);

}

}

The code takes these actions to achieve its goals:

• 
  Declares Excel.Range variables for tracking the entire range, the first found range, and the current found range:
  
• 
  ' Visual Basic
  
• 
  Dim rng As Excel.Range = ThisApplication.Range("Fruits")
  
• 
  Dim rngFound As Excel.Range
  
• 
  Dim rngFoundFirst As Excel.Range
  
• 
  
  
• 
  // C#
  
• 
  Excel.Range rng = ThisApplication.
  
• 
  get_Range("Fruits", Type.Missing);
  
• 
  Excel.Range rngFound;
  
• 
  Excel.Range rngFoundFirst = null;
  
• 
  Searches for the first match, specifying all the parameters except the cell to search after—by default, the search starts after the cell in the upper-left corner of the range—and searches for "apples" in the cell values, matching partial values, searching by rows in a forward direction, not case sensitive:
  
• 
  ' Visual Basic
  
• 
  rngFound = rng.Find( _
  
• 
  "apples", , _
  
• 
  Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
  
• 
  Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
  
• 
  False)
  
• 
  
  
• 
  // C#
  
• 
  rngFound = rng.Find("apples", Type.Missing,
  
• 
  Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
  
• 
  Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext,
  
• 
  false, Type.Missing, Type.Missing);
  
• 
  Continues searching as long as it continues to find matches:
  
• 
  ' Visual Basic
  
• 
  While Not rngFound Is Nothing
  
• 
  ' Code removed here...
  
• 
  End While
  
• 
  
  
• 
  // C#
  
• 
  while (rngFound != null)
  
• 
  {
  
• 
  // Code removed here...
  
• 
  }
  
• 
  Compares the first found range (rngFoundFirst) to Nothing, which it can only be if the code just found the first match. In that case, the code stores away the found range; otherwise, if the address of the found range matches the address of the first found range, the code exits the loop.
  
• 
  ' Visual Basic
  
• 
  If rngFoundFirst Is Nothing Then
  
• 
  rngFoundFirst = rngFound
  
• 
  ElseIf rngFound.Address = rngFoundFirst.Address Then
  
• 
  Exit While
  
• 
  End If
  
• 
  
  
• 
  // C#
  
• 
  if (rngFoundFirst == null )
  
• 
  {
  
• 
  rngFoundFirst = rngFound;
  
• 
  }
  
• 
  else if (GetAddress(rngFound) == GetAddress(rngFoundFirst))
  
• 
  {
  
• 
  break;
  
• 
  }
  
• 
  Sets the appearance of the found range:
  
• 
  ' Visual Basic
  
• 
  With rngFound.Font
  
• 
  .Color = ColorTranslator.ToOle(Color.Red)
  
• 
  .Bold = True
  
• 
  End With
  
• 
  
  
• 
  // C#
  
• 
  rngFound.Font.Color = ColorTranslator.ToOle(Color.Red);
  
• 
  rngFound.Font.Bold = true;
  
• 
  Performs another search:
  
• 
  ' Visual Basic
  
• 
  rngFound = rng.FindNext(rngFound)
  
• 
  
  
• 
  // C#
  
• 
  rngFound = rng.FindNext(rngFound);
  

Clicking the Reset Find link on the sample sheet runs this simple procedure, putting the range back as it started:

' Visual Basic

Private Sub ResetFind()

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

With rng.Font

.Color = ColorTranslator.ToOle(Color.Black)

.Bold = False

End With

End Sub

// C#

private void ResetFind()

{

Excel.Range rng = ThisApplication.

get_Range("Fruits", Type.Missing);

rng.Font.Color = ColorTranslator.ToOle(Color.Black);

rng.Font.Bold = false;

}

Tip   If you want to both search and replace within a range, check out the Range.Replace method. This method works much like the Find method, but also allows you to specify a value to replace. The Replace method returns a Boolean value indicating whether it performed any replacements. If it replaces even one value, it returns True.

Sorting Data within a Range

Just as you can sort data within a range from the Excel user interface, you can use the Range.Sort method to sort data programmatically. You indicate the range to be sorted and optionally, up to three rows or columns to sort by and several other optional parameters, and Excel handles the rest. Table 5 shows all the parameters for the Sort method. (Visual Basic .NET developers will most likely use only a few of these parameters, and C# developers must supply a value for each.)

Table 5. Parameters for the Sort method

Parameter	Type	Description
Key1	Object (String or Range)	First sort field, either as a range name (String) or Range object; determines the values to be sorted.
Order1	XlSortOrder (xlAscending, xlDescending)	Determines the sort order for the values specified in Key1.
Key2	Object (String or Range)	Second sort field; cannot be used when sorting a pivot table.
Type	Object	Specified which elements are to be sorted when sorting a pivot table; has no effect on a normal range.
Order2	XlSortOrder	Determines the sort order for the values specified in Key2.
Key3	Object (String or Range)	Third sort field; cannot be used when sorting a pivot table.
Order3	XlSortOrder	Determines the sort order for the values specified in Key3.
Header	XlYesNoGuess (xlGuess, xlNo, xlYes)	Specifies whether the first row contains header information, xlNo is the default value; specify xlGuess if you want Excel to guess.
OrderCustom	Integer	Specifies a one-based index into the list of custom sort orders; leaving this parameter out uses the default sort order. Figure 28 shows the technique for creating a custom sort order. For this example, specifying 6 for this parameter would sort based on the "fruits" custom order.
MatchCase	Boolean	Set to True to perform a case-sensitive sort, False to perform non-case sensitive sort; can't be used with pivot tables.
Orientation	XlSortOrientation (xlSortRows, xlSortColumns)	Orientation for the sort.
SortMethod	XlSortMethod (xlStroke, xlPinYin)	Specifies the sort method; doesn't apply to all languages (current values only apply to Chinese sorting and will have no bearing on other languages).
DataOption1	XlSortDataOption (xlSortTextAsNumbers, xlSortNormal)	Specifies how to sort text in the range specified in Key1; doesn't apply to pivot table sorting.
DataOption2	XlSortDataOption	Specifies how to sort text in the range specified in Key2; doesn't apply to pivot table sorting.
DataOption3	XlSortDataOption	Specifies how to sort text in the range specified in Key3; doesn't apply to pivot table sorting.

Tip   Visual Basic .NET developers have a distinct advantage over C# developers when calling methods like this one. Because you're unlikely to use all the parameters, Visual Basic .NET developers can use named parameters, specifying only the parameters they need. C# developers must pass null values for all the unused parameters in order to accept default behaviors.



Figure 28. You can create your own custom sorting lists, and then refer to these specific sort orders from your code.

Clicking the Sort link on the Range Class sample sheet runs the following procedure to sort the Fruits range by the data in the first column, and then by the data in the second column:

' Visual Basic

Private Sub DemoSort()

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

rng.Sort( _

Key1:=rng.Columns(1), Order1:=Excel.XlSortOrder.xlAscending, _

Key2:=rng.Columns(2), Order2:=Excel.XlSortOrder.xlAscending, _

Orientation:=Excel.XlSortOrientation.xlSortColumns, _

Header:=Excel.XlYesNoGuess.xlNo)

End Sub

// C#

private void DemoSort()

{

Excel.Range rng = ThisApplication.

get_Range("Fruits", Type.Missing);

rng.Sort(rng.Columns[1, Type.Missing],

Excel.XlSortOrder.xlAscending,

rng.Columns[2, Type.Missing],Type.Missing,

Excel.XlSortOrder.xlAscending,

Type.Missing, Excel.XlSortOrder.xlAscending,

Excel.XlYesNoGuess.xlNo, Type.Missing, Type.Missing,

Excel.XlSortOrientation.xlSortColumns,

Excel.XlSortMethod.xlPinYin,

Excel.XlSortDataOption.xlSortNormal,

Excel.XlSortDataOption.xlSortNormal,

Excel.XlSortDataOption.xlSortNormal);

}

Clicking the Reset Sort link on the same sheet runs the following procedure, which sorts the second column based on the custom sort shown in Figure 28:

' Visual Basic

Private Sub ResetSort()

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

rng.Sort(rng.Columns(2), OrderCustom:=6, _

Orientation:=Excel.XlSortOrientation.xlSortColumns, _

Header:=Excel.XlYesNoGuess.xlNo)

End Sub

// C#

private void ResetSort()

{

Excel.Range rng = ThisApplication.

get_Range("Fruits", Type.Missing);

rng.Sort(rng.Columns[2, Type.Missing],

Excel.XlSortOrder.xlAscending,

Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,

Type.Missing, Excel.XlSortOrder.xlAscending,

Excel.XlYesNoGuess.xlNo, 6, Type.Missing,

Excel.XlSortOrientation.xlSortColumns,

Excel.XlSortMethod.xlPinYin,

Excel.XlSortDataOption.xlSortNormal,

Excel.XlSortDataOption.xlSortNormal,

Excel.XlSortDataOption.xlSortNormal);

}

What's Next?

Although this document seems long, it barely scratches the surface of the richness provided by the Excel object model. This document has introduced the most important classes —Application, Workbook, Worksheet, and Range —but hasn't described other classes that may be useful to you. You may need to also investigate the second "tier" of classes provided by the Excel object model, including PivotTable and Chart, for example. The completeness of the Excel object model makes it possible for you to accomplish just about any automated task you require, as long as you're willing to do the research to find the exact class you need. Armed with the contents of this document, the Object Browser, and the Excel VBA online help, you should be able to tackle just about any task you can imagine in Excel.