Excel-DNA / Excel-DNA/ExcelDna

Problem getting cell address of a spilled function invoked in BYROW

Open
#522 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.