Page 45 - Excel for Scientists and Engineers: Numerical Methods

22 EXCEL: NUMERICAL METHODS

Using Worksheet Functions with VBA
In addition to the 187 VBA functions, you can make use of any of Excel's
worksheet functions in your VBA code. To use one of Excel's worksheet
functions, simply use the syntax
.
.
Application. WorksheetFunctionName(argumenf~, . )
and supply arguments for the function just as you would in a worksheet. For
example, to use the SUBSTITUTE function in VBA, use the code
FormulaString = Application.Substitute(FormulaString, XRef, NewX)
to replace all occurrences, in the string contained in the variable FormulaString,
of the variable XRef with the variable NewX.

Some Useful Methods
Although most methods can only be used within Sub procedures, there are a
few methods that can be used within Function procedures. Only methods that
do not "change the appearance of the screen" can be used in Function
procedures; it should be obvious that methods like Cut, Paste, Open, Close etc.,
cannot be used in a custom function.

Table 2-5. Some Methods Applicable to the Range Object
That Can Be Used in a Function Procedure

Address Returns the reference of a cell or range, as text.
Columns Returns a Range object that represents a single
column or multiple columns.
ConvertFormula Converts cell references in a formula between Al-
and R1 C l-style, and between relative and absolute.
Evaluate Converts a formula to a value.
Intersect Returns the reference that is the intersection of two
ranges.
Rows Returns a Range object that represents a single row
or multiple rows.

Volatile Marks a user-defined function as volatile. The
function recalculates whenever calculation occurs in
any cell of the worksheet.