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

Calculate: Forces a recalculation of all open workbooks, a specific workbook, or a specific range:

' Visual Basic

ThisApplication.Calculate

' Or...

ThisWorkbook.Calculate

' Or...

ThisApplication.Range("SomeNamedRange").Calculate

// C#

ThisApplication.Calculate();

// Or...

ThisWorkbook.Calculate();

// Or...

ThisApplication.get_Range("A1", "B12").Calculate();

Note   As shown in the example code, the Range and Worksheet objects also supply a Calculate method. Use the method of the object that limits the calculation range to the smallest number of cells that you want to recalculate. The recalculation engine in Excel is very fast, but if you can limit the number of cells involved, you can optimize the operation. Use Application.Calculate only when you want to recalculate every pending change in every open workbook.

Tip   Visual Basic .NET and C# don't handle Excel members exactly the same. For example, the Range property in Excel, VBA, and Visual Basic .NET can only be accessed in C# using the get_Range method. You'll find several examples of this, and other, accessor members throughout this document.

• 
  CheckSpelling: Returns a Boolean indicating whether the supplied parameter is spelled correctly. You can optionally supply the name of a custom dictionary and a Boolean indicating whether you want to ignore case. The following fragment checks the spelling of a value you supply and indicates the results on the sheet:
  
• 
  ' Visual Basic
  
• 
  Private Sub TestSpelling()
  
• 
  Dim rng As Excel.Range = _
  
• 
  ThisApplication.Range("CheckSpelling")
  
• 
  Dim strOut As String
  
• 
  
  
• 
  If ThisApplication.CheckSpelling( _
  
• 
  rng.Offset(0, 1).Value.ToString) Then
  
• 
  strOut = "Spelled correctly"
  
• 
  Else
  
• 
  strOut = "Spelled incorrectly"
  
• 
  End If
  
• 
  rng.Offset(0, 2).Value = strOut
  
• 
  End Sub
  
• 
  
  
• 
  // C#
  
• 
  private void TestSpelling()
  
• 
  {
  
• 
  // If you specify only a named range in the call
  
• 
  // to get_Range, use Type.Missing for the second parameter.
  
• 
  Excel.Range rng = ThisApplication.
  
• 
  get_Range("CheckSpelling", Type.Missing);
  
• 
  
  
• 
  // Note that C# requires you to retrieve and set
  
• 
  // the Value2 property of the Range, rather than
  
• 
  // the Value property, because the Value property
  
• 
  // is parameterized, making it unavailable to C# code:
  
• 
  rng.get_Offset(0, 2).Value2 =
  
• 
  (ThisApplication.CheckSpelling(
  
• 
  rng.get_Offset(0, 1).Value2.ToString(),
  
• 
  Type.Missing, Type.Missing)
  
• 
  ? "Spelled correctly"
  
• 
  : "Spelled incorrectly");
  
• 
  }
  

Tip   The previous fragment uses the Offset method of a Range object, neither of which you may have encountered yet. Both are discussed in the section discussing the Range object later in this article. The use of the Range class is simple to understand: A Range object represents a cell or group of cells. In this case, the Range object refers to the named range, CheckSpelling. The Offset property returns a Range object at the specified number of rows and columns from the upper-left corner of the associated Range, and allows you to work with cells relative to a known location.

• 
  Evaluate: Converts an Excel name into an actual reference or value. This method allows you to create a reference as a string, and then convert it as needed into an actual object reference, or to evaluate the value of the expression. The following example allows you to enter a cell address into the sample sheet, and the code places text into the cell whose address you specify:
  
• 
  ' Visual Basic
  
• 
  Private Sub TestEvaluate()
  
• 
  Dim rng As Excel.Range = _
  
• 
  ThisApplication.Range("Evaluate")
  
• 
  
  
• 
  Try
  
• 
  Dim rngNew As Excel.Range = _
  
• 
  ThisApplication.Evaluate( _
  
• 
  DirectCast(rng.Offset(0, 1).Value), Excel.Range)
  
• 
  rngNew.Value = "Hello, World!"
  
• 
  Catch ex As Exception
  
• 
  MessageBox.Show(ex.Message, ThisApplication.Name)
  
• 
  End Try
  
• 
  End Sub
  
• 
  
  
• 
  // C#
  
• 
  private void TestEvaluate()
  
