Custom Functions

The data reduction program can be driven interactively from the console terminal or in batch mode from a command file created by the customizing function on the auxiliary functions menu. 

This customized function also circumvents the intractability problem pertinent to (19). Unlike the area function discussed previously to relate the physical properties of carbon black to the overall viscoelastic properties of the composite [229], the function introduced here is unique because it is a dimensionless quantity. Thus, it represents the property better and is devoid of any significant bias arising out of the differences in magnitudes of the constituting components. 

Data pipelining via enterprise server applications such as Pipeline Pilot (Accelrys/Scitegic), Inforsense, or the Talend (http //www.talend.com/) and KNIME (http //www.knime.org/) open source tools represent a powerful approach to integrate applications and develop custom functionality. As a specific application, Pipeline Pilot 7.0 includes a plate analytics collection for the development of complex plate-based data analysis and visualization protocols. 

You can create two different kinds of macro Sub procedures, often called command macros, and Function procedures, called function macros, custom function macros, or user-defined functions. Although these procedures can use many of the same set of VBA commands, they are distinctly different. 

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. 

As a simple first example of a Function procedure, we ll create a custom function to convert temperatures in degrees Fahrenheit to degrees Celsius. 

Function procedures can t be recorded you must type them on a module sheet. You can have several macros on the same module sheet, so if you recorded the ScientificFormat macro earlier in this chapter, you can type this custom function procedure on the same module sheet. If you do not have a module sheet available, insert one by choosing Module from the Insert menu. 

A custom function is used in a worksheet formula in exactly the same way as any of Excel s built-in functions. You can enter it in a formula by using Paste Function, or by typing it. The workbook containing the custom function must be open. 

Figure 13-11. Entering a custom function by using CONTROL+SHIFT+A.

Unfortunately, if you re entering the custom function in a different workbook than the one that contains the custom function, the function name must be entered as an external reference, e.g. PERSONAL.XLS FtoC. This can make typing the function rather cumbersome, and means that you ll probably enter the function by using Excel s Paste Fimction. But, see "Creating Add-In Function Macros" in Chapter 17 for a solution to the problem. 

Since you can t "run" a Function procedure, the only way to step through a Function procedure is to add a breakpoint, then recalculate a formula containing the custom function. 

The following sections provide some examples of more advanced custom functions. 

It is obvious that there is error in both the x and the y values. Calculation of the least-squares slope and intercept by "standard" methods is clearly not valid. In the custom function that follows, the method of Deming is used to calculate the regression parameters for the straight line y = mx + b. The Deming regression calculation assumes Gaussian distribution of errors in both x and y values and uses duplicate measurements of x values (and of y values) to estimate the standard errors. A portion of a data table is shown in Figure 17-1. 

This function could be located in the Statistical category, rather than in the User Defined category. See "Assigning a Custom Function to a Function Category" later in this chapter. 

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. 

PROVIDING A DESCRIPTION FOR A CUSTOM FUNCTION IN THE PASTE FUNCTION DIALOG BOX... 

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. 

Type the name of the custom function in the Macro Name box. 

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. 

Initially, your custom function will be located in the User Defined category in the Paste Function dialog box. You can specify the category in which the function will be located, by typing and running a Sub procedure similar to the one shown in Figure 17-7. The category values are listed in Appendix D. 

Saving a custom function as an Add-In is by far the most convenient way to use it. Here are some of the advantages ... 

An Add-In custom function is listed in the Paste Function list box without the workbook name preceding the name of the function, making it virtually indistinguishable from Excel s built-in functions. 

Your custom functions will now be indistinguishable from Excel s built-in functions. By giving them names using lower-case letters, you can distinguish them from Excel s built-in functions. 

The main advantage of using a custom function instead of worksheet formulas is minimization of errors occurring when formulas are entered. The main disadvantage is that custom functions are slower than worksheet formulas. For example, in a sheet that uses worksheet formulas to calculate the four alpha values for phosphoric acid in 0.1 pH increments from pH 0 to 14 (calculation of 564 cells) using worksheet formulas (see Chapter 20), there was no detectable delay when the sheet was recalculated. On the same PC, when the ALPHA function was used, it took three seconds to recalculate the sheet. 

The custom function ALPHA provides a convenient way to calculate alpha values for polyprotic species, and thus to construct distribution diagrams. The macro is shown in Figure 20-2. The syntax of the function is ALPHA(y, pH,... 

Instructions for MolWt is a Word document that gives more details about using the custom function. 

Alpha.xls is a custom function that returns a values for a polyprotic acid species. 

---