The Application object represents the entire Microsoft Excel application. By accessing this object we can get and set basic information about the Excel application itself, display or hide components, show or hide alerts and notifications, and many other useful activities such as capturing key stroke or setting time dependencies (wait, timer).
How to target the object
Application
Get or set basic information
Application.UserName
Returns or sets the name of the current Excel Application user. That’s the licensed name specified under File -> Options -> General.
MsgBox (Application.UserName)
Application.Caption
It changes the default Excel caption next to your file name to any text we want.
Application.Caption = "Excel Macro Class"
Application.Name
Returns the name of the application (Excel).
MsgBox (Application.Name)
Application.Version
Returns the Excel Application version the system is currently using.
Display or hide components
Application.Visible
Shows or hides the whole Excel Application. This can be useful when using a secondary file as reference or data recipient, or to restrict user access and only allow input through a user form for example.
Application.DisplayFullScreen
It shows the Excel spreadsheet full screen view when set to TRUE. The full screen shows only the file name, headings and sheets, it hides the ribbon, formula bar, status bar, etc.
Application.DisplayFormulaBar
Shows or hides the Excel Formula bar.
Application.DisplayScrollBars
Application.DisplayStatusBar
Application.Cursor
It sets the type of cursor to use in Excel to one of these four types. By default the cursor changes for different hovering situations while with the other three stays always the same.
Some other related elements such as the headings or gridlines are not changed for the entire application but for a specific window/workbook instead using the Window object. The following example hides the headings and gridlines in the active window.
Show or hide alerts and notifications
Application.DisplayAlerts
When set to FALSE avoids certain alerts and messages that Excel displays while running a macro. Excel sets this property to TRUE when the code is finished. The following code avoids the pop up alert when a workbook is closed without saving in the macro.
Application.AskToUpdateLinks
When set to TRUE, avoids the Update Links warning that pops up when opening files with references to other workbooks.
Application.EnableEvents
Enables or disables Events for a particular object to happen. When set to FALSE, it will also disable any Event procedures for the Workbook (Thisworkbook) and Sheet objects.
Application.ScreenUpdating
Shows the changes in the screen while a macro is running if set to TRUE. In many situations you may not want to show the process but just the final result. In that case it is wise to set the screen updating to FALSE for better user experience and macro performance.
Application.Calculation
Sets the calculation mode in Excel to one of the three options below. The mode is set to automatic by default, but sometimes we may need to change it to manual for performance reasons, when dealing with many formulas for example. When set to manual, the method Calculate (Application.Calculate) can be used to immediately calculate all existing formulas.
Other useful members of the Application object
Application.OnKey
Assigns a specified procedure when a particular key is pressed. In this example, MacroProcedure1 will run when pressing the letter m in the keyboard.
Application.OnKey "m","MacroProcedure1"
The key needs to be released when no longer needed as follows.
Application.OnKey "m"
Application.OnTime
Schedules a procedure to be run at a specified time in the future or after a specific amount of time has passed. The example below will run the ‘MacroProcedure1’ subroutine at 17:00.
Application.OnTime TimeValue("17:00:00"), "MacroProcedure1"
In this other example, the procedure will run 10 minutes after the code is executed.
Application.OnTime Now + TimeValue("00:10:00"), "MacroProcedure1"
Application.Wait
Pauses a running macro until a specified
time. During that time the application is frozen and users cannot interact –
the mouse turns to the wait symbol
In this example it waits until 18:00.
Application.Wait "18:00:00"
In this other example it waits for 10 seconds
Application.Wait (Now + TimeValue("0:00:10"))
Application.Speech
Produces a voice message of the specified string.
Application.Speech.Speak ("Hello")
NOTE:
Some other commonly used members of the Application object have not been included here as they are fully qualified and do not require a reference to the parent. That’s the case of Workbooks, Worksheets, Range, etc, which are covered in separate sections.
The full list of members of the Application object can be found in the Excel VBA Object Browser using the search option (see below).
Other VBA Objects
No comments:
Post a Comment