• 
  {
  
• 
  Excel.Range rng = ThisApplication.
  
• 
  get_Range("Evaluate", Type.Missing);
  
• 
  
  
• 
  try
  
• 
  {
  
• 
  Excel.Range rngNew =
  
• 
  (Excel.Range) ThisApplication.Evaluate(
  
• 
  rng.get_Offset(0, 1).Value2);
  
• 
  rngNew.Value2 = "Hello, World!";
  
• 
  }
  
• 
  catch (Exception ex)
  
• 
  {
  
• 
  MessageBox.Show(ex.Message, ThisApplication.Name);
  
• 
  }
  
• 
  }
  
• 
  MailSystem, MailSession, MailLogoff, MailLogon, SendMail: These members allow you to log on, send the current workbook as an attachment, and log off from your installed e-mail system. The MailSystem property indicates the installed e-mail system and the MailSession property returns a reference to the current e-mail session (you don't need to log on if there's an active session). The following example sends the sample workbook as an attachment to a simple e-mail message:
  
• 
  ' Visual Basic
  
• 
  Private Sub TestEmail()
  
• 
  If ThisApplication.MailSystem = Excel.XlMailSystem.xlMAPI Then
  
• 
  If ThisApplication.MailSession Is Nothing Then
  
• 
  Dim frm As New SendMail
  
• 
  If frm.ShowDialog = DialogResult.OK Then
  
• 
  ThisApplication.MailLogon( _
  
• 
  frm.EmailName, frm.EmailPassword, frm.DownloadNewMail)
  
• 
  End If
  
• 
  End If
  
• 
  Dim strEmail As String = _
  
• 
  ThisApplication.Range("SendMail").Offset(0, 1). _
  
• 
  Value.ToString
  
• 
  ThisWorkbook.SendMail(strEmail, "Sample Excel Email")
  
• 
  ThisApplication.MailLogoff()
  
• 
  Else
  
• 
  MessageBox.Show( _
  
• 
  "This demonstration works only if MAPI is installed.")
  
• 
  End If
  
• 
  End Sub
  
• 
  
  
• 
  // C#
  
• 
  private void TestEmail()
  
• 
  {
  
• 
  if (ThisApplication.MailSystem ==
  
• 
  Excel.XlMailSystem.xlMAPI )
  
• 
  {
  
• 
  if ( ThisApplication.MailSession == null )
  
• 
  {
  
• 
  SendMail frm = new SendMail();
  
• 
  if (frm.ShowDialog() == DialogResult.OK )
  
• 
  {
  
• 
  ThisApplication.MailLogon(frm.EmailName,
  
• 
  frm.EmailPassword, frm.DownloadNewMail);
  
• 
  }
  
• 
  }
  
• 
  string strEmail = ThisApplication.
  
• 
  get_Range("SendMail", Type.Missing).
  
• 
  get_Offset(0, 1).Value2.ToString();
  
• 
  ThisWorkbook.SendMail(strEmail,
  
• 
  "Sample Excel Email", Type.Missing);
  
• 
  ThisApplication.MailLogoff();
  
• 
  }
  
• 
  else
  
• 
  {
  
• 
  MessageBox.Show("This demonstration works only if " +
  
• 
  "MAPI is installed.");
  
• 
  }
  
• 
  }
  

Note   The Workbook class provides the SendMail method; this makes sense, because the most granular object you can email is the workbook itself. You'll note that the SendMail method doesn't supply any means of attaching text to the message you send, nor is there much flexibility in the addressing. Clearly, these members are supplied only to make it simple to send a workbook via email. If you want more full-featured support, you'll to investigate other means of interacting with e-mail. In addition, if you're not currently online and connected to your e-mail system, the previous sample code will fail. You could work around this failure and not attempt to send the mail if the MailSession property returns Nothing.

• 
  Quit: Allows you to quit Excel programmatically. If you've set the DisplayAlerts property to False, you won't be prompted to save any unsaved data. In addition, if you set the Saved property of a Workbook to True, Excel won't ask you to save it whether or not you've made changes:
  
• 
  ' Visual Basic
  
• 
  ThisApplication.Quit
  
• 
  
  
• 
  // C#
  
• 
  ThisApplication.Quit();
  
• 
  Undo: Cancels the last action taken by the user within the user interface. This method has no effect on actions taken by code, and can only undo a single action. It's not terribly functional, but it does allow you to undo the last action the user took before executing your code:
  
• 
  ' Visual Basic
  
• 
  ThisApplication.Undo
  
