Built-in function to simulate custom functions using worksheets only.

1. Decide on a set of cells to be your inputs, and one cell to be your output. (Ex: let cells A1,B1 and C1 be the inputs, and let A2 be the output.)
2. Call the new function something like PLUGIN or CALC or APPLY or something short like F.
3. To run our example function on the values 7,8 and 9, use =F(A1,7,B1,8,C1,9,A2).
4. The result of the “function call” acts as if the actual numbers 7,8,9 were typed into cells A1,B1,C1 (respectively), and the result was read out of A2 and inserted into the current cell (that contains the “=F(…)” formula).

• The “function definition” can include many intermediate cells used in the calculation of the result cell.
• The “function” has cells chosen by convention for its inputs and outputs.
• The “function call” doesn’t actually change anything; it only runs as if the inputs/output of the “function” were changed.

Example: Function to change error values to Null; uses A1 as input, and itself as output. =IF(ISERROR(A1),Null,A1) If the above “function” were put in B1, we could use “=F(A1,your_formula_here,B1)” to return Null if the formula “your_formula_here” returned an error. For one-step functions like this one, the old way to calculate this would have been: =IF(ISERROR(your_fomula_here),Null,your_formula_here) That’s wasteful because “your_formula_here” must be duplicated. In a long formula where the result could be an error code, you could store the result in one cell and clean it up in another cell, but that clutters up the spreadsheet.

This “wish” can’t (easily) be simulated using VBA, since in writing your own worksheet function the code can’t change the contents of other cells. You’d have to copy the sheet with the “function” definition into memory and get Excel to do calculations in the temporary memory copy.