Get range offset or actual cell when using ExcelReference inside a lambda

[Graeme Smith]

We have a use case where we need to get the cell address being called from.

Our code for doing this is as follows:

[ExcelFunction(Description = "Test")]
public static object Test(int number)
{
var cellAddress = GetAddress();
return $"{number} - {cellAddress}";
}

internal static string GetAddress(int rowOffSet = 0, int colOffSet = 0)
{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

return $"R{caller.RowFirst + rowOffSet}C{caller.ColumnFirst + colOffSet}:{caller.SheetId}";
}

In normal usage (calling the function once per cell), this works fine.
If, however, we call the function inside a lambda (using a byrow function, for example), then the address returned is always the top left cell of the range being passed into the byrow function, not the cell being referenced in that particular iteration of the lambda.

Example below:

Note how the cell address is all "R2C6", unlike the "dragged down" version in col F.

Is there a way to get an offset or something similar when getting a cell address inside a lambda?

Thanks

[Graeme Smith]

Ok, turns out, for our use case, simply adding a GUID to the computed cell address works perfectly:

[ExcelFunction(Description = "Test")]
public static object Test(int number)
{
var cellAddress = GetAddress();
return $"{number} - {cellAddress}";
}

internal static string GetAddress(int rowOffSet = 0, int colOffSet = 0)
{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

return $"R{caller.RowFirst + rowOffSet}C{caller.ColumnFirst + colOffSet}:{caller.SheetId}:{Guid.NewGuid()}";
}

[Graeme Smith]

This solution does, however, introduce a memory leak (since we never invalidate our cache of stored values, since no 2 keys ever match).

It would be nice if we could at least detect that we are inside a function that was called as part of a dynamic range/lambda type function, though I can't seem to work out how to do that.