Application Object

The Application object is the root object of the Excel object model. All the other objects in the Excel object model can only be accessed through the Application object. Many objects, however, are globally available. For example, the ActiveSheet property of the Application object is also available globally. That means that the active worksheet can be accessed in at least two ways: Application.ActiveSheet and ActiveSheet.

The Application object holds most of the application-level attributes that can be set through the Options menu in Excel. For example, the DefaultFilePath is equivalent to the Default File Location text box in the Save section of the Excel Options dialog box.

Many of the Application object's properties and methods are equivalent to things that can be set with the Options dialog box.

The Application object is also used when automating Excel from another application, such as Word. The CreateObject function, the GetObject function, or the New keyword can be used to create a new instance of an Excel Application object from another application. Please refer to Chapter 18 for examples of automation from another application.

The Application object can also expose events. However, Application events are not automatically available for use. The following three steps must be completed before Application events can be used:

1. Create a new class module (perhaps called cAppObject) and declare a Public object variable in a class (perhaps called AppExcel) to respond to events. For example:

Public WithEvents AppExcel As Excel.Application

□ Now the Application object events will be available in the class for the AppExcel object variable.

2. Write the appropriate event handling code in the class. For example, if you wanted a message to appear whenever a worksheet was activated, you could write the following:

Private Sub AppExcel_SheetActivate(ByVal Sh As Object) 'display worksheet name

MsgBox "The " & Sh.Name & " sheet has just been activated." End Sub

3. Finally, in a procedure in a standard module, instantiate the class created in the previous step with a current Application object:

Private App As New cAppObject 'class with the above code snippets Sub AttachEvents()

Set App.AppExcel = Application End Sub

The EnableEvents property of the Application object must also be set to True for events to be triggered at the appropriate time.

0 0

Post a comment