Ken Getz
MCW Technologies, LLC

April 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Studio® .NET 2003

Summary: Discusses some of the objects provided by Microsoft Office Excel 2003 and explains how you can use them to create managed code solutions with Microsoft Visual Studio Tools for the Microsoft Office System. The focus is mainly on the Application, Workbook, Worksheet, and Range objects. Visual Basic .NET and Visual C# code examples demonstrate some of the properties, methods, and events of each object. (98 printed pages)

Download the ExcelObj.exe from the Microsoft Download Center.

Contents

Introduction
The Application Object
   Members That Control the State and Display in Excel
   Members That Return Objects
   Members That Execute Actions
   Members That Handle File Manipulation
   Other Useful Members
   Application Events
The Workbook Class
   Properties of the Workbook Class
   Working with Document Properties
   Working with Styles
   Working with Sheets
   Methods of the Workbook Class
The Worksheet Class
   There's No Sheet Class
   Working with Protection
   Object Properties
The Range Object
   Managing the Selection
   Referring to a Range in Code
   Using the Technology
   Working with Ranges
   Sorting Data within a Range
What's Next?

Introduction

Developers intending to take advantage of Microsoft® Visual Studio Tools for the Microsoft Office System and those wishing to simply use COM Automation to control Microsoft Office Excel 2003 applications will need to be able to interact with the objects provided by the Excel object model. Excel provides hundreds of objects with which you might want to interact, but you can get a good start on the object model by focusing on a very small subset of the available objects. These include:

• Application
• Workbook
• Worksheet
• Range

Although it's impossible to quantify concretely, a large percentage of the work you'll do with Excel centers around these four classes and their members. In this document, you'll learn how to take advantage of each of these classes, and will be introduced to some of the properties, methods, and events of each. You'll also find examples you can try, demonstrating some of the features of each object.

Tip   In general, developers who use Microsoft Visual Basic® .NET have an easier time working with Microsoft Office objects than do developers who use Microsoft Visual C#® for one important reason: Visual Basic for Applications (VBA) methods often include optional parameters, and Visual Basic .NET supports optional parameters. C# developers will find that they must supply a value for each and every optional method parameter, whereas Visual Basic .NET developers can simply used named parameters to supply only the values they need. In addition, C# doesn't support properties with parameters other than indexers, yet many Excel properties accept parameters. You'll find that properties such as the Application.Range property, available in VBA and Visual Basic .NET, require separate accessor methods for C# developers (the get_Range method replaces the Range property.) Watch for differences between the languages like these throughout this document.

For the most part, you'll find that the Excel object model directly emulates its user interface. It wouldn't be too hard to guess that the Application object provides a wrapper around the entire application, and each Workbook object contains a collection of Worksheet objects. From there, the major abstraction representing cells is the Range object, which allows you to work with individual cells or groups of cells.

Each of the following sections describes one of the major Excel objects, picking specific members of the object for demonstration. With hundreds of objects to explore, it's impossible to dig into all of them here: you'll get enough of the flavor of the object models to be able to get started, and to use the Excel online help for more details.

Tip   Throughout this article, you'll see many uses of the DirectCast and CType methods. The reason for this is that the sample project has its Option Strict setting on—this means that Visual Basic .NET requires strict type conversions. Many Excel methods and properties return Object types or rely on late binding: For example, the Application.ActiveSheet property returns an Object, as opposed to a Worksheet, as you might expect. Therefore, to be as rigorous about conversions as possible, the sample has enabled Option Strict, and handles each type conversion explicitly. (Without using Option Strict in Visual Basic .NET, it's possible that you'll write code that compiles fine, but fails at run time. That's the point of Option Strict—it makes it much less likely that an invalid conversion at run time will cause an exception.) If you're a C# developer reading this document, you'll likely appreciate this decision.

This white paper references the sample project, ExcelObjectModel.sln. This project contains an Excel workbook and associated Visual Basic .NET code. Not every sample shown in this paper appears in the example project, but any that involve more than a line or two of code have been placed into the workbook, with a hyperlink within the project set up to call the code.

Tip   In an article of this limited size, it's not possible to document each and every object or member. It's not even possible to mention a small fraction of the classes. Your best tool for investigating any large object model is the Object Browser window, where you can find a list of each of the classes, along with the members of that class. You'll find that many of the members discussed in this document apply to many different classes: for example, the PrintOut method discussed in the context of the Sheets collection applies just as well to the Chart, Worksheet, Range, and other objects. The point of this document is to give you an idea of what's available, and leave the rest to your inquisitive nature.

The Application Object

The Excel Application object represents the Excel application itself. That may sound obvious, but the Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance. The Application object provides many members, many of which you'll never need to investigate, and others of which will be crucial to the correct behavior of your application. You can break these members down into the following categories:

• Members that control the state and display in Excel
• Members that return objects
• Members that execute actions
• Members that handle file manipulation
• Other members

The following sections introduce each of these groups, along with code examples demonstrating of some of the members.

Members That Control the State and Display in Excel

The Application object provides a large set of properties that control the general state of Excel. Table 1 lists a subset of the state-related Application object properties.

Table 1. Some of the Application properties that control the state of Excel

Of all the properties listed in Table 1, the one you're most likely to use is the ScreenUpdating property. By taking advantage of this property, you can not only make your Excel applications look more professional, you can make them run faster—updating the display after each modification can exact a huge toll on your code, especially when programmatically filling in a large range. It's important, however, that you always set this property when you're done with your work, because Excel won't reset it for you. Therefore, you'll need to always use code similar to the following fragment when using the ScreenUpdating property, taking advantage of .NET exception handling to ensure that screen updating resumes:

' Visual Basic
Try
    ThisApplication.ScreenUpdating = False
    ' Do your work that updates the screen.

Finally
    ThisApplication.ScreenUpdating = True
End Try

// C#
try
{
    ThisApplication.ScreenUpdating = false;
    // Do your work that updates the screen.
}

finally
{
    ThisApplication.ScreenUpdating = true;
}

The Application object also provides a group of properties that control the display in Excel. You can modify any of these to change what users see on the screen. Table 2 lists a subset of the available display options.

Table 2. Some of the Application properties that control the appearance of Excel

Tip   Just as with the ScreenUpdating property, it's important to reset the DisplayAlerts property. Because Excel won't reset this property for you, and with it set to False, Excel won't prompt you to save workbooks before you close them; not carefully resetting the DisplayAlerts property can cause you to lose data if you're not careful.

Members That Return Objects

Many of the Application object's properties return other objects. Because the standard Microsoft Office project template supplied by Visual Studio® .NET contains only the ThisApplication and ThisWorkbook objects, you'll generally need to take advantage of the object members of the Application class to reference the other objects provided by Excel. You can use these members to retrieve a reference to a particular child object using a property like ActiveWindow, or to a collection of available objects using a property like Charts. Table 3 lists a subset of the object-returning properties of the Application object.

Table 3. A subset of the available object-returning properties of the Application object.

You'll most often interact with the Workbooks property of the Application class. This property allows you to iterate through the open workbooks, open or create a new workbook. The following section describes the behavior of this property.

The Workbooks Collection

The Workbooks collection makes it possible to work with all the open workbooks, create a new workbook, and import data into a new workbook. The following list works through some of the main uses you'll find for the Workbooks collection:

• Create a new workbook using code like the following (you can also specify the name of a workbook template as a parameter to the Add method):
  ' Visual Basic
  Dim wb As Excel.Workbook = ThisApplication.Workbooks.Add()
  
  // C#
  Excel.Workbook wb = ThisApplication.Workbooks.Add(Type.Missing);
  

• Close all open workbooks. Unlike most collections, this one allows you to close all its members at once. The following method call closes all open workbooks:
  ' Visual Basic
  ThisApplication.Workbooks.Close()
  
  // C#
  ThisApplication.Workbooks.Close();
  

• Open an existing workbook, using the Open method of the Workbooks collection. In its simplest form, you'll use the Open method like you'll see in the following fragment. The Open method supplies a large number of optional parameters that affect its behavior in specific circumstances, but you generally won't need to use the optional parameters:
  ' Visual Basic
  Dim wb As Excel.Workbook = _
    ThisApplication.Workbooks.Open("C:\YourPath\YourWorkbook.xls")
  
  // C#
  Excel.Workbook wb = ThisApplication.Workbooks.Open( 
      "C:\\YourPath\\Yourworkbook.xls", 
      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);
  

  Tip   If you're a C# developer, get used to seeing references to the Type.Missing value in method calls. Because the Excel object model was written with VBA in mind, many of its methods accept optional parameters—sometimes up to 30 optional parameters. You'll need to use many instances of the Type.Missing value, or supply the specific default value for each individual parameter.

• Open a text file, database, or XML file as a workbook using the OpenText, OpenDatabase or OpenXml method. These methods provide a great deal of flexibility, and even simple coverage would take more space than can be allotted here. Your knowledge of the existence of these methods is enough for now—if you need to load any of these items into Excel, you can investigate these items in more detail. You can use code like the following to load a text file as a workbook, using commas as delimiters, starting on the third row in the text file:
  ' Visual Basic
  Dim wb as Excel.Workbook = _
      ThisApplication.Workbooks.OpenText("C:\Test.txt", StartRow:=3, _
      DataType:=xlDelimited, Comma:=True)
  
  // C#
  Excel.Workbook wb = 
      ThisApplication.Workbooks.OpenText("C:\\Test.txt", 
      Type.Missing, 3, Excel.XlTextParsingType.xlDelimited, 
      Excel.XlTextQualifier.xlTextQualifierDoubleQuote, 
      Type.Missing, Type.Missing, Type.Missing, True, 
      Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing, 
      Type.Missing, Type.Missing, Type.Missing);
  