• 
  
  
• 
  // C#
  
• 
  ThisApplication.Undo();
  

Members That Handle File Manipulation

The Application object provides several members that allow you to interact with the file system within the Excel application's context. The following sections describe some of the members you're likely to use. (The samples described in this section are on the Application File Handling sheet of the sample workbook.)

DefaultFilePath Property

This simple property gets or sets the path Excel uses for loading and saving files:

' Visual Basic

' When the workbook opens:

ThisApplication.Range("DefaultFilePath").Value = _

ThisApplication.DefaultFilePath

' When you save the DefaultFilePath property:

ThisApplication.DefaultFilePath = _

ThisApplication.Range("DefaultFilePath"). _

Value.ToString

// C#

// When the workbook opens:

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

Value2 = ThisApplication.DefaultFilePath;

// When you save the DefaultFilePath property:

ThisApplication.DefaultFilePath =

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

Value2.ToString();

DefaultSaveFormat Property

This property gets or sets the default format for saving workbooks. Excel provides a large number of options for this property, all of which are members of the XlFileFormat enumeration. The sample workbook allows you to select from the available items, as shown in Figure 1. The following code fragment demonstrates how the sample loads and saves the property's value.

In the example, column E in the sample sheet contains a list of the names of all the possible values for the XlFileFormat enumeration (in a range named "XlFileFormat") and column F contains the corresponding integer values. Figure 2 shows a subset of these two columns. The DefaultSaveFormat named range (in Figure 1) contains a reference to the XlFileFormat range, allowing you to select from a list. Once you make a choice and elect to save the value, the code must find the string you've selected using the Range.Find method, and then uses the Range.Offset method to return a value at the specified offset from the value you found. (See the section titled "Searching within Ranges" later in this document for more information on the Range.Find method.) Finally, the code stores the integer value (converted to the appropriate enumeration type) back into the DefaultSaveFormat property.

Retrieving the current value of the DefaultSaveFormat is easy. The following code converts the value into text, and displays it in the correct Range on the sample sheet:

' Visual Basic

' When the workbook opens, convert the enumerated value

' into a string:

ThisApplication.Range("DefaultSaveFormat").Value = _

ThisApplication.DefaultSaveFormat.ToString

// C#

// When the workbook opens, convert the enumerated value

// into a string:

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

Value2 = ThisApplication.DefaultSaveFormat.ToString();

Assigning the selected value back is a bit more difficult. This involves three steps. The code must handle the following tasks:

Retrieve the name of the selected save format from the DefaultSaveFormat range on the sheet:

' Visual Basic

' Retrieve the name of the new save format, as a string:

Dim strSaveFormat As String = _

ThisApplication.Range("DefaultSaveFormat"). _

Value.ToString()

// C#

// Retrieve the name of the new save format,

// as a string:

string strSaveFormat = ThisApplication.

get_Range("DefaultSaveFormat", Type.Missing).

Value2.ToString();

Look up the matching integer value, in the column adjacent to the XlFileFormat range on the sheet, calling the Find method of the Range class. Then, the code uses the Range.Offset property to retrieve the value one column to the right:

' Visual Basic

Dim intSaveFormat As Integer = _

CType(ThisApplication.Range("XlFileFormat"). _

Find(strSaveFormat).Offset(0, 1).Value, Integer)

// C#

Excel.Range rng = ThisApplication.

get_Range("xlFileFormat", Type.Missing);

Excel.Range rngFind = rng.Find(strSaveFormat,

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

Excel.XlSearchDirection.xlNext, Type.Missing, Type.Missing,

Type.Missing);

// In C#, use the get_Offset method instead of the Offset property:

int intSaveFormat =

Convert.ToInt32(rngFind.get_Offset(0, 1).Value2);

Assign the integer value back into the DefaultSaveFormat property:

' Visual Basic

ThisApplication.DefaultSaveFormat = _

CType(intSaveFormat, Excel.XlFileFormat)

// C#

ThisApplication.DefaultSaveFormat =

Excel.XlFileFormat) intSaveFormat;



Figure 1. Select a file format from the list of available types.



Figure 2. A subset of the XlFileFormat range on the sample worksheet.

RecentFiles Property

The RecentFiles property returns a collection of strings containing the names of all the files that appear within the File menu's list of recently used files. The length of the list will vary depending on the number of files the user has selected to retain. The sample workbook calls this procedure as it opens, copying the list of recent files to a range named RecentFiles on the sample worksheet:

