Working with the Application Object

This chapter covers some of the major objects in the Excel object model starting with the Application object. The major objects in the Excel object model have many methods and properties, and it is beyond the scope of this book to completely describe these objects. Instead, this chapter focuses on the most commonly used methods and properties.

The Application object has the largest number of methods, properties, and events of any object in the Excel object model. The Application object is also the root object in the Excel object model hierarchy. You can access all the other objects in the object model by starting at the Application object and accessing its properties and the properties of objects it returns. The Application object also has a number of useful application-level settings.

Controlling Excel's Screen Updating Behavior

When your code is performing a set of changes to a workbook, you may want to set the ScreenUpdating property to false to prevent Excel from updating the screen while your code runs. Setting it back to TRue will refresh the screen and allow Excel to continue updating the screen.

Beyond the cosmetic benefit of not forcing the user to watch Excel change cells while your code runs, the ScreenUpdating property proves very useful for speeding up your code. Repainting the screen after each operation can be quite costly. Be sure to set this property back to true when your code is finishedotherwise, the user will be left with an Excel that does not paint. As you will see below, a try-finally block is a handy way to ensure that the property is reset even if an exception is thrown.

An even better convention to follow than just setting the ScreenUpdating property back to true is to save the value of the ScreenUpdating property before you change it and set it back to that value when you are done. An important thing to remember when doing Office development is that your code is not going to be the only code running inside of a particular Office application. Add-ins might be running, as well as other code behind other documents, and so on. You need to think about how your code might affect other code also running inside of Excel.

As an example, another add-in might be running a long operation of its own, and that add-in might have set the ScreenUpdating property to false to accelerate that operation. That add-in does an operation that triggers an event that is handled by your code. If your code sets the ScreenUpdating property to false, does something, and then sets it to TRue when it is done, you have now defeated the add-in's attempt to accelerate its own long operation because you have now turned screen updating back on. If instead you store the value of ScreenUpdating before you set it to false and later set ScreenUpdating back to its original value, you coexist better with the other code running inside of Excel.

Listing 5-1 shows an example of using the ScreenUpdating property with VSTO.

Listing 5-1. A VSTO Customization That Sets the ScreenUpdating Property

private void Sheet1_Startup(object sender, System.EventArgs e) { bool oldScreenUpdatingSetting = this.Application.ScreenUpdating; try { this.Application.ScreenUpdating = false; Random r = new Random(); for (int i = 1; i < 1000; i++) { string address = String.Format("A{0}", i); Excel.Range range = Range[address, missing]; range.Value2 = r.Next(); } } finally { this.Application.ScreenUpdating = oldScreenUpdatingSetting; } }

Controlling the Dialogs and Alerts that Excel Displays

Occasionally the code you write will cause Excel to display dialogs prompting the user to make a decision or alerting the user that something is about to occur. If you find this happening while a section of your code runs, you might want to prevent these dialog boxes from being displayed.

You can set the DisplayAlerts property to false to prevent Excel from displaying dialog boxes and messages when your code is running. Setting this property to false causes Excel to choose the default response to any dialog boxes or messages that might be shown. Be sure to get the original value of this property and set the property back to its original value after your code runs. Use try, catch, and finally blocks to ensure that you always set the property back to its original value, as shown in Listing 5-1.

Changing the Mouse Pointer

During a large operation, you might want to change the appearance of Excel's mouse pointer to an hourglass to let users know that they are waiting for something to complete. The Cursor property is a property of type XlMousePointer that allows you to change the appearance of Excel's mouse pointer. It can be set to the following values: xlDefault, xlIBeam, xlNorthwestArrow, and xlWait.

Be sure to get the original value of Cursor before changing it and set it back to its original value using try, catch, and finally blocks. Listing 5-2 shows use of the Cursor property.

Listing 5-2. A VSTO Customization That Sets the Cursor Property

private void Sheet1_Startup(object sender, System.EventArgs e) { XlMousePointer originalCursor = this.Application.Cursor; try { this.Application.Cursor = XlMousePointer.xlWait; Random r = new Random(); for (int i = 1; i < 2000; i++) { string address = String.Format("A{0}", i); Excel.Range range = this.Range[address, missing]; range.Value2 = r.Next(); } } finally { this.Application.Cursor = originalCursor; } }

Displaying a Message in Excel's Status Bar

StatusBar is a property that allows you to set the message displayed in Excel's status bar, found at the lower-left corner of the Excel window. You can set the StatusBar property to a string representing the message you want to display in the status bar. You can also set StatusBar to false to display Excel's default status bar message. If Excel is displaying the default status bar message, the StatusBar property returns a false value.

As with the other application properties in this section, you want to save the original value of the StatusBar property before changing it and be sure to set it back to its original value using TRy, catch, and finally blocks. Remember to save the value of the StatusBar property to an object variable because it can return a string or a bool value. Listing 5-3 shows an example.

Listing 5-3. A VSTO Customization That Uses the StatusBar Property to Show Progress

