Example: The Application Object

Let's take a look at an object that is common to all programs: the Application object. The Application object refers to the application as a whole; therefore, it acts as a container for all the program's objects. However, the Application object does have a few useful properties and methods of its own, and many of these members are applicable to all the Office applications.

The next few sections take you through examples of some of the most useful Application object properties and methods.

Displaying a Message in the Status Bar

Most applications have a status bar at the bottom of the screen that's used for displaying messages and indicating the progress of the current operation. For Word and Excel, you can use the Application object's StatusBar property to display text messages in the status bar at the bottom of the screen. This gives you an easy way to keep the user informed about what a procedure is doing or how much is left to process.

Listing below demonstrates the StatusBar property

A Procedure That Displays a Message in the Status Bar

Sub StatusBarProperty()
    ActiveDocument.Save
    Application.StatusBar = ActiveDocument.Name & " was saved."
End Sub

This procedure saves the active document and then uses the StatusBar property to display a message telling the user that the document (specified with the Name property) was saved.

Tip: To clear any messages from the status bar, set the StatusBar property to the null string (""):

Application.StatusBar = ""

Changing the Title Bar Caption

The Application object's Caption property returns or sets the name that appears in the title bar of the main application window. In Excel, for example, to change the title bar caption from "Microsoft Excel" to "ACME Coyote Supplies," you would use the following statement:

Application.Caption = "ACME Coyote Supplies"

To reset the title bar to the default application name, set the Caption property to the null string (""):

Application.Caption = ""

Working with the Application Window

The application's window contains the interface elements such as the Ribbon and the status bar, as well as an area for displaying a document. In Word, PowerPoint, and Access the application window can have multiple instances (one for each open document, presentation, or database), whereas in Excel there is always just one application window (possibly with multiple open workbooks). You can wield seven Application object properties to control the application window:

Application.Height-Returns or sets the height, in points, of the application window.
Application.Left-Returns or sets the distance, in points, of the left edge of the application window from the left edge of the screen.
Application.Top-Returns or sets the distance, in points, of the top of the application window from the top of the screen.
Application.UsableHeight-The maximum height, in points, that a window can occupy within the application's window. In other words, this is the height of the application window less the vertical space taken up by the title bar, menu bar, toolbars, status bar, and so on.
Application.UsableWidth-The maximum width, in points, that a window can occupy within the application's window. This is the width of the application window less the horizontal space taken up by items such as the vertical scroll bar.
Application.Width-Returns or sets the width, in points, of the application window.
Application.WindowState-Returns or sets the state of the main application window. This property is controlled via three built-in constants that vary between applications:

Window State	Excel	Word	PowerPoint
Maximized	xlMaximized	wdWindowStateMaximize	ppWindowMaximized
Minimized	xlMinimized	wdWindowStateMinimize	ppWindowMinimized
Normal	xlNormal	wdWindowStateNormal	ppWindowNormal

Accessing an Application's Built-In Dialog Boxes

Many VBA methods are known as dialog box equivalents because they let you select the same options that are available in an application's built-in dialog boxes. Using dialog box equivalents works fine if your procedure knows which options to select, but there are times when you might want the user to specify some of the dialog box options.

