I’ve just released another xLite “introduction”, this time the xLiteWorkbookFunction function. I’ve had most of the now released functionality working (and in use) for quite a while but had delayed publishing until I’d installed Excel 2010 as I’d wished to test against a modern Excel version.
I’d not bothered with Excel 2007, as I couldn’t see the advantage over Excel 2003, but Excel’s new PowerPivot is one hell of a reason to upgrade to 2010. I’d preformed a quick test against 2007 by installing a trial version on an EC2 Window’s image and it had appeared to work fine; but it was a different story under 2010,strange things started to happen.
The core functionality, as tested by VBA code, worked OK but when I tested using xLite.SQL as a UDF (a user defined “formula”) things fell apart. For an explanation of what the xLite.SQL function is and why I wasn’t that surprised when it started to act up, see here. As xLite.SQL plays to the rules rather than the spirit of a UDF, I assumed it was pay back time for my blasé ignoring of functional programming constraints and I set about tracking down the cause.
It turns out the cause is a change in behaviour (a bug) whereby in certain circumstances the cell range returned by Application.Caller is not, as one would expect, the cell hosting the called UDF but that cell more usually associated with Application.ActiveCell (i.e. most likely the cell where the cursor currently resides).
Why is this a problem and what is Application.Caller usually used for? The most common use I’ve made of Application.Caller is to determine whether a VBA function had been called from a cell as a UDF or from a menu, button or some VBA code. This is important because when called in UDF mode, a function must be side-effect free, i.e. its only affect on the workbook is the return value; attempting anything else will silently fail (or in extreme cases, abort Excel). This functionality is not affected by “the bug” as the usual method of achieving this is by …
If IsObject(Application.Caller) Then
… this will work even if Application.Caller returns .ActiveCell, as both are objects.
If however, various properties of that range need to be interrogated (such as the actual address or the formula text that xLite.SQL requires) then Application.Caller in certain circumstances returning the .ActiceCell range rather than the calling cell’s range causes problems. I’ve managed to get around these problems by adding an extra parameter (homeCell) to xLite.SQL which the function will auto populate on first entry (when Application.Caller and .ActiveCell are guaranteed to be the same object). For example, a SQL call entered in cell A2 of Sheet1 as ..
=SQL(“Select name from sqlite_master”)
is automatically rewritten as …
=SQL(“Select name from sqlite_master”,,,,,,”[test.xls]Sheet1!$A$2″)
Not ideal but it gets around the problem in the short term. Long term I may do a version for sub-2010 reverting back to original dependence on Application.Caller. As xLite studiously avoids Excel’s UI features such as menus/ribbons I’d hope to avoid different versions for pre/post ribbon editions, but needs must.
The xLiteWorkbook example (test_call_workbook_function.xls calling test_workbook_function.xls) consistently generates the bug (there’s a SQL call against the log database at the end to test for this). Executing the same logic manually on the called workbook (test_workbook_function.xls) generally doesn’t, but it has done so occasionally!
I’ve messed around changing the .xls files to .xlsm in case of compatibility problems but it doesn’t appear to affect the outcome.
If anybody else has come across this problem or has an alternative to using Application.Caller to return a UDF’s calling cell’s range, do let me know.
Peter Hoadley suggests a workaround by using Application.ThisCell, see comments below …
UPDATE: March 19th 2010
Why not join me on Twitter at gobansaor?