Working with the Application Object





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 describe these objects completely. 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 finished; otherwise, 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 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 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 the ScreenUpdating property to true when it is done, you have defeated the add-in's attempt to accelerate its own long operation because you have 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 Excel.

Listing 5.1 shows an example of using the ScreenUpdating property with VSTO.

Note

Because it is important that you set ScreenUpdating back to its original value after your code runs, you should use Visual Basic's support for exception handling to ensure that even if an exception occurs in your code, ScreenUpdating will be set back to its original value.

Visual Basic supports TRy, Catch, and Finally blocks to deal with exceptions. You should put the code to set ScreenUpdating back to its original value in your Finally block because this code will run both when an exception occurs and when no exception occurs.


A VSTO Customization That Sets the ScreenUpdating Property

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim oldScreenUpdatingSetting As Boolean
    oldScreenUpdatingSetting = Me.Application.ScreenUpdating

    Try
      Me.Application.ScreenUpdating = False
      Dim r As Random = New Random()

      Dim i As Integer
      For i = 1 To 1000
        Dim address As String = String.Format("A{0}", i)
        Dim xlRange As Excel.Range = Range(address)
        xlRange.Value2 = r.Next()
      Next
    Finally
      Me.Application.ScreenUpdating = oldScreenUpdatingSetting
    End Try

  End Sub

End Class

Controlling the Dialog Boxes and Alerts That Excel Displays

Occasionally, the code you write will cause Excel to display dialog boxes 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 the use of the Cursor property.

A VSTO Customization That Sets the Cursor Property

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim originalCursor As Excel.XlMousePointer
    originalCursor = Me.Application.Cursor

    Try
      Me.Application.Cursor = Excel.XlMousePointer.xlWait
      Dim r As Random = New Random()
      Dim i As Integer
      For i = 1 To 2000
        Dim address As String = String.Format("A{0}", i)
        Dim xlRange As Excel.Range = Me.Range(address)
        xlRange.Value2 = r.Next()
      Next
    Finally
      Me.Application.Cursor = originalCursor
    End Try

  End Sub

End Class

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, located in the bottom-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 Boolean value. Listing 5.3 shows an example.

A VSTO Customization That Uses the StatusBar Property to Show Progress

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim oldValue As Object = Me.Application.StatusBar

    Try
      Dim r As Random = New Random()
      Dim i As Integer
      For i = 1 To 2000
        Dim address As String = String.Format("A{0}", i)
        Dim xlRange As Excel.Range = Me.Range(address)
        xlRange.Value2 = r.Next()
        Dim status As String = String.Format( _
          "Updating {0} of 2000...", i)
        Me.Application.StatusBar = status
      Next
     Finally
       Me.Application.StatusBar = oldValue
     End Try

  End Sub

End Class

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 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; therefore, you do not want your code to be notified of something it already knows.

Your code might call a method such as Close on Workbook that will cause Excel to raise the BeforeClose event, for example. 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. 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 a number. Notice that Excel lets you type in the cell or in the formula bar at the top of the window. You can move the insertion point inside the cell to edit the contents of the cell further. 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 of the Options dialog box. The EditDirectlyInCell property lets you change this setting in your code. Setting this property to False makes it so the user can edit the contents of a cell only 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 box. 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 Figure.

Properties That Control Elements of the Excel User Interface

Property Name

Type

What It Does

DisplayFormulaBar

Boolean

Controls whether Excel displays the formula bar.

DisplayFullScreen

Boolean

Shows Excel in full-screen mode.

DisplayScrollBars

Boolean

Controls whether Excel displays the horizontal and vertical scroll bars for workbooks.

DisplayStatusBar

Boolean

Controls whether Excel displays the status bar in the bottom-left corner of the Excel window.

Height

Double

Sets the height in pixels of the main Excel window when WindowState is set to XlWindowState.xlNormal.

Left

Double

Sets the left position in pixels of the main Excel window when WindowState is set to XlWindowState.xlNormal.

ShowToolTips

Boolean

Controls whether Excel shows tooltips for toolbar buttons.

ShowWindowsInTaskbar

Boolean

Controls whether Excel shows open Excel windows with one taskbar button in the Windows taskbar for each open window.

Top

Double

Sets the top position in pixels of the main Excel window when WindowState is set to XlWindowState.xlNormal.

Visible

Boolean

Sets whether the Excel application window is visible.

Width

Double

Sets the width in pixels of the main Excel window when WindowState is set to XlWindowState.xlNormal.

WindowState

XlWindow-State

Sets whether the main Excel window is minimized (xlMinimized), maximized (xlMaximized), or normal (xlNormal). The Width, Height, Top, and Left settings work only when WindowState is set to XlWindowState.xlNormal.


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 Integer 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 property in the General tab of Excel's Options dialog box.

