caller.Address for UDF appears to be current (selected) cell rather than cell the UDF is in

[dheine777]

In a C# asynchronous UDF built on ExcelDna 1.6.0-preview4, getting the caller.Address value from the caller object seems to always return the address of the selected cell rather than the cell the UDF is located in.

[ExcelFunction(Description = "Demo UDF")]
public static object MyUDF() {
 Application excel = (Application)ExcelDnaUtil.Application;
 dynamic caller = excel.Caller; 
 string currentValue = caller.Text;
 string address = caller.Address; // Not a reliable value - seems to not reflect the actual address much of the time!
 int row = caller.Row;            // Consistent with address 
 int column = caller.Column;      // Consistent with address 
 Debug.WriteLine("Current value {0} is not empty in cell {1}.", currentValue, address);
 ...
 }

Output from the above Debug.WriteLine(...) when the selected cell is D13 and various cells contain an instance of the UDF in a formula that generates a string of the form "0/0/0", where "0" is some integer represented as a string:

Current value 0/0/0 is not empty in cell $D$13. Current value 0/0/0 is not empty in cell $D$13. Current value 0/0/0 is not empty in cell $D$13. Current value 1/0/0 is not empty in cell $D$13. Current value 1/0/0 is not empty in cell $D$13. Current value 2/0/0 is not empty in cell $D$13. Current value 3/1/2 is not empty in cell $D$13.

As can be seen above, while there are different cells referencing this UDF, the address value is the same regardless of the cell that called the function and, in all instances I have seen, reflects the cell that is the current selected cell in the worksheet.

Have also verified that the caller.Row and caller.Column properties also do not represent the caller's actual address, but are rather consistent with the selected cell and the caller.Address value.

As a (possibly) unrelated topic, the reason the debug statement above notes whether the cell's current value is empty is because the caller.Text property does not appear to reliably reflect the actual text contents of the cell. Sometimes it does, sometimes it doesn't, can't figure out the pattern yet.

[govert]

Getting the calling cell from an Excel-DNA UDF with the COM call to Application.Caller is problematic. Instead, you can use the C API call XlCall.Excel(XlCall.xlfCaller). This gives you an ExcelReference object that should refer to the right cell. Depending on what you want to do next, you can use this in other C API calls, or you can get a COM Range object referring to the same cell. But using the COM object model inside a UDF can be problematic.

You mention 'asynchronous UDF`, but the example you show is not async. Depending on what type of async UDF you make (RTD-based of native Excel asynchronous function) and how it is implemented (using the Excel-DNA Registration extensions or directly) you might have further complications.

[dheine777]

Thanks so much, using XlCall worked perfectly!