• Refer to individual workbooks. You can index into the Workbooks collection using either integers (indicating position within the collection) or workbook names. If you want to refer to a workbook by name, however, you'll need to be careful how you refer to it: you must use the name you see in the title bar, which doesn't include the ".xls" extension until you've saved the file:
  ' Visual Basic
  Dim wb As Excel.Workbook = ThisApplication.Workbooks(1)
  ' Before Book1 is saved:
  wb = ThisApplication.Workbooks("Book1")
  ' After Book1 is saved:
  wb = ThisApplication.Workbooks("Book1.xls")
  
  // C#
  Excel.Workbook wb = ThisApplication.Workbooks[1];
  // Before Book1 is saved:
  wb = ThisApplication.Workbooks["Book1"];
  // After Book1 is saved:
  wb = ThisApplication.Workbooks["Book1.xls"];
  

  Tip   When you refer to a particular workbook, you're taking advantage of the default indexer, the Item property. In addition to the Item property, the Workbooks collection, like all collections provided by Microsoft Office, includes a Count property that returns the number of items, (Workbooks in this case) in the collection.

Members That Execute Actions

The Application object provides a number of methods that allow you to execute actions from recalculating the current data to undoing changes to data. The following list enumerates some of the Application object's methods and describes each with a small example. The samples for this section appear on the Application Object sheet of the sample workbook:

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

Figure 4. Select the WorksheetFunction sheet to test out the WorksheetFunction class and its useful methods.

As you can see in the sample code, you can pass a Range object as a parameter to the WorksheetFunction methods. In addition, you can pass a single value, or a list of values as parameters. The methods generally accept up to thirty-two parameters, so if you wanted to calculate the average of a fixed list of numbers, you could use code like the following:

' Visual Basic
dblAverage = ThisApplication.WorksheetFunction.Average( _
 12, 14, 13, 19, 21)

// C#
// Note the number of Type.Missing values--the method accepts
// 30 parameters.
dblAverage = ThisApplication.WorksheetFunction.Average(
    12, 14, 13, 19, 21, 
    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);

The Window Class and Windows Collection

As you might expect, it's the Application object that provides control over the windows displayed within the Excel application, and you can use the Windows property of the Application object to open, close, and arrange Excel object windows.

The Windows property returns a collection of Window objects, and you can call the Arrange method to arrange all the open windows (or just the visible windows). Specify one of the XlArrangeStyle enumerated values to indicate how you want the windows arranged, and optionally, information on whether you want only visible windows arranged and how you want to synchronize the window scrolling. For example, to tile the windows within the Excel workspace, you can use code like the following:

' Visual Basic
ThisApplication.Windows.Arrange( _
  Excel.XlArrangeStyle.xlArrangeStyleTiled)

// C#
ThisApplication.Windows.Arrange( 
  Excel.XlArrangeStyle.xlArrangeStyleTiled, 
  Type.Missing, Type.Missing, Type.Missing);

If you want to programmatically create a new window, you can call the NewWindow method of the workbook, like this:

' Visual Basic
ThisWorkbook.NewWindow()

// C#
ThisWorkbook.NewWindow();

Because the NewWindow method returns a Window object, you can also write code like the following, which sets the caption for the new window and then activates it:

' Visual Basic
With ThisWorkbook.NewWindow()
    .Caption = "New Window"
    .Activate()
End With

// C#
Excel.Window wnd = ThisWorkbook.NewWindow();
wnd.Caption = "New Window";
wnd.Activate();

The Windows class provides properties and methods that control the appearance and behavior of the associated window, including colors, caption, visibility of window features, and scrolling behavior. You could write code like the following to work with a particular window's properties:

' Visual Basic
With ThisApplication.Windows(3)
    .GridlineColor = ColorTranslator.ToOle(Color.Red)
    .Caption = "A New Window"
    .DisplayHeadings = False
    .DisplayFormulas = False
    .DisplayWorkbookTabs = False
    .SplitColumn = 1
End With

// C#
wnd = ThisApplication.Windows[3];
wnd.GridlineColor = ColorTranslator.ToOle(Color.Red);
wnd.Caption = "A New Window";
wnd.DisplayHeadings = false;
wnd.DisplayFormulas = false;
wnd.DisplayWorkbookTabs = false;
wnd.SplitColumn = 1;

Tip   Although VBA and .NET work with colors using a similar paradigm—each uses a triplet of bytes containing red, green, and blue components of a color, encoded as the lower three bytes of a 32-bit integer—they handle the colors differently. You can use the System.Drawing.ColorTranslator.ToOle method to convert from a .NET color to an OLE color that's required by VBA.

