When I do the following:
var customFunction = new CustomFunction(); // my implementation
workbook.Worksheets[ "Sheet1" ].Cells[ "A12" ].Calculate( false, customFunction );
Error in calculating cell [Sheet1!A12]: ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Parameter name: index
The formula for A12=MyFunc(Sheet2!LocalC2,D3:F4). Given the following information:
MyFunc is just your sample function from 'Using ICustomFunction feature' page. And Sheet2!LocalC2 is a named range (LocalC2) scoped to the worksheet (Sheet2).
If [Sheet2]LocalC2=15 and I have the following...
A13:=Sheet2!LocalC2+2
var customFunction = new CustomFunction(); // my implementation
workbook.Worksheets[ "Sheet1" ].Cells[ "A13" ].Calculate( false, customFunction );
Console.WriteLine( workbook.Worksheets[ sheetName ].Cells[ cellAddress ].StringValue );
I correctly get 17. So using a named range scoped to a worksheet seems to only be a problem when used in a ICustomFunction.
Any suggestions to work around this issue would be greatly appreciated.
Thanks in advance.