Developer’s Guide to the Excel 2010 Application Object

When writing Visual Basic for Applications (VBA) macros, you work with objects that can receive instructions. The Application object is the top-most object in the Excel object model, and it contains:

• Application-wide settings and options. Many of these options are the same ones found in the Options dialog box, available on the Backstage.

• Methods for top-level objects, such as ActiveCell and ActiveSheet.

This Visual How To shows how to perform the following tasks:

• Access common collections, such as columns, rows, and sheets

• Use active properties (such as the ActiveCell and ActiveSheet properties)

• Access the current selection

• Display dialog boxes

This Visual How To is based on Developers Guide to the Excel 2007 Application Object by Frank Rice. This article contains additional information and code samples, and in general the Excel 2007 information is also valid for Excel 2010.

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, for example from Application to Workbook to Worksheet to Cells.

Application.Workbooks(1).Worksheets(1).Cells(1,1)

You can use many of the properties and methods that return the most common user-interface objects, such as the active worksheet without the Application object qualifier. For example, instead of writing the following.

Application.ActiveSheet.Name = "Monthly Sales"

You can write the following.

ActiveSheet.Name = "Monthly Sales"

However, be careful when you use these shortcut notations that you have the correct objects selected.

There are a few instances where you must use the Application qualifier. For example, the OnTime method needs the qualifier as does the Width and Height properties of the application window. In general, properties that relate to 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 Calculate method requires the qualifier.

Collections

From the Application object, you can obtain several kinds of useful collections. This section describes the collections you can access.

Columns and Rows

To select the fourth column, you can use the Columns property.

Application.Columns(4).Select

Similarly, to select the fourth row, you can use the Rows property.

Application.Rows(4).Select

Sheets

The following code example loops through all of the worksheets in the workbook and prints any worksheets that contain data ranges. Be aware of where the Sheets collection is used as a property of the Application object.

For iSheet = 1 To Application.Sheets.Count
   If Not IsEmpty(Application.Sheets(iSheet).UsedRange) Then
      Application.Sheets(iSheet).PrintOut copies:=1
   End If
Next iSheet

Object Properties

To access the various objects in the Excel 2010 application, there are a large number of Application properties—too many to discuss here. They include ActiveCell, ActiveChart, ActiveSheet, ActiveWindow, ActiveWorkbook, RangeSelection, Selection, StatusBar, and ThisWorkbook properties. There are only a few that you frequently use. Let us start with the ActiveCell property.

ActiveCell

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 do not 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 code example changes the font formatting for the active cell. Be aware that to ensure that you are working with the correct cell, the Activate method of the Worksheets collection makes Sheet1 the active worksheet.

Worksheets("Sheet1").Activate 
With ActiveCell.Font 
   .Bold = True 
   .Italic = True 
End With

ActiveChart

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 code example uses the ActiveChart property to add a 3D-column chart to the Monthly Sales worksheet.

Charts.Add
With ActiveChart
   .ChartType = xl3DColumn
   .SetSourceData Source:=Sheets("Sheet1").Range("B3:H15")
   .Location Where:=xlLocationAsObject, Name:="Monthly Sales"
   .HasTitle = True
   .ChartTitle.Characters.Text = Monthly Sales by Category
End With

ActiveSheet

The ActiveSheet property returns a Worksheet object that represents the currently selected sheet─that is, the worksheet on top. Only one sheet in a workbook can be the active sheet. The following code example copies the active worksheet three times and puts the copies in front of Sheet1.

For numtimes = 1 To 3 
   ' Put copies in front of Sheet1. 
   ActiveWorkbook.ActiveSheet.Copy _ 
      Before:=ActiveWorkbook.Sheets("Sheet1") 
Next

ActiveWindow

The ActiveWindow property returns a Window object that represents the active window, which is the window on top. The following code example selects and prints a worksheet and then repeats the process for a second worksheet. The ScreenUpdating property is set to False to eliminate a flicker as the macro is run.

Application.ScreenUpdating = False
Sheets("Sales").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Expenses").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

ActiveWorkbook

The ActiveWorkbook property returns a Workbook object that represents the workbook in the active window, which is the window on top. This example sets the calculation mode to manual, so that the other workbooks do not calculate, and then loops through and recalculates each worksheet of the active workbook.

Dim wks As Worksheet
Application.Calculation = xlManual
For Each wks In ActiveWorkbook.Worksheets
   wks.Calculate
Next
Set wks = Nothing