Clicking Work with Windows on the Other Application Members sheet runs the sample procedure TestWindows, which contains all the code provided in small chunks throughout this section. Clicking Reset Windows in the same sheet runs the following procedure, which closes all but the first window and then maximizes that window:

' Visual Basic
Private Sub ResetWindows()
    Dim i As Integer
    For i = ThisApplication.Windows.Count To 2 Step -1
        ThisApplication.Windows(i).Close()
    Next
    ThisApplication.Windows(1).WindowState = _
      Excel.XlWindowState.xlMaximized
End Sub

// C#
private void ResetWindows()
{
    for (int i = ThisApplication.Windows.Count; i >= 2; i--)
        ThisApplication.Windows[i].Close(
          false, Type.Missing, Type.Missing);
    ThisApplication.Windows[1].WindowState = 
        Excel.XlWindowState.xlMaximized;
}

The Name Class and Names Collection

The Application object supplies its Names property, which returns a collection of Name objects. Each Name object corresponds to a named range in the Excel application. There are a number of ways to retrieve a reference to a named range—you can use the Names property of a Workbook, or of a Worksheet object, as well.

To create a new named range, use the Add method of the Names collection, as in the following fragment. The Add method accepts a number of optional parameters, in addition to the two required parameters:

' Visual Basic
Dim nm As Excel.Name
nm = ThisApplication.Names.Add( _
  "NewName", "='Other Application Members'!$A$6")

// C#
Excel.Name nm;

nm = ThisApplication.Names.Add(
    "NewName", @"='Other Application Members'!$A$6", 
    Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing);

Specify the name and location (along with other optional parameters), and you can then refer to the range in your code:

' Visual Basic 
ThisApplication.Range("NewName").Value = "Hello, World!"

// C#
ThisApplication.get_Range(
    "NewName", Type.Missing).Value2 = "Hello, World!";

To retrieve information about a named range, you can use the various properties of the Name class. The following list describes a few of the most commonly used members:

• Name returns the name assigned to the named range.
• RefersTo returns a string containing the actual target address, in standard format ("=SheetName!$B$25").
• RefersToR1C1 returns the target address, in "R1C1" format ("=SheetName!R25C2").
• Value returns a reference to the named range that resolves to the contents of the range.

Clicking the Work with Names link in the sample runs the following code, filling a region on the sheet with information about all the named ranges:

' Visual Basic
Dim nm As Excel.Name
Dim rng As Excel.Range = ThisApplication.Range("Names")
Dim i As Integer

For i = 0 To ThisApplication.Names.Count – 1
    nm = ThisApplication.Names.Item(i + 1)
    rng.Offset(i, 0).Value = nm.Name
    ' Without the leading "'", these references
    ' get evaluated, rather than displayed directly.
    rng.Offset(i, 1).Value = "'" & nm.RefersTo.ToString
    rng.Offset(i, 2).Value = "'" & nm.RefersToR1C1.ToString
    rng.Offset(i, 3).Value = nm.Value
Next i

// C#
Excel.Range rng = ThisApplication.get_Range("Names", Type.Missing);
for ( int i = 0 ; i <= ThisApplication.Names.Count - 1; i++)
{
    nm = ThisApplication.Names.Item(i + 1, 
        Type.Missing, Type.Missing);
    rng.get_Offset(i, 0).Value2 = nm.Name;
    // Without the leading "'", these references
    // get evaluated, rather than displayed directly.
    rng.get_Offset(i, 1).Value2 = "'" + nm.RefersTo.ToString();
    rng.get_Offset(i, 2).Value2 = "'" + nm.RefersToR1C1.ToString();
    rng.get_Offset(i, 3).Value2 = nm.Value;
}

Application Events

In addition to all the other methods provided by the Application class, you'll find a large group of events available. Although it's impossible to demonstrate them all in any coherent sort of way, their use is relatively clear from the names alone. The following sections describe a subset of the events, arguably the events you're most likely going to handle in your own applications.

Tip   The parameters passed to event handlers in an Office application may feel different than parameters used in native .NET events. Normally, .NET event handlers always receive an Object variable referring to the object that raised the event, and a second parameter that inherits from the EventArgs base class containing extra information about the event. There is no such well-defined event design pattern for Office applications, so each event handler accepts an arbitrary number of parameters, defined by the original developer.

Sheet Behavior

The Application object provides a range of events associated with sheets (both charts and worksheets). The following list contains information about many of those events:

