April 12, 2011, 10:43 p.m.
posted by dante
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.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"
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.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.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.
Methods of the Application Object
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):
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 Figure, "Programming Excel." Also, see Chapter 12 to learn more about the InputBox function.)
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.
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.)