RangeSelection

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. 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. The following code example displays the first three characters in a cell. You may wonder why a Name property is used twice in a row. RangeSelection.Name returns the address of the range, such as Sheet1!$A$1:$B$15, and then the range Name property returns the name of the range itself.

Range("A1").Select
MsgBox Left(ActiveWindow.RangeSelection.Name.Name, 3)

Selection

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 without an object qualifier, it is equivalent to using Application.Selection. The following code example counts the number of cells in the selection and displays the result in a message box.

Dim cell As Object 
Dim count As Integer 
count = 0 
For Each cell In Selection 
   count = count + 1 
Next cell 
MsgBox count & " item(s) selected"

ThisWorkbook

The ThisWorkbook property returns a Workbook object that represents the workbook where the current macro code is running. This property enables 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. This code example closes the workbook that contains the example code. Changes to the workbook, if any, are not saved.

ThisWorkbook.Close SaveChanges:=False

Methods

This section describes some of the methods that can be accessed from the Application object. It discusses dialog boxes and input boxes.

Dialogs

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 file from your application 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 following code 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. Be aware that the file is not opened.

Dim fileToOpen As String
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") 
If fileToOpen <> "" Then 
   MsgBox "Open " & fileToOpen 
End If

Unlike the GetOpenFilename method, the FindFile 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 code 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.

Dim bSuccess As Boolean
Msgbox "Locate the MonthlySales.xls file."
bSuccess = Application.FindFile
If Not bSuccess Then
   Msgbox "File not open."
End If

You can open these file dialog boxes, or any other common file dialog by using the Dialogs collection that is part of Excel. One of the advantages of using the Dialogs collection when you use the Show method, you can pass in arguments to modify the default behavior of the built-in dialog box. To find the arguments to set for a particular dialog box, locate the corresponding dialog box constant in the Built-In Dialog Box Argument Lists topic in the Excel Developer Reference.

To see the list of members in the Dialogs collection

1. Open a VBA module.

2. To display the Object Browser, on the View menu, click Object Browser. Or, press F2.

3. Type xlDialog in the search box.

4. Click the search button.

InputBox

The InputBox method displays a dialog box that prompts the user for a value. This method enables you to do some selective validation of the input by specifying the type of data expected from the user. This is the syntax for the method, where Prompt is the message displayed in the dialog box so you can let the user know the type of data that 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, HelpFile and HelpContextID specify a Help file and which topic to display, and Type is the return data type. Be aware that Left and Top are relative to the upper-left corner of the screen and are expressed in points. Also, if HelpFile and HelpContextID are specified, a Help button is included in the dialog box.

For Type, the default is 2 (Text). The allowable types are shown in Table 1, and include a formula, number, string, logical value, a cell reference, and error value, and a list of values.

Table 1. InputBox return data types

Value	Type
0	A formula. The formula is returned as a string. This is the only required argument.
1	A number. You can also include a formula here with values just so that it returns a value.
2	Text (a string).
4	A logical value (True or False).
8	A cell reference, as a Range object.
16	An error value, such as #N/A.
64	A list of values.

Note

If Type is 8, you must use the Set statement to assign the result to a Range object as shown in the following code example.

The following code example prompts the user for the number of copies and prints that number of the active worksheet. Be aware that the type specifies that the method is expecting a number.

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

The Frank Rice article (Developers Guide to the Excel 2007 Application Object) contains additional information about the Application object. It includes the following:

• The Run method. The Run method executes a macro or calls a function.

• Application object events. The Application object exposes several events that you can use to monitor actions for the entire Excel application.

• Turning off and on alerts. Allows you to delete or save a worksheet without prompting the user.

• Sending information to Notepad by using SendKeys. This example copies a range of data from Excel into Notepad and then saves the Notepad file.

• Scheduling a Macro to run at a specified time and interval. This example use the OnTime method of the Application object to run a procedure at a specified time or at regular time intervals.

Length: 00:10:50

• Developers Guide to the Excel 2007 Application Object

• Getting Started with VBA Development in Office 2010

• Getting Started with VBA in Excel 2010

• Developers Guide to the Excel 2007 Range Object

About the Author

Peter Gruenbaum

  Peter Gruenbaum, started out as a physicist but became a software developer, working on technologies as diverse as Tablet PCs, Augmented Reality, computer-aided design and surgical simulation. He founded SDK Bridge LLC to bring together his love of technology and writing, where he writes and teaches about technology.