XLL UDF: SheetId of range parameter can be set to the wrong sheet in multi-sheet workbooks

Add-in Express™ Support Service
That's what is more important than anything else

XLL UDF: SheetId of range parameter can be set to the wrong sheet in multi-sheet workbooks
 
David Jones


Guest


Hi there

I'm having a problem in multi-sheet workbooks with a UDF that takes a range parameter where AcceptAsArray is set to False. Regardless of the sheet where the function has been entered, when the workbook is recalculated the SheetId of the range parameter gets set to the ID of the currently active sheet.

Here is a very simple UDF that demonstrates the problem (I will also send the whole project to the support email address): 


        Public Shared Function GetCallerDetails(ByVal contents As Object) As Object
            Dim result As String = ""

            ' AcceptAsArray = False
            Dim pageContents As AddinExpress.MSO.ADXExcelRef = DirectCast(contents, AddinExpress.MSO.ADXExcelRef)

            Dim caller As ADXExcelRef = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
            result = "caller.SheetId: (" & caller.SheetId & ") contents.SheetId: (" & pageContents.SheetId & ")"

            Return result

        End Function


Start the project in Visual Studio and put a breakpoint on the Return statement.

In Excel, on Sheet1 enter a command like this:
=GetCallerDetails(D6:G13)

Depending on the sheet you are on when you press 'Calculate Now', the result string will look something like this:
* Sheet1: caller.SheetId: (12201984) contents.SheetId: (12201984)
* Sheet2: caller.SheetId: (191066112) contents.SheetId: (191066112)
* Sheet3: caller.SheetId: (191067072) contents.SheetId: (191067072)

As you can see, contents.SheetId always takes the ID of the active sheet. This is a problem because when my function tries to process the supplied range it might not be looking at the correct sheet so it gets the wrong values!

The only workaround I have found is to explicitly define the sheet in the parameter, like this:
=GetCallerDetails(Sheet2!E6:G13)

If you do this the results are as you would expect:
* Sheet1: caller.SheetId: (12201984) contents.SheetId: (191066112)
* Sheet2: caller.SheetId: (191066112) contents.SheetId: (191066112)
* Sheet3: caller.SheetId: (191067072) contents.SheetId: (191066112)

I can't expect my users to enter the Sheet name in their range reference - is there anything I can change in my code to fix this? Or is it a bug?

Thanks very much

David
Posted 22 Nov, 2012 12:16:06 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi David,

This is a bug. We have already fixed it in the new version of the product. We will publish it in a week or so.
Please wait a little.
Posted 23 Nov, 2012 04:02:21 Top
David Jones


Guest


Great - thanks, Sergey.

David
Posted 25 Nov, 2012 16:54:51 Top
David Jones


Guest


Small update - my workaround doesn't fix the problem if the qualified range is on the same sheet as the function itself.

So if the following function is entered on sheet 1 then it will always work:
=GetCallerDetails(Sheet2!E6:G13)

But this one will only work when Sheet1 is the active sheet:
=GetCallerDetails(Sheet1!E6:G13)

Similarly, if the following function is entered on sheet 2 then it will only work when Sheet2 is the active sheet:
=GetCallerDetails(Sheet2!E6:G13)

But this one will always work:
=GetCallerDetails(Sheet1!E6:G13)

Thanks!

David
Posted 26 Nov, 2012 07:38:51 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
 Joined: 2004-07-05
Hi David,

The new build with the fix is about to be published.
Posted 27 Nov, 2012 08:39:53 Top
David Jones


Guest


I've downloaded the new version and it fixes the problem. Thanks very much!

David
Posted 29 Nov, 2012 09:05:22 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Thank you for letting us know!


Andrei Smolin
Add-in Express Team Leader
Posted 29 Nov, 2012 09:24:06 Top