• SheetActivate occurs when any sheet is activated. Excel passes the event handler an Object variable containing a reference to the activated sheet.

  Tip   As in any situation in Excel in which you're passed an Object that refers to a sheet, you'll need to cast the reference to a variable of the correct type (Worksheet or Chart, depending on the circumstances) before you can use the reference. If you've disabled the Option Strict setting in Visual Basic .NET, however, you can take advantage of late binding. You still won't be able to take advantage of IntelliSense as you type, making writing the code more difficult. All of the examples in this document that use an item within the Sheets collection cast the result as the particular type of sheets that's required, either a Worksheet or Chart, explicitly.

• SheetBeforeDoubleClick occurs when any sheet is double-clicked, before Excel provides the default double-click handling. Excel passes the event handler an Object variable containing a reference to the sheet, a Range object containing the cell nearest the location of the double-click, and a Boolean value (False by default) that allows you to cancel the default event handling. (This event won't occur on chart sheets.)

  Tip   All of the events that include the word "Before" in their name allow you to cancel the default event handling. The parameter passed to your event handler is normally named Cancel, and has a default value of False. Set this parameter to True, and Excel won't execute its default handling for the event.

• SheetBeforeRightClick occurs when any sheet is right-clicked, before Excel provides the default right-click handling. Excel passes the event handler an Object variable containing a reference to the sheet, a Range object containing the cell nearest the location of the right-click, and a Boolean value (False by default) that allows you to cancel the default event handling. (This event won't occur on chart sheets.)
• SheetCalculate occurs when any sheet is recalculated. Excel passes the event handler an Object containing a reference to the sheet that's been recalculated.
• SheetChange occurs when cells in any worksheet are changed, either by the user or by running code. Excel passes the event handler an Object variable containing a reference to the sheet and a Range variable referring to the changed range.
• SheetDeactivate occurs when any sheet is deactivated (that is, when it no longer has the focus). This event handler only runs when the focus shifts to another sheet within the same workbook. Excel passes the event handler an Object variable containing a reference to the sheet that has been deactivated.
• SheetFollowHyperlink occurs when you click any hyperlink within any workbook. Excel passes the event handler an Object variable referring to the sheet containing the link, and a Hyperlink object containing a reference to the link you clicked. (The sample project makes use of this event, providing the navigation within the sample.)
• SheetSelectionChange occurs when the selection changes on a worksheet (the event doesn't occur on chart sheets). Excel passes the event handler an Object variable referring to the sheet on which the selection changed and a Range variable that refers to the new selection. (Note that Excel doesn't pass information about the original selection, before it was changed.)

  Note   Each of the events in this section is also available as an event provided by the Workbook class. When the event is provided by the Application object, it is raised for any sheet currently open within Excel. When it's provided by the Workbook object, the event only occurs when it affects a sheet within that particular workbook. In addition, you'll find the same events provided by the Worksheet class. In that case, the event name doesn't include the word "Sheet" (for example, you'll find FollowHyperlink instead of SheetFollowHyperlink, and so on), and the event handlers aren't passed a reference to a sheet—that information is implied by the object that received the event. Otherwise, the events, their usage, and their parameters are identical to the events you see here.

Window Behavior

The Application object (and correspondingly, the Workbook object) provides a range of events that handle behavior of Window objects. The following list describes these events:

• WindowActivate occurs when any window is activated. Excel passes the event handler a Workbook object referring to the workbook that supplied the window, and a Window object that refers to the selected window. Like other activation events, this event only fires when the focus moves within Excel. Switching to another application and then back to Excel does not raise this event.
• WindowDeactivate occurs when any window is deactivated. See the WindowActivate event description for more information.
• WindowResize occurs when any workbook window is resized. Excel passes the event handler a Workbook object referring to the workbook that supplied the window and a Window object that refers to the resized window.

  Note   In the events provided by the Workbook class, the event handlers don't receive a reference to a Workbook—that information is implied by the object that raised the event.

Workbook Management

The Application object provides a range of events that occur when you interact with any Workbook object. Each of these event procedures receives a Workbook variable that indicates the particular workbook that was involved in the event. The following list describes a subset of the available events:

• NewWorkbook occurs when a new workbook is created. Excel passes the event handler a Workbook variable that refers to the new workbook. (This event is only supplied by the Application class.)
• WorkbookActivate occurs when any workbook is activated. Excel passes the event handler a Workbook variable that refers to the workbook that has been activated. (As with other "activation" events, this event only occurs when you switch from one workbook to another.)
• WorkbookBeforeClose occurs when an open workbook closes, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook that's about to close, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, keeping the workbook open).

  Warning   If you summarily set the Cancel parameter to True, without taking any conditions into consideration, no workbook will ever be able to close.

• WorkbookBeforePrint occurs when printing starts within a workbook, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook containing printed content, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, skipping the requested printing).
• WorkbookBeforeSave occurs when a workbook is saved, just before the default event handling. Excel passes the event handler a Workbook variable that refers to the workbook being saved, along with a Boolean value (False by default) that allows the event handler to cancel the default event handling (that is, canceling the save).
• WorkbookDeactivate occurs when any workbook is deactivated. Excel passes the event handler a Workbook variable that refers to the workbook that has been deactivated. (As with other "activation" events, this event only occurs when you switch from one workbook to another.)
• WorkbookNewSheet occurs when a new sheet is added to a workbook. Excel passes the event handler a Workbook variable that refers to the workbook, and an Object variable that refers to the new sheet.
• WorkbookOpen occurs when a workbook is opened. Excel passes the event handler a Workbook variable that refers to the newly opened workbook.

  Note   The Workbook class provides its own set of events, very similar to the events you've seen here. All the events that begin with "Workbook" appear in the Workbook class's list of events without that designation ("Activate" instead of "WorkbookActivate", and so on). The Workbook class event handlers don't receive a Workbook variable as a parameter; that information is implied by the object raising the event. In addition, the Workbook class provides mirrors of the other Application object events, but traps them only for a single workbook, as opposed to trapping for all workbooks. The rest of this document won't discuss events, as you've now seen the ones you're most likely to use.

