After that it operates just like the f() function, with xLiteReturn used to return a value to the calling statement.

Having returned, the xLteWorkbookFunction closes the called workbook (without saving).

My original use case for this functionality was to allow me to modularise xLite activities. So, for example, if building a dimensional model I could use a separate workbook for each dimension; isolating the dimension specific ETL logic and allowing for finer control over testing, and when in production, the run sequence for such logic (e.g. time dimension might run once a year, customer dimension code  every week).

The try it out, see the test_call_workbook_function.xls (there's also an .xlsm version) in the latest release ...

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 ...

So, is there a 'less exciting' method of loading and working with database files. Several, in fact:

Like (nearly) all xLite functions, xLite.SQL may be used as a UDF (User Defined Function - called in-cell via the = operator like normal Excel formulas) or called from a macro via Application.Run function, or as part of an XLiteScript script.  xLiteScript scripts are tables (sourced from a worksheet or SQLite) that describe a sequence of functions calls designed to perform a sequence-dependent task such as a typical ETL (Extract Transfrom & Load) operation. While it is possible to "chain" a sequence of UDFs & Excel formulas together to simulate a procedural program it can very difficult and the resulting "code" extremely fragile. Instead, the recommended method for automating xLite is to build a macro (Sub()) or an XlIteScript script that can be initiated by either a button or event (or by a single "firing" UDF).  

Avoiding having xLite functions called in a cascade of other xLite UDF calls is particularly important in the case of xLite.SQL as it breaks "the prime directive" of UDFs, the "no side effects" rule. Excel formulas and UDFs are not allowed to affect anything on a workbook except for the return value of the cell hosting the function. If you attempt to affect such an action in VBA code, the action will be ignored (or in extreme cases cause Excel to fail). The second parameter to the xLite.SQL function is a string representation of an Excel Cell address e.g. "Sheet2!B3", if the SQL is a Select statement the resulting dataset is pasted to the area of the workbook pointed to by that parameter. Through the magic of the SQLIte function "u", any VBA function (e.g. Select u('xLiteScript','Script!A1')) that also performs restricted activities can also be supported by xLite.SQL allow-side-affects functionality.

So, how does SQL circumvent this restriction? And should it? 

The xLite.SQL function when called in UDF mode (i.e via = operator in an Excel cell) hands over execution to a temporary instance of the SQLHelper class. This listens for Calculation Events on the host cell. The first such event will be triggered by the function's exit after the creation of the listening object, the created object will then execute the SQL and if a result set is produced and a 2nd parameter was specified, paste that result to the required location. It will also store a return code (OK or ABEND) or the result data itself (if a single cell select, i.e. no 2nd para) in a message-box associated with the original host cell. Finally, the listening object will reassign the same calling formula to the host cell. The effect of re-assigning the formula will be for the cell to call the xLite.SQL function again, but this time it will check the message-box for the result of the statement, destroy the listener object, and return the result to the calling cell. 

All of the above activities are perfectly "legal" even if they go against the "spirit of the law" (but then MS themselves sometimes do so too e.g. Data Tables). 99% of the time xLite.SQL caused side-effects will not be a problem, but there are times when it will fail, most likely in cases where the side-effect result of one SQL call is used to trigger a cascade of other side-effect causing xLite.SQL calls.

There are two guaranteed-no-side-effects SQL calling functions which can be used instead, xLite.xLiteSQL and xLite.xLiteCellSelect (they have the added advantage of being faster and of working in manual-calculation mode). xLite functions such as xLiteScript when used as UDFs are converted to xLite.SQL calls, others' simply refuse to run unless called by a macro or script.

So, you might ask, why bother, why not restrict xLite.SQL to no-side-effect environments like macros and scripts? Because it's so damn useful to use as an UDF. The original use-case for xLite was as a tool to enable large sets of tabular data to be investigated by a combination of the best of SQL and the best of Excel. I use the term investigated rather than analysed or transformed because a lot of a datasmith's job is about looking at datasets to determine their applicability to a given task or to track down problem data within.  Many such tasks are extreme "one-offs" with resulting work often handed over to professional IT or reporting analysts. Being able to use xLite UDFs as freely as one would use normal Excel formulas in such situations (where speed of response is often paramount) is, as the credit card ad puts it, priceless!

Click here to download a zip file, containing  xLite.xla , a test workbook test_SQL.xls and associated files.