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.