Example: The Application Object You'll be seeing plenty of objects when you turn your attention to the Microsoft Office programs in Part 2, "Putting VBA to Work." For now, though, 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 of 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. Properties of the Application Object The Application object has dozens of properties that affect a number of aspects of the program's environment. For starters, any control in the application's Options dialog box (select Tools, Options) has an equivalent Application object property. For example, the StatusBar property in Word and Excel takes a True or False value that toggles the status bar on or off. Here's a rundown of a few other Application object properties you'll use most often in your VBA code: Application.ActivePrinter ” Returns or sets the name of the application's current printer driver. ActiveWindow ” Returns a Window object that represents the window that currently has the focus. Application.Caption ” 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"  | The application restores the DisplayAlerts property to its default state (True) when your procedure finishes. If you would prefer to turn the alerts back on before then, set the DisplayAlerts property to True. |
Application.Dialogs ” Returns the collection of all the application's built-in dialog boxes. See Chapter 12 to learn how to display these dialog boxes from your VBA procedures. Application.DisplayAlerts ” Determines whether or not the application displays alert dialog boxes. For example, if your code deletes an Excel worksheet, Excel normally displays an alert box asking you to confirm the deletion. To suppress this alert box and force Excel to accept the default action (which is, in this case, deleting the sheet), set the DisplayAlerts property to False. 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. Path ” Returns the path of the Application object. In other words, it tells you the drive and folder where the application's executable file resides (such as C:\Program Files\Microsoft Office\Office). Note that the returned path does not include a trailing back slash (\). Application.ScreenUpdating ” Returns or sets the application's screen updating. When ScreenUpdating is set to True (the default), the user sees the results of all your code actions: cut-and-paste operations, drawing objects added or deleted, formatting, and so on. Applications look more professional (and are noticeably faster) if the user just sees the end result of all these actions. To do this, turn off screen updating (by setting the ScreenUpdating property to False), perform the actions, and turn screen updating back on. 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.Version ” Returns the version number of the application. Application.Visible ” A Boolean value that either hides the application (False) or displays the application (True). Application.Width ” Returns or sets the width, in points, of the application window. Application.Windows ” The collection of all the application's open Window objects. 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 | Methods of the Application Object The Application object features a few dozen methods that perform actions on the program's environment. Here's a summary of the most common methods: Application.CheckSpelling ”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.3 gets a word from the user, checks the spelling, and tells the user whether or not the word is spelled correctly. (You also can use this property with a Document , Worksheet , or Range object, as described in Chapter 7, "Programming Word," and 8, "Programming Excel." Also, see Chapter 12 to learn more about the InputBox function.) Listing 5.3. 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 Application.EnableCancelKey ” This property controls what the application does when the user presses Esc (or Ctrl+Break), which, under normal circumstances, interrupts the running procedure. If you don't want the user to interrupt a critical section of code, you can disable the Esc key (and Ctrl+Break) by disabling the EnableCancelKey property. (In Word, for example, you do this by setting the property to wdCancelDisabled .) To restore interrupts to their default state, enable the EnableCancelKey property. caution  | Wield the EnableCancelKey property with care. If you disable the Esc key and your code ends up in an infinite loop (see Chapter 6, "Controlling Your VBA Code"), there's no way to shut down the procedure short of shutting down the application itself. Therefore, while you're testing and building your application, you should always make sure the EnableCancelKey property is set to True. |
Application.Help ” Displays the application's Help system. Application.Quit ” Quits the application. If there are any open documents with unsaved changes, the application will ask if you want to save the changes. To prevent this, either save the documents before running the Quit method (I tell you how to do this in the appropriate chapters in Part 2), or set the DisplayAlerts property to False. (In the latter case, note that the application will not save changes to the documents. Also, Word's version of Quit accepts an argument that specifies whether or not to save changes.) Application.Repeat ” Repeats the user's last action. This is equivalent to selecting the Edit, Repeat command. | 