Page 71 - Excel for Scientists and Engineers: Numerical Methods

48 EXCEL: NUMERICAL METHODS

Returning an Error Value from a Function Procedure

If, during execution, a function procedure detects an incorrect value or an
incipient error such as a potential divide-by-zero error, we need to return an error
value. You could specify a text message as the return value of the function
procedure, like this:
If (error found) Then FunctionName = "error message": Exit Function
but this is not the best way to handle an error. Use the CVErr(errorvalue)
keyword to return one of Excel's worksheet error values that Excel can handle
appropriately. For example, if a result cannot be calculated by the function, then
a #N/A error message should be returned. This is accomplished by means of the
following:
If (error found) Then FunctionName = CVErr(x1ErrNA): Exit Function
The error values are listed in Appendix 1.

A Custom Function that Takes an Optional Argument
A custom function can have optional arguments. Use the Optional keyword
in the list of arguments to declare an optional argument. The optional argument
or arguments must be last in the list of arguments.
Within the procedure, you will need to determine the presence or absence of
optional arguments by using the IsMissing keyword. As well, you will usually
need to provide a default value if an argument is omitted.

Arrays in Function Procedures
You can create Function procedures that use arrays as arguments, or return
an array of results.

A Range Passed to a Function Procedure
Can Be Used as an Array
If a range argument is passed in a function macro, the range can be treated as
an array in the VBA procedure. No Dim statement is necessary. Thus the
expression
Function MyLINEST(known-ys, known-xs)

passes the worksheet ranges known-ys and known-xs to the VBA procedure
where they can be used as arrays. A one-row or one-column reference becomes
a one-dimensional array; a rectangular range becomes a two-dimensional array
of dimensions array(rows, columns).