Custom formula stored in a named range shows incorrect cell location

Aspose.Cells Product Family
1

I have a custom function (B) which is stored as text in a named range. This named range is then referenced by custom function (A) from a worksheet cell. For the named range custom function (B), the cell object in the contextObjects parameter on the CalculateCustomFunction function is showing the cell reference of custom function A instead of B. This is incorrect as the source of calculation is not the worksheet function, but the function stored in the named range.

How can we determine that a formula is firing from a named range?

Please see attached archive for a sample.

AsposeNamedRangeIssue.zip (2.1 KB)

2

@mhopkins,
In your implementation of ICustomFunction, the “functionCell” means the cell whose formula is being calculated in fact. So it is always A1 while calculating its formula. What you want to get should be the cell reference used in the formula. To get more details about the formula, please use AbstractCalculationEngine instead (which provides more flexible and convenient operations for manipulating custom functions).

The code example:

        workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(NamedRangeFormula,C1:C5)";

        // Calcualting Formulas
        workbook.CalculateFormula(new CalculationOptions(){CustomEngine = new CustomFunction()});

    class CustomFunction : AbstractCalculationEngine
   {
       public override void Calculate(CalculationData data)
       {
           Console.WriteLine(data.FunctionName);
        Console.WriteLine(data.Cell.Name);
        Console.WriteLine(data.GetParamValue(0));
           data.CalculatedValue = 0;
       }
   }

Let us know your feedback.

3

Ahsan,

I attempted to use the AbstractCalculationEngine and it does not appear to calculate the formula text stored in the named range. Using ICustomFunction actually does calculate the named range reference first before resolving the formula stored in A1.

Regardless, my main problem is that the formula stored as a named range value is not providing any indication that it is located in a named range. It is in fact reporting it’s location as A1 as well which is incorrect.

Thanks,

Matt

4

@mhopkins,
We are analyzing your feedback and will share our comments soon.

5

@mhopkins,
As we have said, “A1” only denotes which cell are now being calculated and that also means the cell’s calculated value depends on the custom function currently being processed.

Parameters’ values may have not been calculated for the custom function when entering method AbstractCalculationEngine.Calculate(). This logic provides the ability of preprocessing some parameters for the custom function before calculating them recursively. For example, you may check the parameter to avoid circular references, or select the proper value/reference for your custom function.

To get the calculated value of the parameter, you may invoke

CalculationData.GetParamValue(paramIndex).

If you need to check whether the parameter is a Name object, you have to check the parameter text (CalculationData.GetParamText(paramIndex)) by yourself.