' Visual Basic

Private Sub ListRecentFiles()

Dim i As Integer

Dim rng As Excel.Range = DirectCast( _

ThisApplication.Range("RecentFiles"). _

Cells(1, 1), Excel.Range)

For i = 1 To ThisApplication.RecentFiles.Count

rng.Offset(i - 1, 0).Value = _

ThisApplication.RecentFiles(i).Name

Next

End Sub

// C#

private void ListRecentFiles()

{

Excel.Range rng = (Excel.Range)ThisApplication.

get_Range("RecentFiles", Type.Missing).Cells[1, 1];

for (int i = 1; i <= ThisApplication.RecentFiles.Count; i++)

{

rng.get_Offset(i - 1, 0).Value2 =

ThisApplication.RecentFiles[i].Name;

}

}

FileDialog property

The FileDialog property returns a FileDialog object, which handles four types of file manipulation. This FileDialog object returned by the property allows you to:

• 
  Select a file and open it.
  
• 
  Select a file location and save the current workbook.
  
• 
  Select a folder.
  
• 
  Select a file name.
  

Using this dialog box, you can take advantage of all the file handling capabilities provided by Microsoft Office. The FileDialog property requires that you select a particular use of the dialog box by passing it one of the msoFileDialogType enumerated values, msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogOpen, or msoFileDialogSaveAs. You can then interact with the FileDialog object returned by the property.

The FileDialog object, like many others, is provided by the Microsoft.Office.Core namespace. To avoid typing the full path for each Office object, the sample project imports this namespace with an Imports or using statement. The code fragments in this document assume that you've also added the appropriate namespace reference to your file:

' Visual Basic

Imports Office = Microsoft.Office.Core

// C#

using Office = Microsoft.Office.Core;

The FileDialog object's Show method displays the dialog box, and returns -1 if you press OK, and 0 if you press Cancel. If you've used the msoFileDialogOpen or msoFileDialogSaveAs enumerated values, you can use the Execute method of the class to actually open or save the files. The SelectedItems property contains a collection of strings, each representing one of the selected file names.

For example, the following code from the sample workbook prompts you to open a new workbook. This fragment allows multi-selection, clears the list of available filters, adds two new filters, and then displays the dialog box, as shown in Figure 3. If you select a file or files, the code then calls the Execute method of the FileDialog object to open the requested file(s):

' Visual Basic

With ThisApplication.FileDialog( _

Office.MsoFileDialogType.msoFileDialogOpen)

.AllowMultiSelect = True

.Filters.Clear

.Filters.Add "Excel Files", "*.xls;*.xlw"

.Filters.Add "All Files", "*.*"

If .Show <> 0 Then

.Execute

End If

End With

// C#

dlg = ThisApplication.get_FileDialog(

Office.MsoFileDialogType.msoFileDialogOpen);

dlg.Filters.Clear();

dlg.Filters.Add("Excel Files", "*.xls;*.xlw", Type.Missing);

dlg.Filters.Add("All Files", "*.*", Type.Missing);

if(dlg.Show() != 0)

dlg.Execute();



Figure 3. Using the FileDialog class displays the standard File Open dialog box.

The following fragment from the sample demonstrates how you might use the dialog box to select a folder:

' Visual Basic

With ThisApplication.FileDialog( _

Office.MsoFileDialogType.msoFileDialogFolderPicker)

If .Show <> 0 Then

ThisApplication.Range("FolderPickerResults"). _

Value = .SelectedItems.Item(1)

End If

End With

// C#

dlg = ThisApplication.get_FileDialog(

Office.MsoFileDialogType.msoFileDialogFolderPicker);

if (dlg.Show() != 0)

{

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

Value2 = dlg.SelectedItems.Item(1);

}

Note   The Application object also provides the GetOpenFileName and GetSaveAsFileName methods, which allow you to select a file name for opening. Although you can use these, you'll find that the corresponding OpenFileDialog and SaveFileDialog controls provided by the Microsoft .NET Framework are richer, and easier to use.

Other Useful Members

The Application object provides several members that don't fit other categories, such as the WorksheetFunction property, the Names collection, and the Windows collection. The following sections describe these members.

The WorksheetFunction Class