The Workbook Class

As you might imagine, the Workbook class represents a single workbook within the Excel application. In this section, you'll investigate some of the members of this class, including the most-often-used properties and methods.

Tip   Many of the Application class' members show up as members of the Workbook class as well. In this case, the properties apply to a specific workbook, as opposed to applying to the active workbook. This section will discuss far fewer members than the previous section, mostly because you've already seen many of the items in question.

Properties of the Workbook Class

The Workbook class provides a huge number of properties (around 90 or so), and many deal with specific cases that most developers never think about; for example, the AutoUpdateFrequency property returns the number of minutes between automatic updates for a shared workbook, the Date1904 property returns True if the workbook uses the 1904 date system (a date serialization scheme which uses Jan 2, 1904 as the date corresponding to the value 1, common on Macintosh computers), the PasswordEncryptionAlgorithm property allows you to set the exact algorithm used for encrypting passwords, and so on.

Rather than making any attempt to be comprehensive in covering the many properties of the Workbook object, this section merely introduces the ones you're most likely to use. The general rule of thumb is this: If you need some behavior of a workbook, someone else has probably already requested it, and there's most likely a property that allows the behavior, and normally a method that provides the behavior. Check the documentation carefully before adding your own code to a workbook.

The following list describes some of the most commonly used Workbook properties:

• Name, FullName, Path (String, read-only): Each of these properties returns a different version of the workbook's name. FullName returns the full path, including the workbook file name. Name returns just the name portion and Path returns just the path portion. Clicking the Name Information link in the sample workbook runs the following code, and returns information as shown in Figure 5:
  ' Visual Basic
  ThisApplication.Range("WorkbookName").Value = _
    ThisWorkbook.Name
  ThisApplication.Range("WorkbookPath").Value = _
    ThisWorkbook.Path
  ThisApplication.Range("WorkbookFullName").Value = _
    ThisWorkbook.FullName
  
  // C#
  ThisApplication.get_Range("WorkbookName", Type.Missing).
      Value2 = ThisWorkbook.Name;
  ThisApplication.get_Range("WorkbookPath", Type.Missing).
      Value2 = ThisWorkbook.Path;
  ThisApplication.get_Range("WorkbookFullName", Type.Missing).
      Value2 = ThisWorkbook.FullName;
  

  

  Figure 5. Use Workbook properties to retrieve information about the name.

• Password (String): Gets or sets the password associated with the workbook. If you've specified a non-empty password, the HasPassword property of the workbook will return True, as well. You can retrieve the Password property, but its value is always "********". Clicking the Set Password link on the sample workbook runs the following code, which either sets or clears the workbook's password, depending on whether you've supplied text or an empty string. This example uses the form named Password from the sample project, which provides a single text box and a Password property:
  ' Visual Basic
  Private Sub SetPassword()
      Dim frm As New Password
  
      If frm.ShowDialog = DialogResult.OK Then
          ThisWorkbook.Password = frm.Password
      End If
      frm.Dispose()
  End Sub
  
  // C#
  private void SetPassword()
  {
      Password frm = new Password();
  
      if (frm.ShowDialog() == DialogResult.OK)
          ThisWorkbook.Password = frm.Value;
      frm.Dispose();
  }
  

• PrecisionAsDisplayed (Boolean): If True, Excel performs calculations using the number of decimals displayed. If False (the default value), calculations are performed using all available decimals, even if they're not all displayed. Figure 6 shows the sample workbook, with the property set to True. Each of the values in column C is a copy of a value in column B, but the numeric formatting has been set to display only two decimal places in column C. Note that with the PrecisionAsDisplayed property set to True, the sums are different, because rounding has caused the actual values to be different. If you click the PrecisionAsDisplayed = False link, the sums are the same. Clicking calls the following procedure, passing in True or False (depending on the link you click):
  ' Visual Basic
  Private Sub TestPrecisionAsDisplayed( _
    ByVal IsPrecisionAsDisplayedOn As Boolean)
      ThisWorkbook.PrecisionAsDisplayed = IsPrecisionAsDisplayedOn
  End Sub
  
  // C#
  private void TestPrecisionAsDisplayed(
      bool IsPrecisionAsDisplayedOn)
  {
      ThisWorkbook.PrecisionAsDisplayed = 
          IsPrecisionAsDisplayedOn;
  }
  

  

  Figure 6. With the PrecisionAsDisplayed property set to True, Excel uses only the visible decimals to perform its calculations.

• ReadOnly (Boolean, read-only): Returns True if the workbook was opened as read-only. You may want to take different actions in your application if you're unable to save data to the workbook.
• Saved (Boolean): Gets or sets the saved state of the workbook. If the user has made modifications to the workbook's contents or structure, the Saved property is True. Attempting to close the workbook or quit Excel will cause an alert to appear, prompting you to save the workbook (unless you've set the Application.DisplayAlerts property to False). If you set the Saved property value to False in your code, Excel will treat your workbook as if it had already been saved, and not prompt you to save it again.

Working with Document Properties

Just like the other Office applications, Excel allows you to store document properties along with the workbook. Excel provides a number of built-in properties, and you can add your own, as well. Selecting File|Properties displays the dialog box shown in Figure 7, and you can also select the Custom tab to create and modify custom properties.

Figure 7. Use this dialog box to set document properties.

Use the Workbook class BuiltInDocumentProperties property to work with built-in properties, and the CustomDocumentProperties property to work with custom properties. Each of these properties returns a DocumentProperties object, which is a collection of DocumentProperty objects. You can use the Item property of the collection to retrieve a particular property, either by name, or by index within the collection. The full list of property names is available in the Excel documentation, but there's an easy way to retrieve the list: the following procedure runs when you click the Document Properties link on the sample workbook (see Figure 8). This procedure calls the DumpPropertyCollection method to list all the built-in properties and their current values, and then repeats the process for the custom properties. In addition, the procedure modifies the Revision Number property individually, and creates a new custom property:

' Visual Basic
Private Sub DisplayDocumentProperties()
    Dim prp As Office.DocumentProperty
    Dim prps As Office.DocumentProperties

    Dim rng As Excel.Range = _
      ThisApplication.Range("DocumentProperties")
    Dim i As Integer

    Try
        ThisApplication.ScreenUpdating = False

        Try
            prps = DirectCast( _
              ThisWorkbook.BuiltinDocumentProperties, _
              Office.DocumentProperties)

            ' Set the Revision Number property:
            prp = prps.Item("Revision Number")
            prp.Value = CType(prp.Value, Integer) + 1

            ' Dump contents of the collection:
            DumpPropertyCollection(prps, rng, i)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        ' Work with custom properties:
        Try
            prps = DirectCast( _
              ThisWorkbook.CustomDocumentProperties, _
             Office.DocumentProperties)
            DumpPropertyCollection(prps, rng, i)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        ' Add a custom property:
        Try
            ' Delete the property, if it exists.
            prp = prps.Item("Project Name")
            prp.Delete()
        Catch
            ' Do nothing if you get an exception.
        End Try

        Try
            ' Add a new property.
            prp = prps.Add("Project Name", False, _
             Office.MsoDocProperties.msoPropertyTypeString, _
              "White Papers")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    Finally
        ThisApplication.ScreenUpdating = True
    End Try
End Sub

Private Sub DumpPropertyCollection( _
  ByVal prps As Office.DocumentProperties, _
  ByVal rng As Excel.Range, ByRef i As Integer)
    Dim prp As Office.DocumentProperty

    For Each prp In prps
        rng.Offset(i, 0).Value = prp.Name
        Try
            If Not prp.Value Is Nothing Then
                rng.Offset(i, 1).Value = _
                   prp.Value.ToString
            End If
        Catch
            ' Do nothing at all.
        End Try
        i += 1
    Next
End Sub

// C#
private void DisplayDocumentProperties()
{
    Office.DocumentProperty prp = null;
    Office.DocumentProperties prps = 
        (Office.DocumentProperties) 
        ThisWorkbook.BuiltinDocumentProperties;

    Excel.Range rng = ThisApplication.
        get_Range("DocumentProperties", Type.Missing);
    int i = 0;

    try 
    {
        ThisApplication.ScreenUpdating = false;

        try 
        {
            // Set the Revision Number property:
            prp = prps["Revision Number"];
      prp.Value = Convert.ToInt32(prp.Value) + 1;

            // Dump contents of the collection:
            i = DumpPropertyCollection(prps, rng, i);
        } 
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message, ThisApplication.Name);
        }

        // Work with custom properties:
        try 
        {
      prps = (Office.DocumentProperties)
        ThisWorkbook.CustomDocumentProperties;
            DumpPropertyCollection(prps, rng, i);
        } 
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message, ThisApplication.Name);
        }

        // Add a custom property:
        try 
        {
            // Delete the property, if it exists.
            prp = prps["Project Name"];
            prp.Delete();
        } 
        catch
        {
            // Do nothing if you get an exception.
        }
        try 
        {
            // Add a new property.
            prp = prps.Add("Project Name", false, 
                Office.MsoDocProperties.msoPropertyTypeString,         
                "White Papers", Type.Missing);
        } 
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message, ThisApplication.Name);
        }
    } 
    finally 
    {
        ThisApplication.ScreenUpdating = true;
    }
}

private int DumpPropertyCollection(
    Office.DocumentProperties prps, Excel.Range rng, int i) 
{
    foreach (Office.DocumentProperty prp in prps)
    {
        rng.get_Offset(i, 0).Value2 = prp.Name;
        try 
        {
            if (prp.Value != null ) 
            {
                rng.get_Offset(i, 1).Value2 = 
                    prp.Value.ToString();
            }
        } 
        catch
        {
            // Do nothing at all.
        }
        i += 1;
    }
    return i;
}

Tip   The previous code sample, DisplayDocumentProperties, used several enumerations and types from the Microsoft.Office.Core assembly. The sample code includes an Imports/using statement that sets up the text "Office" as an abbreviation for this namespace, just as it does for the "Excel" abbreviation. The project template automatically sets up the "Excel" abbreviation. You'll need to add the "Office" statement yourself.

Figure 8. Built-in document properties

Note   Although you're working with Excel and its objects here, it's Office that supplies the list of available built-in document properties, and Excel doesn't necessarily implement all the properties—attempting to access the Value property for undefined properties triggers an exception. The sample procedure includes simple exception handling to deal with this situation, should it occur.

Working with Styles

Much like a Word document, Excel workbooks allow you to apply named styles to regions within the workbook, and Excel supplies a number of predefined (although not terribly interesting) styles. You can use the Format|Styles menu item to display a dialog box that allows you to interactively modify styles, as shown in Figure 9.

Figure 9. Modify styles interactively with this dialog box.

If you click Modify on the Style dialog box, you'll display the Format Cells dialog box shown in Figure 10.

Figure 10. Use the Format Cells dialog box to modify styles.

The Format Cells dialog box displays all the options you can use when formatting cells, and each of the options available in this dialog box is available from within your code. You can use the Styles property of a Workbook object to interact with and apply styles to ranges within the workbook.

You can create, delete, and modify styles, using the Styles property of a Workbook object. Clicking Apply Style on the sample workbook runs the following procedure, which creates a new style (or uses an existing one, if you've run the code already), sets up the various aspects of the style, and applies it to a region:

' Visual Basic
Private Sub ApplyStyle()
    Const STYLE_NAME As String = "PropertyBorder"
    Dim rng As Excel.Range
    ' Get the range containing all the document properties.
    rng = GetDocPropRange()

    Dim sty As Excel.Style
    Try
        sty = ThisWorkbook.Styles(STYLE_NAME)
    Catch
        sty = ThisWorkbook.Styles.Add(STYLE_NAME)
    End Try

    sty.Font.Name = "Verdana"
    sty.Font.Size = 12
    sty.Font.Color = ColorTranslator.ToOle(Color.Blue)
    sty.Interior.Color = ColorTranslator.ToOle(Color.LightGray)
    sty.Interior.Pattern = XlPattern.xlPatternSolid
    rng.Style = STYLE_NAME

    rng.Columns.AutoFit()
End Sub

// C#
private void ApplyStyle()
{
    const String STYLE_NAME = "PropertyBorder";
    // Get the range containing all the document properties.
    Excel.Range rng = GetDocPropRange();
    Excel.Style sty;
    try
    {
        sty = ThisWorkbook.Styles[STYLE_NAME];
    }
    catch
    {
        sty = ThisWorkbook.Styles.Add(STYLE_NAME, Type.Missing);
    }

    sty.Font.Name = "Verdana";
    sty.Font.Size = 12;
    sty.Font.Color = ColorTranslator.ToOle(Color.Blue);
    sty.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
    sty.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
    rng.Style = STYLE_NAME;
    rng.Columns.AutoFit();
}

The GetDocPropRange method returns the range filled in by the document properties. This procedure uses the Range.End method to find the end of the range filled with document properties, and then creates a new range based on the upper-left corner of the range and the bottom-right corner:

' Vis