For example, if your procedure prints a document (using the Application object's PrintOut method), you might need to know how many copies the user wants or how many pages to print. You could use the InputBox method to get this data, but it's usually easier to just display the Print dialog box.

The Application object has a Dialogs property, which represents the collection of all the built-in dialog boxes, each of which is a Dialog object. Note that these objects are implemented only in Word and Excel.

To reference a particular dialog box, use one of the predefined application constants. Table below lists a few of the more common ones from Word and Excel.

Some Word and Excel Built-in Dialog Box Constants

Word Constant	Excel Constant	Dialog Box
wdDialogFormatFont	xlDialogFont	Font
wdDialogFileNew	xlDialogNew	New
wdDialogFileOpen	xlDialogOpen	Open
wdDialogFilePageSetup	xlDialogPageSetup	Page Setup
wdDialogEditPasteSpecial	xlDialogPasteSpecial	Paste Special
wdDialogFilePrint	xlDialogPrint	Print
wdDialogFilePrintSetup	xlDialogPrinterSetup	Printer Setup
wdDialogFileSaveAs	xlDialogSaveAs	Save As
wdDialogInsertObject	xlDialogObject	Object
wdDialogFormatStyle	xlDialogStyle	Style
wdDialogTableSort	xlDialogSort	Sort

Note: To see a complete list of constants for Word and Excel's built-in dialog boxes, first open the Object Browser by selecting View, Object Browser (or by pressing F2). In the list of libraries, select the application (such as Excel or Word), and highlight <globals> in the Classes list. In the Member list, look for the xxDialog constants, where xx varies between applications:wdDialog for Word and xlDialog for Excel.

Note: The Object Browser is a handy tool that shows you the objects available for your procedures as well as the properties, methods, and events for each object.To display the Object Browser in the Visual Basic Editor, select View, Object Browser (you can also press F2). In the Object Browser dialog box that appears, use the Classes list to select the object you want to see and its properties, methods, and events appear in the Members list on the right.

To display any of these dialog boxes, use the Dialog object's Show method. For example, the following statement displays Excel's Print dialog box:

Application.Dialogs(xlDialogPrint).Show

If the user clicks Cancel to exit the dialog box, the Show method returns False. This means that you can use Show inside an If statement to determine what the user did:

If Not Application.Dialogs(xlDialogPrint).Show Then
    MsgBox "File was not printed"
End If

Note, too, that the Show method can take arguments. For example, Word's Show method uses the following syntax:

Dialog.Show(Timeout)

Dialog
The Dialog object you want to show.

Timeout
The time, in thousandths of a second, after which the dialog box is dismissed. (Changes made by the user are accepted.)

For example, the following statement shows the Font dialog box, and then dismisses it after approximately 10 seconds:

Application.Dialogs(wdDialogFormatFont).Show 10000

Here's the syntax for Excel's Show method:

Dialog.Show(Arg1, Arg2....)

Dialog
The Dialog object you want to show.

Arg1, Arg2,...
These arguments represent specific controls in the dialog box, and they enable you to set the value of the controls in advance.

For example, here's the syntax for Excel's Font dialog box:

Application.Dialogs(xlDialogFont).Show name_text, size_num

Here, name_text and size_num represent the Face and Size controls, respectively, in the Font dialog box. The following statement shows Excel's Font dialog box, and it sets the Face list to Garamond and the Size list to 16:

Application.Dialogs(xlDialogFont).Show "Garamond", 16

To do the same thing in Word, you use the predefined argument names as though they were properties of the specified Dialog object. For example, you use Font to return or set the Font control value in the Font dialog box:

With Dialogs(wdDialogFormatFont)
    .Font = "Garamond"
    .Show
End With

Word's Dialog object is much more flexible and powerful than Excel's in that it supports extra properties and methods. For example, the DefaultTab property enables you to specify which dialog box tab has the focus when you display a dialog box. Here's an example that displays the Layout tab in the Page Layout dialog box:

With Application.Dialogs(wdDialogFilePageSetup)
    .DefaultTab = wdDialogFilePageSetupTabLayout
    .Show
End With

Word's Dialog object also has a Display method that uses a syntax similar to that of the Show method:

Dialog.Display(Timeout)

Dialog
The Dialog object you want to show.

Timeout
The time, in thousandths of a second, after which the dialog box is dismissed.

The difference is that if you specify a Timeout value, Word does not accept the user's changes when the dialog box is dismissed after the specified time.

Another useful Dialog object method is Execute, which runs the dialog box without showing it to the user. Listing below shows an example.

A Function Procedure That Uses Word's Word Count Dialog Box to Get the Total Number of Words in the Active Document

Function CountDocumentWords() As Long
    With Dialogs(wdDialogToolsWordCount)
        .Execute
        CountDocumentWords = .Words
    End With
End Function

Sub DisplayWordCount()
    MsgBox "This document contains " & CountDocumentWords & " words."
End Sub

This procedure uses Execute to run the Word Count dialog box, and then uses the Words argument to return the number of words in the document. (Note, however, that if you have text selected before running this procedure, it will return the number of words in the selection.)

Checking Spelling

When used with the Word or Excel Application object, the CheckSpelling method checks the spelling of a single word using the following syntax (note that Word's method has a few extra arguments):

Application.CheckSpelling(word,customDictionary,ignoreUppercase)

word
The word you want to check.

customDictionary
The filename of a custom dictionary that the application can search if word wasn't found in the main dictionary.

ignoreUppercase
Set to True to tell the application to ignore words entirely in uppercase.

For example, the code shown in Listing 5.5 gets a word from the user, checks the spelling, and tells the user whether or not the word is spelled correctly.

A Procedure That Checks the Spelling of an Entered Word

Sub SpellCheckTest()
    '
    ' Get the word from the user
    '
    word2Check = InputBox("Enter a word:")
    '
    ' Spell-check it
    '
    result = Application.CheckSpelling(word2Check)
    '
    ' Display the result to the user
    '
    If result = True Then
        MsgBox "'" & word2Check & "' is spelled correctly!"
    Else
        MsgBox "Oops! '" & word2Check & "' is spelled incorrectly."
    End If
End Sub