Developers Guide to the Excel 2007 Application Object
Summary: Learn how to extend your Microsoft Office Excel 2007 applications by programmatically manipulating the members of the Application object. Explore some of the more common operations involving the Application object with many code examples. (15 printed pages)
Frank Rice, Microsoft Corporation
Applies to: Microsoft Office Excel 2007
The Application object is the highest object in the Microsoft Office Excel 2007 object model and represents the Excel application itself. The Application object exposes information about the running application, the options applied to that instance, and the current user objects open within the instance. Because it is the topmost object within the object model, the Application object also contains many of the parts that make up a workbook, including the collections of workbooks, worksheets, cells, and the data these items contain.
The Application object contains:
Application-wide settings and options. Many of these options are the same ones found in the Options dialog box, available on the Tools menu.
Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.
In the following sections, you explore some of the more frequently used objects, methods, and properties associated with the Application object by using Microsoft Visual Basic for Applications (VBA) code examples. For more detailed information about all of the Application object model members, see Application Object Members.
You use the Application property to return the Application object. After you have a reference to the application, to access the objects under the Application object, you move down the object model hierarchy. The following example sets the first cell in a worksheet equal to 20.
To refer to the cell, the example starts at the Application object, moves to the first workbook, then to the first worksheet, and finally to the cell.
The following example creates an Excel workbook object in another application and then opens a workbook in Excel.
You can use many of the properties and methods that return the most common user-interface objects, such as the active worksheet (ActiveSheet property), without the Application object qualifier. For example, instead of writing Application.ActiveSheet.Name = "Monthly Sales", you can write ActiveSheet.Name = "Monthly Sales". However, you must be careful when using these shortcut notations so that you have the correct objects selected. If you are certain that you have the proper workbook and worksheet selected, such as by using the Activate method of the Worksheet object, you can refer to the first cell by using Cell(1, 1).
There are a few instances where you must use the Application qualifier. For example, the OnTime method, which is discussed later in this article, needs the qualifier as does the Width and Height properties of the application window. In general, properties that have to do with the appearance of the Excel window or that affect the overall behavior of the application require the Application qualifier; for example, the DisplayFormulaBar property that is used to display or hide the formula bar, and the Calculation method requires the qualifier.
This section details some of the collections related to the Application object.
The AddIns collection represents all of the add-ins currently loaded into Excel. You can list different types of information about the add-ins in your application just as you would iterate through any other collection. The following example lists the path and name of the add-ins currently loaded in Excel.
Columns and Rows Collection
These collections represent the columns and rows in the active workbook. You use them to select specific columns and rows, respectively.
This statement selects the D column just as you would by clicking the header in the user-interface.
This statement selects the fifth row in the worksheet just as if you clicked the row border.
The Dialogs collection consists of all of the dialog boxes in the application. The Dialogs collection is discussed in more detail later in this article.
The Sheets object returns a collection of all of the sheets in the specified or active workbook. The Sheets collection can contain Chart or Worksheet objects. The following example prints all of the worksheets in the active workbook.
This example loops through all of the worksheets in the workbook and prints out any worksheets that contain data ranges.
To access the various objects in the Excel 2007 application, there are a large number of properties—too many to remember or discuss here. Fortunately, there are only a few that you use frequently.
The following scenarios illustrate the use of these frequently used properties.
The ActiveCell property of the Application object returns a Range object that represents the active cell on the active worksheet on the active workbook. If you don't specify an object qualifier, this property returns the active cell in the active window.
Be careful to distinguish between the active cell and a selection of cells. The active cell is a single cell inside the current selection. The selection may contain a collection of cells, but only one is the active cell.
The following example changes the font formatting for the active cell. Notice that to ensure that you are working with the correct cell, the Activate method of the Worksheets collection makes Sheet1 the active worksheet.
The ActiveChart property returns a Chart object that represents the active chart, whether it is an embedded chart or a chart sheet. An embedded chart is considered active when it is either selected or activated. The following example uses the ActiveChart property to add a 3D-column chart to the Monthly Sales worksheet.
The ActiveSheet property returns a Worksheet object that represents the currently selected sheet (the worksheet on top). Only one sheet in a workbook can be the active sheet. The following example displays the name of the active sheet.
The following example copies the active worksheet the number of times specified by the user and places them in front of Sheet1.
The ActiveWindow property returns a Window object that represents the active window (the window on top). The following example displays the name (Caption property) of the active window.
The Caption property acts as the name for the active window allowing you to access the window by using a friendly name instead of an index number.
The following example selects and prints a worksheet and then repeats the process for a second worksheet.
Sub PrintWorksheet() Application.ScreenUpdating = False Sheets("Sales").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Expenses").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub
In this example, you may wonder why the ScreenUpdating property is set to False. As Excel performs a series of tasks, the screen is updated and refreshed many times. This results in a flickering screen. Setting the ScreenUpdating property to False eliminates this flicker. Additionally, because the computer processor does not have to pause to refresh the screen, this can make larger applications run a little faster.
The ActiveWorkbook property returns a Workbook object that represents the workbook in the active window (the window on top). This example displays the name of the active workbook.
The following example sets the calculation mode to manual (so the other workbooks do not calculate) and then loops through and calculates each worksheet of the active workbook.
The RangeSelection property returns a Range object that represents the selected cells on the worksheet in the specified window even if a graphic object is active or selected on the worksheet. This example displays the address of the selected cells on the worksheet in the active window.
When you select a range of cells, the RangeSelection property and the Selection object represent the same range of cells. When you select a graphic, the RangeSelection property returns the previous selection of cells. More information about the Selection property appears in the next section.
The following example displays the first three characters in a cell.
You may wonder why this expression uses this syntax.
When you select a range, there is the address of the range such as Sheet1!$A$1:$B$15 that is retrieved by the command RangeSelection.Name. In addition, there is the name of the range itself such as MyRange1 that is retrieved by the command RangeSelection.Name.Name.
The Selection property returns the selected object in the active window for an Application object. For example, for cells, the property returns a Range object; for charts, it returns a Chart object. If the property is used with no object qualifier, it is equivalent to using Application.Selection.
This example clears the selection on Sheet1 (assuming that the selection is a range of cells).
The following is an example that stores the total number of selected rows in the NumRows variable:
This example counts the number of cells in the selection and displays the result in a message box.
The StatusBar property returns or sets the text in the status bar. This property allows you to change the message that is displayed in the Status Bar at the bottom of the Excel window. This can be especially helpful to keep the user informed of the progress of processes that take some time to complete.
The StatusBar property returns False if Excel has control of the status bar. In addition, to restore the default status bar text, just set the property to False; this works even if the status bar is hidden.
For example, you assign the text to the StatusBar property with each file processed with the following expression.
Dim FileNum As Integer FileNum = 0 For Each file in Files ' Do something here. Application.StatusBar = "Now processing File " & FileNum FileNum = FileNum + 1 Next
Then, when the routine finishes, you return the status bar back to normal with the following statement.
You can create your own procedure to use the StatusBar property to show the progress of a macro or other process.
Sub ShowStatusBarProgress() Dim i As Long Dim pctDone As Double Dim numSquares As Long Const MAXSQR As Long = 15 For i = 1 To 30 pctDone = i / 30 numSquares = pctDone * MAXSQR Application.StatusBar = Application.Rept(Chr(31), numSquares) Application.Wait Now + TimeSerial(0, 0, 1) Next i Application.StatusBar = False End Sub
This example displays a maximum of fifteen squares as defined by the constant MAXSQR. The squares are produced by using the ASCII character 31. This example lacks the visual indication of how far along the macro is; it just shows that it is progressing. The Wait method simulates a macro that takes a long time to execute.
The ThisWorkbook property returns a Workbook object that represents the workbook where the current macro code is running. This property allows add-ins to refer to the workbook that contains the code. The ActiveWorkbook property does not work in this instance because the active workbook may not be the one that contains the add-in code. In other words, the ActiveWorkbook property does not return the add-in workbook; it returns the workbook that is calling the add-in. If you create an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.
The following example closes the workbook that contains the example code. Changes to the workbook, if any, are not saved.
The following example loops through each open workbook and closes it. It then closes the workbook that contains this code.
In addition to the Application object properties, the following discuss some of the more frequently used methods.
FindFile Method and Dialogs Collection
Unlike the GetOpenFilename method, the FileFind method displays the Open dialog box and allows the user to open a file. If a new file is opened successfully, the method returns True. If the user cancels the dialog box, this method returns False.
The following example displays a message instructing the user to open a specific file and then displays the Open dialog box. If the user cannot open the file, a message is displayed.
Sub OpenFile1( ) Dim bSuccess As Boolean Msgbox "Please locate the MonthlySales.xls file." bSuccess = Application.FindFile If Not bSuccess Then Msgbox "File not open." End If End Sub
You can also accomplish the same thing by using one of the enumerations of the Dialogs collection to open the particular dialog box. One of the advantages of using the Dialogs collection when you use the Show method is that you can pass in arguments to modify the default behavior of the built-in dialog box. For example, the arguments for xlDialogOpen are: file_text, update_links, read_only, format, prot_pwd, write_res_pwd, ignore_rorec, file_origin, custom_delimit, add_logical, editable, file_access, notify_logical, converter.
To find the arguments to set for a particular dialog box, locate the corresponding dialog box constant in "Built-In Dialog Box Argument Lists" in Excel Help.
The following example displays the Open dialog box with Book1.xlsm in the File names box, which allows the user to display the file without first selecting it.
Sub OpenFile2( ) Application.Dialogs(XlBuiltInDialog.xlDialogOpen).Show arg1:="Book1.xlsm" End Sub
The significance of the Dialogs collection is that you can use it to display any of the Excel dialog boxes—there are about 250 of them. You can see the complete list of dialog boxes in the Dialogs collection by following these steps.
To see the list of members in the Dialogs collection
Open a VBA module.
To display the Object Browser, on the View menu, click Object Browser. Alternatively, press F2.
Type xlDialog in the search box.
Click the search button.
The GetOpenFilename method displays the standard Open dialog box and gets a file name from the user without actually opening any files. The GetOpenFilename method gives you the most control over the process of opening a workbook from your applications because all it does is return the full path and file name selected by the user as a string. What you do with that information is up to you. For example, you might pass the result to the OpenText method. The syntax of the method is the following (all of the arguments are optional).
FileFilter is a string that defines the filter criteria (for example, *.txt, *.xla); FilterIndex specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter; Title specifies the title of the dialog box, ButtonText is only used for Macintosh computers; and MultiSelect is a Boolean value that specifies that multiple files can be selected.
The following example displays the Open dialog box with the filtering in the Files of Type box set to Text Files (*.txt) and then displays a message box of the user's choice. Note that the file is not opened.
As you can guess, the InputBox method displays a dialog box that prompts the user for a value. The method enables you to do some selective validation of the input by specifying the type of data expected from the user. The syntax for the InputBox method is as follows.
Prompt is the message displayed in the dialog box. Here you can let the user know the type of data you are expecting.
Title is the title displayed at top of the dialog box.
Default is the default value that is initially displayed.
Left and Top are used to specify the position of the dialog box. These values are in reference to the upper-left corner of the screen and are expressed in points.
HelpFile and HelpContextID specify a Help file. If these values are used, a Help button is included in the dialog box.
Type is the return data type. The default value is Text. The allowable types are list in Table 1.
Table 1. InputBox return data types
A formula. The formula is returned as a string. This is the only required argument.
A number. You can also include a formula here with values just so that it returns a value.
Text (a string).
A logical value (True or False).
A cell reference, as a Range object.
An error value, such as #N/A
A list of values
If Type is 8, you must use the Set statement to assign the result to a Range object as shown in the following example.
The following example prompts the user for the number of copies and prints that number of the active worksheet. Notice that the type specifies that the method is expecting a number.
Sub PrintActiveSheet() Dim TotalCopies As Long, NumCopies As Long Dim sPrompt As String, sTitle As String sPrompt = "How many copies do you want?" sTitle = "Prints the active sheet" TotalCopies = Application.InputBox(Prompt:=sPrompt, Title:=sTitle, Default:=1, Type:=1) For NumCopies = 1 To TotalCopies ActiveSheet.PrintOut Next NumCopies End Sub
The Run method executes a macro or calls a function. You can use this method to run a macro written in VBA or the Excel macro language, or to run a function in a dynamic-link library (DLL) or Excel add-in (XLL). An XLL is an add-in for Excel that you can build with any compiler that supports building DLLs. This is the syntax of the method.
Macro is the name of the macro or function to execute and Arg1 through Arg30 are any arguments that you need to pass the macro or function.
The following example uses the Run method to call a procedure that sets the font of cells in a range to Bold. Notice that you can also use the Call method to accomplish the same result.
The Application object also exposes several events that you can use to monitor actions for the entire Excel application. To use Application events, you must enable event monitoring.
To activate Application event monitoring
Create a class. On the Insert menu, click Class Module.
In the Properties pane, change the name of the class to AppEventClass.
In the code window for the class, add the following:
The Application-level events are now available to your application.
To test, in the Object list in the code window, click Apply.
In the Procedure list in the code window, click Appl_WorkbookOpen. This inserts a placeholder procedure for the Appl_WorkbookOpen event.
Replace the procedure with the following:
Repeat the process to insert the Appl_WorkbookBeforeClose event. Replace the placeholder code with the following:
Next, create a variable that you use to refer to the Application object that you created in the class module. In the Project pane of the VBA Project, double-click the ThisWorkbook node to the open the code window.
Add the following statement.
Create a connection from the declared object to the Application object by adding the following procedure to the ThisWorkbook code window.
Save and close the workbook.
Now test the code by opening the workbook. Notice the dialog box is displayed for the Appl_WorkbookOpen event.
Close the workbook and notice that the dialog box is displayed for the Appl_WorkbookBeforeClose event.
Switching back to the AppEventClass class module and clicking the Procedure list displays a number of events you can use to monitor the actions in your application.
Understanding how these events are triggered and in what sequence is important to understand your application. Add other events to the class and insert message boxes, and then try out various actions to see when particular events are triggered.
In addition to the most frequently used objects of the Application object, there are other tasks that you may want to do in your Excel applications. The following sections discuss some of these tasks.
Deleting a Worksheet Without Saving It
In the following example, you first turn off any messages asking if you want to save the worksheet. Next, you delete the worksheet and turn the alert message back on.
Saving a Worksheet Without Prompting the User
This example saves the worksheet without notifying the user.
Sending Information to Notepad by Using SendKeys
The following example uses the SendKeys commands to copy a range of data from Excel into Notepad and then save the Notepad file.
Sub SKeys() Range("A1:D15").Copy ' Copy the range. SendKeys "% n", True ' Minimize Excel. Shell "notepad.exe", vbNormalFocus ' Start Notepad. SendKeys "^V", True ' Past the range data into Notepad. SendKeys "%FA", True ' Specify SaveAs. SendKeys "SalesData.txt", True ' Provide a file name. SendKeys "%S", True ' Save the file. Close notepad End Sub
The example first copies a range of data to the Clipboard. It then minimizes Excel, starts Notepad, and then copies the data from Clipboard into Notepad. Next, it specifies a file name and saves the file. Finally, it closes Notepad.
Scheduling a Macro to Run at a Specified Time and Interval
You can use the OnTime method of the Application object to run a procedure at a specified time or at regular time intervals. The syntax for the OnTime method is as follows.
The EarliestTime variable specifies when you want the procedure named by the Procedure variable to run. The optional LatestTime and Schedule variables specify the latest time for the procedure to run and whether to schedule a new procedure to run or cancel an existing one. The LatestTime variable is useful if you need to specify a range of time to call the procedure if Excel is busy when you initially call.
The following example specifies that a procedure named YourProc to run every five minutes.
The following example runs the procedure every day at noon.
The following example schedules a call to the AutoSave procedure every five minutes. If you close the workbook, the CleanUp procedure is called to do whatever cleanup you might want and to cancel any additional calls.
Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:05:00"), "AutoSave" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime Now + TimeValue("00:05:00"), "CleanUp", , False End Sub
The Workbook_Open and Workbook_BeforeClose events are contained in the Workbook code module. The AutoSave and CleanUp procedures should reside in a standard code module.
This article touches on just a few of the members of the Application object. There are other members that enable you to change the way your users interact with your Excel applications and also change the look and feel of your applications. Becoming familiar with the Application object lets you extend and adapt the functionality of Excel to your own needs.