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

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