Excel 2010 Application.Caller Bug

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.

UPDATE:

Peter Hoadley suggests a workaround by using Application.ThisCell, see comments below …

UPDATE: March 19th 2010

Danny Khen from Microsoft has emailed me to say …
This is a known Beta bug which has since been fixed. So you should expect it to go away when Office 2010 ships

Why not join me on Twitter at gobansaor?

Advertisements

14 responses to “Excel 2010 Application.Caller Bug

  1. I have also found this bug in Excel 2010: application.caller.address does not always return the address of the cell containing the function, but the active cell address — ie the cell selected in the sheet.

    It works correctly in Excel 2002, Excel 2003 and Excel 2007, but not in Excel 2010.

    I haven’t found a work around yet. It’s an extremely serious bug — my commercial Finance Add-in for Excel relies on this working correctly.

    Do you know if Microsoft are aware of this bug?

  2. @Peter

    I’ve used the “unhappy face” icon to report it, but not sure how effective that is. Searching the web there appears to be nothing out there on the bug, that’s why I wrote the post hoping to flush out others with the problem. You’re the only one so far. So it might be safer to assume MS are not aware of it.

    Having said that, are you testing against the Beta version or the RC (Release Candidate) version? I’ve only tried the Beta version, might be worth getting hold of the RC to see if it has been noticed & fixed.

    Tom

  3. Tom,

    I downloaded it today from the MS site. It just said ‘beta’. Not sure if it’s the release candidate — the version in ‘about’ says: 14.0.4536.1000 (32-bit) BETA.

    I’ll hunt around the MS site to see if there another one but I pretty much did that earlier today and I always ended up at the same download point.

    If there is another way to get the address of the UDF I could use that to work around the issue, but I haven’t discovered one yet.

    I’ll let you know if I discover anything relevant. So far this is the only major issue I’ve discovered in Excel 2010 — but it sure is a major one for me.

    Peter

  4. Peter

    14.0.4536.1000 is the same as my version, which is the “public” beta. I think you have to be an official beta tester to get access to the RC version.

    If I find a work around other than the hack I’ve mentioned in the post, I’ll document it here.

    Tom

  5. Tom,

    I have developed a work around which will be my fall back if the bug isn’t fixed in the release. The solution works in all versions of Excel.

    You can get the address of the UDF using Application.ThisCell.Address This is a string (not a range object).

    To get a range object you can then use code similar to the following:

    sThisCell = Application.ThisCell.Address
    sSheet = rCaller.Parent.name
    Set rRangeObject = Sheets(sSheet).Range(sThisCell )

    rRangeObject (which is defined as type range) now is exactly the same as

    Set rRangeObject = application.caller, except that it is now correct in Excel 2010.

    I’ve tested this and it’s working correctly.

    Peter

  6. Sorry, rCaller above should be application.caller
    Peter

  7. @Peter

    That could be a solution, the only problem may be the
    sSheet = application.caller.Parent.name bit, while most of the time the application.caller range is pointing towards the “wrong” cell in the “right” sheet, I think I’ve also seen it pointing towards both the “wrong” cell and “wrong” sheet, but I might be wrong in that. I’ll make a similar change to my code and try it out.

    Tom

    • @Peter

      As ThisCell also has a .Parent property then by setting …

      sSheet = application.ThisCell.Parent.name

      … this should work even when .Caller is pointing at another sheet.

      Might also be a good idea to go up another level (.Parent.Parent) to get the workbook just to be sure, to be sure (to use an Irishism 😉 )

      Thanks Peter for that.

      Tom

  8. Looks like a few others have noticed the bug http://social.technet.microsoft.com/Forums/en/excel/thread/dc50fdee-3e7a-4fc3-af04-eb8f56472da9

    The way to get this fixed is to post to forums or leave comments on blogs (like this one) or Twitter it or write a blog post. The more noise, the more likely MS will notice it.

    Also if you know any official beta testers ask them to report it.

    Tom

  9. Tom — I agree with you that application.caller may have other issues if address is wrong. If it’s not fixed in the final release I think it would be best to avoid it completely.

    ie Basically, if you are only interested in one cell, you can simply use application.ThisCell in place of application.caller as they both do the same thing (when the range is just one cell). So application.caller can be avoided entirely.

    The only issue to watch out for is that application.ThisCell was introduced in Excel 2003. So if you require your software to work on Excel 2002 (which I do) you would need to test for that.

    Peter

    • You could use Application.Version to determine which range object to work on, .Caller for < 14.0 and .ThisCell for 14.0 (Excel 2010)

      Tom

    • I learned my Excel Object Model on ’97 & 2000 so I’d missed ThisCell (note to oneself; maybe a revisit of the model would be a good idea).

      This warning on ThisCell as regards array formulae is from http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx

      “CAUTION: In Excel 2003, a new object, Application.ThisCell, was introduced. It is similar in nature to Application.Caller, but differs when a UDF is array entered into a range of more than one cell. Application.Caller will return the a Range reference to the entire range in which the UDF was array-entered. Application.ThisCell returns a reference to the first (upper left) cell in the range from which the UDF was called. Frankly, I’m not sure why Application.ThisCell was introduced in the first place.”

  10. Thank you.
    I can confirm that Application.ThisCell.Formula returns the correct formula when Application.Caller does not.

  11. Danny Khen from Microsoft has emailed me to say …

    “This is a known Beta bug which has since been fixed. So you should expect it to go away when Office 2010 ships”

    Good news.

    Tom