The Application object includes a property, WorksheetFunction, which returns an instance of the WorksheetFunction class. This class provides a number of shared/static methods, each of which wraps up an Excel worksheet function. Each of these methods exposes one of the many Excel spreadsheet calculation functions that aren't otherwise provided by VBA. Some of the members are duplicated by Visual Basic .NET and C# operators and methods, so you're unlikely to use those (for example, the And method).

What you will find buried in the methods of the WorksheetFunction class are a large number of interesting and useful functions, summarized in the following list:

• 
  Mathematical functions such as Acos, Acosh, Asin, Asinh, Cosh, Degrees, Ln, Log, Median, Max, Min, Mode, Radians, and more.
  
• 
  Domain functions that allow you to perform calculations on ranges, such as DAverage, DCount, DCountA, DGet, DMax, DMin, DProduct, DSum, and more.
  
• 
  Logical functions such as IsErr, IsError, IsLogical, IsNA, IsNonText, IsNumber, IsText.
  
• 
  Statistical functions such as BetaDist, BinomDist, ChiTest, ChiInv, LogNormDist, NegBinomDist, Pearson, SumProduct, SumSq, TDist, TTest, Var, VarP, and more.
  
• 
  Spreadsheet functions that you're unlikely to take advantage of from the .NET Framework, such as And, Or, Choose, and more.
  
• 
  Thai-related functions: You'll find an unexplained raft of functions that manipulate Thai numbers, calendar, and currency (rumor has it that the Excel team was once exceedingly fond of Thai food and so added these functions to help calculate the bill at the local Thai restaurant, but that's all apocryphal information at this point), such as BahtText, IsThaiDigit, ThaiDayOfWeek, ThaiDigit, ThaiMonthOfYear, ThaiNumSound, ThaiNumString, ThaiStringLength, ThaiYear, RoundBahtDown, and RoundBahtUp.
  

From a Visual Studio .NET project, it's easy to take advantage of the WorksheetFunction class. Because the project template provides you with the ThisApplication object, you can simply refer to the WorksheetFunction property of that object. The sample application contains a sheet named Other Application Members, shown in Figure 4, which tests just a few members of the class.

Note   The WorksheetFunction class and its members provides a good example of why working with Excel objects from Visual Basic is far easier than the equivalent code in C#. Many of the WorksheetFunction class methods require C# developers to pass 30 parameters, most of which are empty. It's certainly possible to ease this load by writing wrappers around the different groups of methods (those that have one required parameter, those that have two required parameters, and so on). For the purposes of this document, the code calls the methods "naked", with no wrapper methods. The C# code is quite ugly, that's for sure.

Clicking the Demonstrate WorksheetFunction link runs the following code (for more information on the Sort method, see the section "Sorting Data within a Range"):

' Visual Basic

Private Sub TestWorksheetFunction()

Dim ws As Excel.Worksheet = _

DirectCast(ThisWorkbook.ActiveSheet, Excel.Worksheet)

Dim rng As Excel.Range = ws.Range("RandomNumbers")

Dim rnd As New System.Random

Dim i As Integer

For i = 1 To 20

ws.Cells(i, 2) = rnd.Next(100)

Next i

rng.Sort(rng, _

Orientation:=Excel.XlSortOrientation.xlSortColumns)

With ThisApplication.WorksheetFunction

ws.Range("Min").Value = .Min(rng)

ws.Range("Max").Value = .Max(rng)

ws.Range("Median").Value = .Median(rng)

ws.Range("Average").Value = .Average(rng)

ws.Range("StDev").Value = .StDev(rng)

End With

End Sub

// C#

private void TestWorksheetFunction()

{

Excel.Worksheet ws = (Excel.Worksheet) ThisWorkbook.ActiveSheet;

Excel.Range rng = ws.get_Range("RandomNumbers", Type.Missing);

System.Random rnd = new System.Random();

for ( int i = 1 ; i <= 20; i++)

ws.Cells[i, 2] = rnd.Next(100);

rng.Sort(rng, Excel.XlSortOrder.xlAscending,

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);

Excel.WorksheetFunction wsf = ThisApplication.WorksheetFunction;

ws.get_Range("Min", Type.Missing).Value2 = wsf.Min(rng,

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);

ws.get_Range("Max", Type.Missing).Value2 = wsf.Max(rng,

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);

ws.get_Range("Median", Type.Missing).Value2 = wsf.Median(rng,

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);

ws.get_Range("Average", Type.Missing).Value2 = wsf.Average(rng,

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);

ws.get_Range("StDev", Type.Missing).Value2 = wsf.StDev(rng,

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);

}