Excel-DNA / Excel-DNA/ExcelDna
Problem getting cell address of a spilled function invoked in BYROW
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 292
- Avg merge
- 20d 12h
- Merged PRs (30d)
- 1
Description
Hi @govert!
We are bumping into an issue with obtaining the correct cell address using `XlCall.Excel(XlCall.xlfCaller)` when our functions are invoked in a spilled fashion. In other words, we have a function say `ABC` which returns a scalar result, and we want to _feed_ it into [BYROW](https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb) which operates on a range of rows, takes each row and executes the specified lambda on it, and then spills the results on the worksheet.
An example might look like:
`=BYROW(A1:B5, LAMBDA(row, ABC(INDEX(row,, 1), INDEX(row,, 2))))`
Here ABC executes 5 times where each instance processes the respective row data and returns a result. What we ultimately want to achieve is to be able to uniquely identify each instance of ABC for some reason, and for that we've been depending on the cell address the function is invoked from. Apparently this does not work in this case because the cell address we get in all the function instances is the same - the address of the spilling parent.
Not sure whether this is a limitation of ExcelDNA or Excel itself, because the ultimate cell address of a spilling function might be uncertain at the point when it is being executed yet. Would love to hear any advice here.
Contributor guide
Assessment
This issue has not been assessed yet.