private void Sheet1_Startup(object sender, System.EventArgs e) { object oldValue = this.Application.StatusBar; try { Random r = new Random(); for (int i = 1; i < 2000; i++) { string address = String.Format("A{0}", i); Excel.Range range = this.Range[address, missing]; range.Value2 = r.Next(); string status = String.Format("Updating {0) of 2000...", i); this.Application.StatusBar = status; } } finally { this.Application.StatusBar = oldValue; } }

A Property You Should Never Use

Excel provides a property called EnableEvents that can be set to false to prevent Excel from raising any of its events. Although you might be tempted to use this property, don't do it. Think again about the fact that your code is almost never going to be running by itself in Excel. Other developers will be creating add-ins and code behind documents that will also be running inside Excel. By setting this property to false, you effectively break all the other code that is loaded inside of Excel until you set it back to TRue.

The problem that this property is trying to fix is the problem of your code calling a method that in turn raises an event on your code. You might not want that event to be raised because you called the method and you therefore do not want your code to be notified of something it already knows.

For example, your code might call a method such as Close on Workbook that will cause Excel to raise the BeforeClose event. If you want to prevent your BeforeClose event handler from running in this case, you have several options that are better than using EnableEvents. The first option is to stop listening to the BeforeClose event before you call the Close method. A second option is to create a guard variable that you can set before you call Close. Your event handler for BeforeClose can check that guard variable and return immediately if the guard variable is set.

Controlling the Editing Experience in Excel

Excel provides a number of properties that you can use to control the editing experience in Excel. To understand the part of the Excel editing experience that these properties control, launch an instance of Excel and create a blank worksheet. Click a cell in that worksheet and type in a number. Notice that Excel lets you type in the cell or it lets you type in the formula bar, which is shown at the top of the window. You can move the insertion point inside of the cell to further edit the contents of the cell. When you press the Enter key after editing the cell, Excel moves to the next cell down. (Your editing settings might differ, but this explanation represents the default behavior of Excel 2003.)

Excel enables you to control whether the contents of the cell can be edited directly inside the cell through the Edit Directly in Cell option in the Edit tab in the Options dialog. The EditDirectlyInCell property lets you change this setting in your code. Setting this property to false makes it so the user can only edit the contents of a cell using the formula bar.

When you press Enter after editing a cell, Excel typically moves to the cell below the cell you were editing. You can control this behavior in the Edit tab of the Options dialog. The MoveAfterReturn property and MoveAfterReturnDirection property enable you to control this behavior in your code. By setting MoveAfterReturn to true, you tell Excel to change the selected cell after the user presses Enter. MoveAfterReturnDirection controls the cell Excel moves to after the user presses Enter if MoveAfterReturn is set to TRue. MoveAfterReturnDirection can be set to a member of the XlDirection enumeration: xlDown, xlToLeft, xlToRight, or xlUp.

Controlling the Look of Excel

You can control the look of Excel through the properties listed in Table 5-1.

Controlling File and Printer Settings

You can configure the behavior when a new blank workbook is created through the SheetsInNewWorkbook property. This property takes an int value for the number of blank worksheets that should be created in a new workbook. The default is three blank worksheets. As with most of these settings, you can also set this in the General page of Excel's Options dialog.

The DefaultFilePath property corresponds to the default file location setting in the General page of Excel's Options dialog. You can set this to a string representing the file path that you want Excel to use by default when opening and saving files.

You can set the default file format you want Excel to use when saving files by using the DefaultSaveFormat property. This property is of type XlFileFormatan enumeration that has values for the various file formats Excel supports. For example, to save Excel files by default in Excel 5 format, you set this property to xlExcel5.

Another useful property when dealing with files is the RecentFiles property, which returns a collection of strings containing the names of all the recently opened files.

Properties That Return Active or Selected Objects

The Application object has a number of properties that return active objectsobjects representing things that are active or selected within Excel. Table 5-2 shows some of these properties.

Properties That Return Important Collections

The Application object is the root object of the object model and has properties that return several important collections. The Workbooks property returns the collection of open workbooks in Excel. The Windows property returns a collection representing the open windows in Excel. Both the Workbooks and Windows collections are discussed in more detail later in this chapter.

Controlling the Calculation of Workbooks

Excel provides a number of settings and methods that correspond to some of the options in the Calculation page of the Options dialog. The Application object provides a Calculation property of type XlCalculation that you can use to set Excel's calculation behavior. By default, Calculation is set to automatic calculation or xlCalculationAutomatic. You can also set Calculation to xlCalculationSemiautomatic, which means to calculate all dependent formulas except data tables. Finally, Calculation can be set to xlCalculationManual, which means Excel only recalculates the workbook when the user or your code forces a calculation.

If you have set Calculation to xlCalculationManual or xlCalculationSemiautomatic, you can force a complete recalculation of all open workbooks with the Calculate method. Using manual calculation may be another way to speed up your code if you are updating a large number of cells that are referred to by formulas. As with other application-level properties, you should restore the original value of the property in a finally block, as shown earlier in this chapter.

Using Built-In Excel Functions in Your Code

