Page 55 - Excel for Scientists and Engineers: Numerical Methods

32 - EXCEL: NUMERICAL METHODS

methods usually do not appear in an assignment statement (that is, no equal sign
is required). For example,
Range("A1 :El").Clear
clears the formulas and formatting in the range A1 :El.
Some useful VBA methods are listed in Table 2-7.
Table 2-7. Some Useful VBA Methods
Activate Activates an object (sheet, etc.).
Clear Clears an entire range.
Close Closes an object.
COPY Copies an object to a specified range or to the Clipboard.
cut Cuts an object to a specified range or to the Clipboard.
FillDown Copies the cell(s) in the top row into the rest of the range.
Select Selects an obiect.

Two Ways to Specify Arguments of Methods
VBA methods usually take one or more arguments. The Sort method, for
example, takes 10 arguments. The syntax of the Sort method is

object.Sort(key7, orderl, key2, order2, key3, Order3, header, ordercustom,
match Case, orientation)
The object argument is required; all other arguments are optional.
You can specify the arguments of a method in two ways. One way is to list
the arguments in order as they are specified in the preceding syntax, i.e.,
Range("A1 :El 50").Sort "Last Name", xlAscending
which sorts the data contained in the range A1 :El 50 in ascending order, using as
the sortkey the values in the column headed by the label Last Name.
xlAscending is one of many built-in constants. You can look them up in the On-
line Help or use the Recorder to provide the correct one.
In the preceding example, only the arguments key7 and Order7 were
specified; the remaining arguments are optional and are not required.
The second way is to use the name of the argument as it appears in the
preceding syntax, with the := operator, to specify the value of the argument, as in
the following:

Selection.Sort Key1 :=Range("A2"), Order1 :=xlAscending, -
Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), -
Order3:=xlDescending, Header:=xlGuess, OrderCustom:=l , -
MatchCase:=False, Orientation:=xlTopToBottom