Where formula is a spreadsheet formula or built-in function, followed by a variable list of parameters. The passed formula can take advantage of xLite "placeholders" e.g. x("20+ :1 + :2",20,40) will return 80, x("upper(:1:)","abc") returns ABC.

The formula's text, with any placeholders replaced, is passed to Excel's Application object's Evaluate method, so not withstanding some of the limitations associated with this function (see here) it's possible to use most Excel formulas within a SQL statement.

Only built-in functions (i.e not User Defined Functions) can be called in this manner.  To call a UDF use the u() function.

When specifying the formula do not include the leading "=", this will ensure formula is evaluated inline along with the rest of the SQL statement. If preceded by "=", the formula is passed back as text. This can sometimes be useful either for testing purposes or to get around Application.Evaluate limitations.

The f() function (aka Worksheet Function) pastes the function's p1,p2,p3.. parametes to the address specified. An additional "Run ID" integer parameter is prepended to the parameter list.  This Run ID can then be referenced by the xLiteReturn() function to pass back a result value to the calling SQL statement.  This allows for the development of "Worksheet Functions", i.e. functions callable by SQL but developed using standard cascading Excel formulas.  Good for testing complex calculation workbooks and for production use where data volumes are low (very VERY slow compared to the u() or x() functions).

Two further methods of developing functions afforded by xLite are xLitePyScript and xLiteWorkbook. I'll cover xLiteWorkbook in a future post.

Unlike u(),x() and f() which are SQlite user defined functions (i.e. are called inline on SQL statement like any other SQLite function), xLitePyScript is an Excel UDF and must be wrapped by the u() function when referenced in SQL (this is likely to change in near future, a py() function?).  The address can be either a string representation of an Excel range e.g. "Sheet2!A3:Sheet2!D6" or a named range.  If the range is a single cell the function will expect to find the full Python script within, if a multi-cell range then the script will utilise cells as Python indents.

The scripts are in fact anonymous parameterless Python functions. Parameters are passed via xLite "placeholders" which are replaced before the script is passed to the Python interpreter e.g.

It this case 2nd parameter is less than 40, return 1st parameter increased by 10% otherwise return unchanged.

To see the above in action download the test_worksheet_functions.xls  from here ...