The WorksheetFunction property returns a WorksheetFunction object that enables you to call the built-in Excel formulas from your code. It provides access to more than 180 formulas. Listing 5-4 illustrates three of them.

Listing 5-4. A VSTO Customization That Uses the WorksheetFunction Object

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.WorksheetFunction func = this.Application.WorksheetFunction; double result = func.Acos(.1); double result2 = func.Atan2(.1, .2); double result3 = func.Atanh(.1); }

Selecting and Activating a Range of Cells

Goto is a method that causes Excel to select a range of cells and activate the workbook associated with that range of cells. It takes an optional object parameter that can be either a string containing cell reference (in "Sheet1!R1C1" format) or a Range object. We talk more about cell reference formats such as "Sheet1!R1C1" in the section "Working with the Range Object" later in this chapter. It also takes an optional object parameter that can be set to TRue to tell Excel to scroll the window so that the selection is at the upper-left corner of the window. Listing 5-5 shows some examples of calling the Goto method.

Listing 5-5. A VSTO Customization That Uses the Goto Method

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Application app = this.Application; app.Goto("R3C3", missing); app.Goto("Sheet2!R10C5", true); app.Goto("[BOOK1.XLS]Sheet1!R4C4", true); app.Goto(this.get_Range("R8C2", missing), true); app.Goto(this.get_Range("R1C1", "R20C3"), true); }

Spell Checking

Excel provides a method called CheckSpelling that you can use to check the spelling of a single word. It takes a required string parameter containing the word to check. It also takes an optional object parameter that can be set to a string for the filename of the custom dictionary to use. Finally, it takes an optional object parameter that can be set to true to ignore uppercase words when spell checking. CheckSpelling returns false if the word passed to it is misspelled. Listing 5-6 shows an example of calling the CheckSpelling method.

Listing 5-6. A VSTO Customization That Uses the CheckSpelling Method

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Application app = this.Application; if (!app.CheckSpelling("funtastic", missing, missing)) { MessageBox.Show("Funtastic was not spelled correctly".); } if (!app.CheckSpelling("fantastic", missing, missing)) { MessageBox.Show("Fantastic was not spelled correctly."); } if (!app.CheckSpelling("FUNTASTIC", missing, true)) { MessageBox.Show("FUNTASTIC was not spelled correctly."); } }

Sending a Workbook in E-mail

Excel provides a simple way to send a workbook as an e-mail message using three methods called MailLogon, Workbook.SendMail, and MailLogoff. MailLogon logs on to the mail system and takes the username as a string, the user's password as a string, and whether to download new mail immediately as a bool. It is also important to check the MailSession property to make sure that a mail session is not already established. If MailSession is not null, you do not need to call the MailLogon method. Workbook's SendMail method takes the recipients as a required string if there is only one recipient or as an array of strings if there are multiple recipients. It also takes a subject for the message as a string and whether to request a read receipt as a bool. Listing 5-7 shows a simple example that mails a workbook.

Listing 5-7. A VSTO Customization That Mails a Workbook

private void ThisWorkbook_Startup(object sender, EventArgs e) { Excel.Application app = this.Application; if (app.MailSession == null) { app.MailLogon(@"DOMAINJOHN", @"JOHN", missing); } this.SendMail(@"bar@domain.com", "Test message", missing); app.MailLogoff(); }

Quitting Excel

You can use the Quit method to exit Excel. If any unsaved workbooks are open, Excel prompts the user to save each unsaved workbook. You can suppress the prompts by setting the DisplayAlerts property to false, which causes Excel to quit without saving workbooks. You can also check the Workbook.Saved property on each workbook and call Workbook.Save to save each unsaved workbook. Remember that when users are prompted to save, they get a dialog box that looks like the one shown in Figure 5-1. If the user clicks the Cancel button or if any code is running that handles the BeforeClose event and sets the cancel parameter to TRue, Excel will not quit.

Figure 5-1. Excel prompts when you call Quit and a workbook needs to be saved.

Undo in Excel

Excel has an Undo method that can be used to undo the last few actions taken by the user. However, Excel does not support undoing actions taken by your code. As soon as your code touches the object model, Excel clears the undo history and it does not add any of the actions your code performs to the undo history.

Sending Keyboard Commands to Excel

Excel provides a method called SendKeys that you can use as a last resort when you cannot find a way to accomplish a command through the object model but you know how to accomplish it through a keyboard command. It takes the keys you want to send to the application as a string and a Wait parameter that if set to true causes Excel to wait for the keystrokes to be processed by Excel before returning control to your code. You can specify modifier keys like Alt, Ctrl, and Shift by prefacing the keystroke you want to send by another character. For example, to send an Alt+T key command, you call SendKeys("%t", Type.Missing), because % is the symbol SendKeys recognizes as Alt. The symbol SendKeys recognizes as Ctrl is ^ and Shift is +. In addition, special strings correspond to keys such as the down arrow. To send a down-arrow keystroke to Excel, you call SendKeys("{DOWN}", Type.Missing). Table 5-3 lists the other special strings that correspond to common keys.