The DefaultFilePath property corresponds to the default file location setting in the General tab of Excel's Options dialog box. 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. To save Excel files by default in Excel 5 format, for example, 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. Figure shows some of these properties.

Application Properties That Return Active Objects

Property Name

Type

What It Does

ActiveCell

Range

Returns the top-left cell of the active selection in the active window. If there isn't a worksheet with an active cell, or if no workbooks are open, this property throws an exception.

ActiveChart

Chart

Returns the active chart sheet. If no chart sheet is active, this property returns Nothing.

ActiveSheet

Object

Returns the active worksheet or a chart sheet. The Object returned can be cast to either a Worksheet or a Chart.

ActiveWindow

Window

Returns the active Window. If no windows are open, this property returns Nothing.

ActiveWorkbook

Workbook

Returns the workbook that is associated with the active window. If no workbooks are open, this property returns Nothing.

Charts

Sheets

Returns all the chart sheets in the active workbook. If no workbooks are open, this property returns Nothing.

Names

Names

Returns all the names associated with the active workbook.

Selection

Object

Returns the current selection in the active window. This can return a Range when cells are selected. If other elements are selected (such as a chart or an autoshape), it can return other types. You can use the Is TypeOf operators in Visual Basic to determine the returned type.

Sheets

Sheets

Returns all the sheets in the active workbook. This collection can contain both worksheets and chart sheets. Objects returned from this collection can be cast to either a Worksheet or a Chart.


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 tab of the Options dialog box. 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 that Excel recalculates the workbook only 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 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.

A VSTO Customization That Uses the WorksheetFunction Object

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim func As Excel.WorksheetFunction
    func = Me.Application.WorksheetFunction
    Dim result As Double = func.Acos(0.1)
    Dim result2 As Double = func.Atan2(0.1, 0.2)
    Dim result3 As Double = func.Atanh(0.1)

  End Sub

End Class

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 a 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 top-left corner of the window. Listing 5.5 shows some examples of calling the Goto method.

A VSTO Customization That Uses the Goto Method

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim app As Excel.Application = Me.Application

    app.Goto("R3C3")
    app.Goto("Sheet2!R10C5", True)
    app.Goto(Me.Range("A1"), True)
    app.Goto(Me.Range("A1", "B2"), True)

  End Sub

End Class

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.

A VSTO Customization That Uses the CheckSpelling Method

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

    Dim app As Excel.Application = Me.Application

    If Not app.CheckSpelling("funtastic") Then
      MsgBox("Funtastic was not spelled correctly.")
    End If

    If Not app.CheckSpelling("fantastic") Then
      MsgBox("Fantastic was not spelled correctly.")
    End If

    If Not app.CheckSpelling("FUNTASTIC", _
      IgnoreUppercase:=True) Then
      MsgBox("FUNTASTIC was not spelled correctly.")
    End If

  End Sub

End Class

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 Boolean. It is also important to check the MailSession property to make sure that a mail session is not already established. If MailSession is not Nothing, 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 Boolean. Listing 5.7 shows a simple example that mails a workbook.

A VSTO Customization That Mails a Workbook

Public Class ThisWorkbook

  Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup

     Dim app As Excel.Application = Me.Application

     If app.MailSession Is Nothing Then
       app.MailLogon("DOMAIN\JOHN", "JOHN")
     End If
     Me.SendMail("[email protected]", "Test message")
     app.MailLogoff()

   End Sub

End Class

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.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.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. Excel does not support undoing actions taken by your code, however. As soon as your code touches the object model, Excel clears the undo history, and it does not add to the undo history any of the actions your code performs.

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 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 with another character. To send an Alt+T key command, for example, you call SendKeys("%t") 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}"). Figure lists the other special strings that correspond to common keys.

Codes Used by SendKeys

Key

Key Code

Backspace

{BACKSPACE} or {BS}

Break

{BREAK}

Caps Lock

{CAPSLOCK}

Clear

{CLEAR}

Delete or Del

{DELETE} or {DEL}

Down arrow

{DOWN}

End

{END}

Enter

~ (tilde)

Enter (numeric keypad)

{ENTER}

Esc

{ESCAPE} or {ESC}

F1 through F15

{F1} through {F15}

Help

{HELP}

Home

{HOME}

Ins

{INSERT}

Left arrow

{LEFT}

Num Lock

{NUMLOCK}

Page Down

{PGDN}

Page Up

{PGUP}

Return

{RETURN}

Right arrow

{RIGHT}

Scroll Lock

{SCROLLLOCK}

Tab

{TAB}

Up arrow

{UP}




 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows