| Formula Overview > Custom Functions in Formulas |
Formulas may include custom, user-defined functions. If you have functions that you use on a regular basis that are not in the built-in functions or you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions. Custom functions can have up to 255 arguments.
A custom function can have the same name as a built-in function. The custom function takes priority over the built-in function. Custom functions are dynamically linked at evaluation time. Thus, the application can redefine an existing custom function.
In Spread, the HYPERLINK function is treated as a custom function since we do not have that as a built-in function. A custom function in Spread gets exported as a custom function in Excel. However, there is no way to export the implementation of the custom function. Thus, Excel sees the exported custom function as an unimplemented custom function which evaluates to the #NAME? error. When you enter or leave edit mode via the formula bar, Excel reparses the formula and recognizes the function as the built-in HYPERLINK function. Unfortunately, there is no way to tell the Spread control that a given custom function in Spread should be exported as a built-in function in Excel.
Suppose the application needs an Excel function that Spread does not support. The application would have to supply a custom function to mimic the Excel function. Spread's implementation of a custom function cannot be exported to an Excel file, so the custom function gets exported as an undefined custom function. In Excel, an undefined custom function will evaluate to the #VALUE! error. When you enter edit mode and then leave edit mode in Excel, Excel will reparse the formula (even if you made no changes to the formula). During reparsing, Excel will treat the function as the built-in function (instead of a custom function). The formula will then evaluate to the expected value (instead of the #VALUE! error). Your example of a problem formula does not appear to fall into the above described scenario because the formula only uses the MAX and SUM functions. However, it is still possible that the formula could be referencing a cell that uses a custom function which would get you back into the above described scenario. Editing the referenced cell would get rid of the #VALUE! error in the referenced cell. The recalculations would cascade back the cell in question.
Refer to the product Developer’s Guide for more details on how to create a custom function.
Refer to the product Assembly Reference for more details on the methods that add or get custom functions.
Return to the Formula Overview.