Custom function arguments

Function procedures augment Excel s library of built-in functions. A function macro is used in a worksheet in the same way as, for example, the SORT function. It is entered in a single cell of a worksheet, performs a calculation and returns a single result (or an array result) to the cell in which it is located. For example, a custom function macro named ALPHA can be used to calculate aj, the fraction of an acid-base species in one of its protonated forms HjX at a particular pH. The function takes three arguments the pH of the solution, the range of pKa values of the weak acid and the coefficient j. This function is useful in constructing distribution diagrams, titration curves, and so on. 

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. 

Many of Excel s worksheet functions can accept an indefinite number of arguments. The SUM function is an example of such a function its syntax is =SUM(number1, number2,...). To create a custom function with an indefinite number of arguments, use the ParamArray keyword. An argument declared with ParamArray is an array of Variants, and each element of the array can itself be an array. 

FINEST(E4 E13,(A4 A13,C4 C13),1,1) returns the REF error message. You could rearrange the worksheet to have all the columns of x values adjacent, but with the custom function shown in Figure 17-5 you can combine the separated ranges into a single array and use it as the known x s argument of FINEST. 

When you use Paste Function to enter a function, the function s description appears at the bottom of the dialog box. For a custom function, the default description is "Choose the Help button for Help cn this function and its arguments", but you can provide a custom description for your custom function. There are two ways to do this you can use the Macro dialog box (normally used only for Sub procedures), or you can write and run a simple one-line VBA Sub procedure. Either way, the description becomes part of Excel and does not need to be entered each time you start Excel. 

For Excel s built-in functions, the Formula Palette (Step 2 of Paste Function) provides help information about each argument as you begin to enter it. There s no way to provide similar information about arguments in the Formula Palette for custom functions. But since the same description text appears in the Step 1 and the Step 2 dialog boxes, you can provide information about the arguments in the description. Unfortunately the limit for Description is 255 characters. You can provide line breaks in the text by using Chr(lO) or Chr(13), but the Formula Palette can only display two lines of text. 

A CUSTOM FUNCTION THAT TAKES AN INDEFINITE NUMBER OF ARGUMENTS... 

Perl has many featines that caimot be covered in a single chapter. For example, subroutines allow one to define customized functions that take arguments and retimi a result. References allow sophisticated data structures such as lists of lists to be created. Objects allow large, complex programs to be written so that code can be reused in different contexts. Pipes and processes allow for the control of external programs, perhaps to create an automated pipeline invoking commonly used programs. 

This expression is a simple generalization of the argument deve Section 2.7. It, and its extension to functions of any number of is referred to as the chain rule . In many applications it is customs) one or more subscripts to the partial derivatives to specify the one variables that were held constant As an example, Eq. (45) becomes... 

---