Members that Produce Actions

Several members of the Application object perform some sort of action. ConvertFormula method

This method converts cell references in a formula between the A1 and R1C1 reference styles. It can also convert between relative and absolute references. Its syntax is:

Application.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle,

ToAbsolute, RelativeTo)

The Formula parameter is a string containing the formula to convert. It must be a valid formula, beginning with an equal sign.

The FromReferenceStyle parameter must be one of the following constants:

Enum XlReferenceStyle xlR1C1 = -4150 xlA1 = 1 End Enum

The optional ToReferenceStyle parameter is the reference style into which to convert the formula. It is also one of the XlReferenceStyle constants. If we omit this argument, the reference style is not changed.

The optional ToAbsolute parameter specifies the converted reference type and can be one of the following XlReferenceType constants:

Enum XlReferenceType xlAbsolute = 1 xlAbsRowRelColumn = 2 xlRelRowAbsColumn = 3 xlRelative = 4 End Enum

If this argument is omitted, the reference type is not changed.

Finally, the optional RelativeTo parameter is a Range object containing a single cell. This cell is used to determine relative references.; that is, we can think of the formula as being placed in this cell and so all relative references are with respect to this cell.

To illustrate, consider the following code:

Debug.Print Application.ConvertFormula(sFormula, xlA1, xlR1C1, xlRelative, Range("C3")) Debug.Print Application.ConvertFormula(sFormula, xlA1, xlR1C1, xlRelRowAbsColumn, Range("C3"))"

The second line converts from A1 notation to R1C1 notation, assuming that the formula is in cell C3. Hence, the output is:

since D2 is one column to the right and one row up from cell C3. The third line of code converts A1 notation to R1C1 notation, but uses an absolute column reference and so produces:

since column 4 is one column to the right of column 3. Evaluate method

This method converts an Excel name to an object or a value. Its syntax is:


(This method also applies to Chart, DialogSheet, and Worksheet objects.)

The Name parameter is the name of the object. It can be any of the following types of name:

An Al-style reference

Name can be any A1-style reference to a single cell. The reference is considered to be absolute. To illustrate, consider the following code, each line of which purports to place the word Mary in cell A1:

Range("A1").Value = "Mary" A1.Value = "Mary" Evaluate("A1").Value = "Mary"

The first line uses the Range method. The second line will produce an error because Excel considers A1 a variable rather than a cell reference. The third line uses the Evaluate method to convert the name of a cell to a Range object. The fourth line is shorthand for the third line.

A range

Name can be any range formed by using the range operator (colon), intersect operator (space), and union operator (comma) with references. The Evaluate method will return the corresponding Range object. To illustrate, consider the following code:

Evaluate("B2:C4").Select Evaluate("B2:C4, D5:F6").Select Evaluate("B2:C4 B1:F2").Select [B2:C4 B1:F2].Select

The first line selects the range B2:C4. The second line selects the union of the two rectangular ranges B2:C4 and D5:F6. The third line selects the intersection of the two rectangular ranges B2:C4 B1:F2. The fourth line is shorthand for the third line.

A Defined Name

Name can be any defined name. For instance, if we name a range test, then the following code selects that range:


(Incidentally, I have had some inconsistent results using the syntax [test].Select. It seems to work some but not all of the time.) We can also use formula names. For instance, the following code displays the sum of the values in cells B2 through B5:

MsgBox Evaluate("SUM(B2:B5)")

Note that external references (references to other workbooks) can be used as well, as in:


As we have seen, using square brackets is equivalent to calling the Evaluate method with a string argument. Square brackets have the advantage of producing more concise code, but they cannot be used with string variables. For instance, we can write:

Dim sFormula As String sFormula = "SUM(B2:B5)" MsgBox Evaluate(sFormula)

But the code:

MsgBox [sFormula]

will simply display the string SUM(B2:B5), as it would without the square brackets. Goto method

This method selects a given range in any workbook. (It can also select a Visual Basic procedure.) The syntax is:

Application.Goto(Reference, Scroll)

The optional Reference parameter specifies the destination. It can be a Range object, a string that contains a cell reference in R1C1-style notation, or a string that contains a Visual Basic procedure name. If the argument is omitted, the destination is the destination used in the previous call to GoTo.

The optional Scroll parameter should be set to True to scroll through the window so that the upper-left corner of the destination appears in the upper-left corner of the window. The default is False, which means the destination will not move if it was visible within the window, or else it will appear at the bottom of the window if it was not visible.

For example, to select the range B5:C6 in the active worksheet, we can write:

Application.Goto Reference:=Range("B5:C6")

Application.Goto Reference:="R5C2:R6C3"

The GoTo method also works in conjunction with the PreviousSelections array. In particular, the Application object has a PreviousSelections property that returns an array of Range objects referencing the previous four ranges selected. The syntax is:


where Index is a number between 1 and 4.

Each time the user selects a range or cell either by using the Name box or the Go To command (on the Edit menu), or the Goto method is called in code, the current range (before the action takes place) is added to the top (index 1) of the PreviousSelections array and the other items in the array are moved down one index value. (The item in position 4, of course, drops out of the array.)

As a simple illustration.

Application.Goto ActiveCell.Value Application.Goto ActiveCell.Value consider the code:

which fills the first cell on each of two sheets, using the GoTo method to add the cell ranges to the PreviousSelections array.

Now the following line will alternate between the two cells when executed repeatedly:

Application.Goto Application.PreviousSelections(1)

Note that the GoTo method differs from the Select method in several ways:

• Both methods select the given range, but the Select method does not activate the sheet upon which the new selection is made (if it is not already active).

• The Select method does not have a Scroll argument.

• The Select method does not add the current selection to the PreviousSelections array.

• The Select method has a Replace argument. Quit method

This method closes Excel. Note that the BeforeClose event will fire when the Quit method is executed. (This event has a Cancel parameter that can be set to cancel the quit operation.) We discussed workbook events (including BeforeClose) in Chapter 11.

Note that if there are any unsaved open workbooks when the Quit method is invoked, Excel will display the usual dialog box asking the user whether he or she wants to save the changes. We can prevent this either by explicitly saving all workbooks (using the Save method) before invoking the Quit method or by setting the DisplayAlerts property to False. However, in the latter case, any unsaved data will be lost without warning!

It is also important to note that Excel checks the Saved property of a workbook in order to determine whether to prompt for saving. Thus, if we set the Saved property to True but do not save the workbook, Excel will quit without prompting to save the workbook (and without saving the workbook).

0 0

Post a comment