Nested UDFs and Calculation

Aspose.Cells Product Family
1

I have a custom udf that is used as a parameter for the TRUE output within an if statement.
A1 = False
A2 = IF(A1=True,UDF(),“do nothing”)

Upon the first calculation, the UDF is not put into the calc chain, as is expected. But, when the value is A1 is set to True, the UDF does not get called. How do I get notification that i need to run the UDF without having to calculate the entire workbook?

Thanks!

2

@PJJTLC,

Please note, if you have UDFs defined in vba, Aspose.Cells cannot calculate/execute VBA custom functions or vba codes via its formula calculation engine.

I guess you want to know if A1 is changed or not, so you should use/instantiate calc chain now, then you may calculate UDFs by yourselves. See the following sample line of code on how to create/instantiate calc chain for your reference:
workbook.Settings.CreateCalcChain = true;

Hope, this helps a bit.

3

We are using createcalcchain=true
Consider the following code:

public class UDFManager : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        var cell = data.Cell;
        var result = cell.Worksheet.Cells.CreateRange(cell.Row, cell.Column, 1, 1).Address;
        data.CalculatedValue = result;
    }
}

        var workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        var options = new CalculationOptions
        {
            CustomEngine = new UDFManager()
        };

        worksheet.Cells["B1"].Formula = @"IF(LEN(A1)>1,TestUDF(),0)"; //Nested UDF
        worksheet.Cells["C1"].Formula = @"IF(LEN(B1)>1,TestUDF(),0)"; //Nested UDF

        workbook.CalculateFormula(options); //No UDFs calc, no knowledge that another recalc is needed
        worksheet.Cells["A1"].Value = "Test";
        workbook.CalculateFormula(options); //how do we know that another recalc has to occur? Recalculation is expensive in a large workbook with thousands of UDFs each having to hit the database
4

@Moonglum,

Thanks for the code segment.

We will evaluate it and get back to you soon.

5

@Moonglum,

Did you get any problem with your code? We tested it and found it work fine. You may add some output in your custom engine to monitor the calculation:

            public override void Calculate(CalculationData data)
            {
                var cell = data.Cell;
                var result = cell.Worksheet.Cells.CreateRange(cell.Row, cell.Column, 1, 1).Address;
                data.CalculatedValue = result;
                Console.WriteLine(data.FunctionName + ": be calculated for " + cell.Name);
            }

Executing your main code we got output for the second calculaion as:
TestUDF: be calculated for B1
TestUDF: be calculated for C1

It denotes the custom function has been calculated correctly when needed.

Giving further test for the calculation, we add another line of code:

            workbook.Settings.FormulaSettings.EnableCalculationChain = true;
            ...
            worksheet.Cells["C1"].Formula = @"IF(LEN(B1)>1,TestUDF(),0)"; //Nested UDF
            worksheet.Cells["D1"].Formula = @"TestUDF()"; //Nested UDF
            ...
            workbook.CalculateFormula(options);
            Console.WriteLine("Calc again...");
            worksheet.Cells["A1"].Value = "Test";
            workbook.CalculateFormula(options);

We can get the output as:

TestUDF: be calculated for D1
Calc again...
TestUDF: be calculated for B1
TestUDF: be calculated for C1

From the output we can confirm those formulas that need to be re-calculated(B1,C1) have been re-calculated and only those formulas have been re-calculated(D1 does not need the calculation so it has not been re-calculated).