Excel's Application Object You begin, however, with the Application object. Recall that in Chapter 5, "Working with Objects," you learned a few Application object properties and methods that are common to all VBA applications. As you can imagine, though, each application has its own unique set of properties and methods for the Application object. Excel is no exception, as you'll see in this section. caution  | The WorksheetFunctions object includes only those worksheet functions that don't duplicate an existing VBA function. For example, VBA has a UCase function that's equivalent to Excel's UPPER() worksheet function (both convert a string into uppercase). In this case, you must use VBA's UCase function in your code. If you try to use Application.WorksheetFunctions.Upper , you'll receive an error message. For a complete list of VBA functions, see Appendix B, "VBA Functions." |
Accessing Worksheet Functions VBA has dozens of functions of its own, but its collection is downright meager compared to the hundreds of worksheet functions available with Excel. If you need to access one of these worksheet functions, VBA makes them available via a property of the Application object called WorksheetFunctions . Each function works exactly as it does on a worksheet ”the only difference being that you have to append "Application." to the name of the function. For example, to run the SUM() worksheet function on the range named Sales and store the result in a variable named totalSales , you'd use the following statement: totalSales = Application.WorksheetFunctions.Sum(Range("Sales")) Methods of Excel's Application Object The Application object features a few dozen methods that perform actions on the Excel environment. Here's a summary of the most common methods:  | A precedent is a cell that is directly or indirectly referenced in a formula. |
Calculate ” Calculates all the open workbooks. Specifically, this method recalculates only those formulas with cell precedents that have changed values. (This is equivalent to pressing F9 or clicking Calc Now in the Calculation tab of the Options dialog box.) Note that you don't need to specify the Application object. You can just enter Calculate by itself. Application.CalculateFull ” Runs a full calculation of all the open workbooks. Specifically, this method recalculates every formula in each workbook, even those with cell precedents that haven't changed values. (This is equivalent to pressing Ctrl+Alt+F9.) | | The code used in this chapter's examples can be found on my Web site at the following address: http://www.mcfedries.com/ABGVBA/Chapter08.xls |
Application.DoubleClick ” Equivalent to double-clicking the current cell. If in-cell editing is activated, running this method opens the cell for editing; otherwise , running this method opens the cell's comment (if it has one) for editing. Application.Evaluate ” Converts a string into an Excel object using the following syntax: Evaluate( Name ) | Name | A string that specifies either a cell address, a range, or a defined name. | For example, Evaluate("A1") returns a Range object (that is, a cell or groups of cells ; see "Working with Range Objects," later in this chapter) that represents cell A1 in the active worksheet. Listing 8.1 shows a more elaborate example that takes the value in cell A1 (the value is "A") and the value in cell B1 (the value is "2"), concatenates them, and then uses Evaluate to display the value from cell A2. Listing 8.1. A Procedure that Tests the Evaluate Function Sub EvaluateTest() Dim columnLetter As String Dim rowNumber As String Dim cellAddress As String ' ' Activate the "Test Data" worksheet ' Worksheets("Test Data").Activate ' ' Get the value in cell A1 ' columnLetter = [A1].Value ' ' Get the value in cell B1 ' rowNumber = [B1].Value ' ' Concatenate the two values and then display the message ' cellAddress = columnLetter & rowNumber MsgBox "The value in cell " & cellAddress & " is " & _ Application.Evaluate(cellAddress) End Sub Application.Quit ” Quits Excel. If there are any open workbooks with unsaved changes, Excel will ask if you want to save the changes. To prevent this, either save the workbooks before running the Quit method (you learn how to save workbooks later in this chapter in the section titled "Manipulating Workbook Objects"), or set the Application.DisplayAlerts property to False. (In the latter case, note that Excel will not save changes to the workbooks.) Application.Wait ” Pauses a running macro until a specified time is reached. Here's the syntax: Application.Wait( Time ) | Time | The time you want to macro to resume running. | For example, if you wanted your procedure to delay for about five seconds, you would use the following statement: Application.Wait Now + TimeValue("00:00:05") See "Running a Procedure at a Specific Time," later in this chapter, to learn more about the TimeValue function. Some Event-Like Methods Excel's Application object comes with several methods that are "event-like." In other words, they respond to outside influences such as the press of a key. This section looks at four of these methods: OnKey , OnTime , OnRepeat , and OnUndo . Running a Procedure When the User Presses a Key When recording a macro, Excel enables you to assign a Ctrl+ key shortcut to a procedure. However, there are two major drawbacks to this method: -
Excel uses some Ctrl+ key combinations internally, so your choices are limited. -
It doesn't help if you would like your procedures to respond to "meaningful" keys such as Delete and Esc. To remedy these problems, use the Application object's OnKey method to run a procedure when the user presses a specific key or key combination: Application.OnKey( Key, Procedure ) | Key | The key or key combination that runs the procedure. For letters , numbers , or punctuation marks, enclose the character in quotes (for example, "a"). For other keys, see Table 8.1. | | Procedure | The name (entered as text) of the procedure to run when the user presses a key. If you enter the null string ("") for Procedure , a key is disabled. If you omit Procedure , Excel resets the key to its normal state. | Table 8.1. Key Strings to Use with the OnKey Method | Key | What to Use | | Backspace | "{BACKSPACE}" or "{BS}" | | Break | "{BREAK}" | | Caps Lock | "{CAPSLOCK}" | | Delete | "{DELETE}" or "{DEL}" | | Down arrow | "{DOWN}" | | End | "{END}" | | Enter (keypad) | "{ENTER}" | | Enter | "~" (tilde) | | Esc | "{ESCAPE}" or "{ESC}" | | Help | "{HELP}" | | Home | "{HOME}" | | Insert | "{INSERT}" | | Left arrow | "{LEFT}" | | Num Lock | "{NUMLOCK}" | | Page Down | "{PGDN}" | | Page Up | "{PGUP}" | | Right arrow | "{RIGHT}" | | Scroll Lock | "{SCROLLLOCK}" | | Tab | "{TAB}" | | Up arrow | "{UP}" | | F1 through F15 | "{F1}" through "{F15}" | You also can combine these keys with the Shift, Ctrl, and Alt keys. You just precede these codes with one or more of the codes listed in Table 8.2. Table 8.2. Symbols that Represent Alt, Ctrl, and Shift in OnKey | Key | What to Use | | Alt | % (percent) | | Ctrl | ^ (caret) | | Shift | + (plus) | For example, pressing Delete normally wipes out only a cell's contents. If you would like a quick way of deleting everything in a cell (contents, formats, comments, and so on), you could set up (for example) Ctrl+Delete to do the job. Listing 8.2 shows three procedures that accomplish this: | SetKey ” | This procedure sets up the Ctrl+Delete key combination to run the DeleteAll procedure. Notice how the Procedure argument includes the name of the workbook where the DeleteAll procedure is located; therefore, this key combination will operate in any workbook. | | DeleteAll ” | ”This procedure runs the Clear method on the currently selected cells. (See "More Range Object Methods," later in this chapter to learn about the Clear method.) | | ResetKey ” | This procedure resets Ctrl+Delete to its default behavior. | Listing 8.2. Procedures that Set and Reset a Key Combination Using the OnKey Method Sub SetKey() Application.OnKey _ Key:="^{Del}", _ Procedure:="Chaptr08.xls!DeleteAll" End Sub Sub DeleteAll() Selection.Clear End Sub Sub ResetKey() Application.OnKey _ Key:="^{Del}" End Sub Running a Procedure at a Specific Time If you need to run a procedure at a specific time, use the OnTime method: Application.OnTime( EarliestTime, Procedure, LatestTime, Schedule ) | EarliestTime | The time (and date, if necessary) you want the procedure to run. Enter a date/time serial number. | | Procedure | The name (entered as text) of the procedure to run when the EarliestTime arrives. | | LatestTime | If Excel isn't ready to run the procedure at EarliestTime (in other words, if it's not in Ready, Cut, Copy, or Find mode), it will keep trying until LatestTime arrives. If you omit LatestTime , VBA waits until Excel is ready. Enter a date/time serial number. | | Schedule | A logical value that determines whether the procedure runs at EarliestTime or not. If Schedule is True or omitted, the procedure runs. Use False to cancel a previous OnTime setting. | tip  | If you want the OnTime method to run after a specified time interval (for example, an hour from now), use Now + TimeValue( Time ) for EarliestTime (where Time is the interval you want to use). For example, the following statement schedules a procedure to run in 30 minutes: Application.OnTime _ EarliestTime:=Now + TimeValue("00:30"), _ Procedure:="Backup" |
The easiest way to enter the time serial numbers for EarliestTime and LatestTime is to use the TimeValue function: TimeValue( Time ) | Time | A string representing the time you want to use (such as "5:00PM" or "17:00"). | For example, the following formula runs a procedure called Backup at 5:00 PM: Application.OnTime _ EarliestTime:=TimeValue("5:00PM"), _ Procedure:="Backup" Running a Procedure When the User Selects Repeat or Undo Excel has a couple of event-like methods that run procedures when the user selects Edit, Repeat or Edit, Undo. The OnRepeat method customizes the name of the Edit, Repeat menu item and specifies the procedure that runs when the user selects Edit, Repeat. Set this property at the end of a procedure so the user can easily repeat the procedure just by selecting Edit, Repeat. Here's the syntax: Application.OnRepeat( Text, Procedure ) | Text | The name of the Edit, Repeat menu item. This command normally uses R as its accelerator key, so make sure that the Text argument has an ampersand (&) before the R (for example, &Repeat Formatting). | | Procedure | The procedure to run when the user selects Edit, Repeat (this will usually be the name of the procedure that contains the OnRepeat statement). | The OnUndo method is similar to OnRepeat , except that it sets the name of the Edit, Undo menu item and specifies the procedure that runs when the user selects Edit, Undo: Application.OnUndo( Text, Procedure ) | Text | The name of the Edit, Undo menu item. Note that the Undo command uses the letter U as its accelerator key. | | Procedure | The procedure to run when the user selects Edit, Undo. | Listing 8.3 shows an example that uses both OnRepeat and OnUndo . The currCell variable stores the address of the active cell. Notice that it's declared at the module-level to make it available to all the procedures in the module (see Chapter 3, "Understanding Program Variables"). The BoldAndItalic procedure makes the font of the active cell bold and italic and then sets the OnRepeat property (to run BoldAndItalic again) and the OnUndo property (to run the procedure named UndoBoldAndItalic ). Listing 8.3. Procedures that Set the OnRepeat and OnUndo Properties Dim currCell As String ' The module-level variable Sub BoldAndItalic() With ActiveCell .Font.Bold = True .Font.Italic = True currCell = .Address End With Application.OnRepeat _ Text:="&Repeat Bold and Italic", _ Procedure:="BoldAndItalic" Application.OnUndo _ Text:="&Undo Bold and Italic", _ Procedure:="UndoBoldAndItalic" End Sub Sub UndoBoldAndItalic() With Range(currCell).Font .Bold = False .Italic = False End With End Sub |
