MS-Excel / General Formatting

Excel's Application Object

You begin, however, with the Application object. Recall that in previous tutorial, "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.

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 WorksheetFunction. 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.WorksheetFunction.Sum(Range("Sales"))

Caution: The WorksheetFunction 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.WorksheetFunction. Upper, you'll receive an error message.

Recalculating Workbooks

The Application object features a couple of methods that enable you to recalculate the open workbooks if you've turned off automatic recalculation:

  • 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 Calculate Now in the Ribbon's Formulas tab.) Note that you don't need to specify the Application object. You can just enter Calculate by itself.
    Note: A precedent is a cell that is directly or indirectly referenced in a formula.
  • 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.) Note that for this method you must specify the Application object.

Converting a String into an Object

Excel's Application object comes with an Evaluate method that converts a string into an Excel object, using the following syntax:

Evaluate(Name)
Name
A string that specifies 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 tutorial) that represents cell A1 in the active worksheet. Listing 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.

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

Pausing a Running Macro

The Application object comes with a Wait method that 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")

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 below.

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.
Key Strings to Use with the OnKey Method
KeyWhat 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 F12"{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 below.

Symbols That Represent Alt, Ctrl, and Shift in OnKey
KeyWhat 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 below 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.
  • ResetKey-This procedure resets Ctrl+Delete to its default behavior.
Procedures That Use the OnKey Method to Set and Reset a Key Combination 
Sub SetKey()
    Application.OnKey _
        Key:="^{Del}", _
        Procedure:="Chaptr08.xlsm!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.

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 p.m.:

Application.OnTime _
    EarliestTime:=TimeValue("5:00PM"), _
    Procedure:="Backup"

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"

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 the Undo or Repeat commands.

Tip: The Repeat command (shortcut key: Ctrl+Y) doesn't appear in the Ribbon or the Quick Access toolbar (which holds the Undo command).To add the Repeat command to the Quick Access toolbar, pull down the Customize Quick Access Toolbar list and then click More Commands.Make sure Popular Commands appears in the Choose Commands From list, click Repeat, click Add, and then click OK.

The OnRepeat method customizes the name of the Repeat command (that is, the text that appears when you hover the mouse pointer over the Repeat button) and specifies the procedure that runs when the user clicks Repeat. Set this property at the end of a procedure so the user can easily repeat the procedure just by clicking Repeat. Here's the syntax:

Application.OnRepeat(Text, Procedure)
Text
The name of the Repeat command.

Procedure
The procedure to run when the user clicks Repeat (this is usually 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 Undo command and specifies the procedure that runs when the user clicks Undo:

Application.OnUndo(Text, Procedure)
Text
The name of the Undo command.

Procedure
The procedure to run when the user clicks Undo.

Listing 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-that is, at the top of the module, above all the procedures to make it available to all the procedures in the module. 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).

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
[Contents] [Next]

In this tutorial:

  1. Excel Programming
  2. Excel's Application Object
  3. Manipulating Workbook Objects
  4. Dealing with Worksheet Objects
  5. Working with Range Objects