Controlling invocation of ICustomFunction

Aspose.Total Product Family
1

Hi,


Is there anyway to control in which order different custom functions are invoked?

Example:
I have two custom functions, MyCustomFunctionA and MyCustomFunctionB.

I know that the order of invocation is implicitly given be dependencies between cells.

For instance, if
A2 contains “=MyCustomFunctionA(A1)” and
A3 contains “=MyCustomFunctionA(A2)”
then MyCustomFunctionA in A2 will be called first, then MyCustomFunctionA in A3 will be called, because the way the cells depend on each other.

However, I want to make sure that this happens:
A2 contains “=MyCustomFunctionB(A1)” and
A3 contains “=MyCustomFunctionA(A1)”

In this case, MyCustomFunctionA in A3 should be called before MyCustomFunctionB in A2.
Or rather, all MyCustomFunctionA:s should always be called before all MyCustomFunctionB:s.

Is this possible?

2

Hi,


Thanks for your query.

Well, generally, Aspose.Cells formula calculation engine would calculate formulas in sequence (row by row) in the sheet which it should. For your custom needs, could you try to use Cell.Calculate(bool ignoreError, ICustomFunction customFunction) overload for your desired cells accordingly if it helps. We might look into your custom needs. We request you to kindly create a simple console application (you may zip the project prior attaching it) with your templatefile(s), we will try to evaluate and check if this can be controlled to help you through.

Thank you.
3

Thanks, I figured out how to solve this in a different way.


But, is it really true that the formulas are calculated row by row? I thought they would have to be executed in the order which is defined by the dependencies between cells?

i.e. if the formula in cell A1 depends on the formula in A2, then A2 gets calculated first?
4

Hi,


Good to know that you have sorted out your issue now.

Well, in fact there is no guarantee for the calculation order of formulas when there is no formula chain. You are right, if there are one formula depends on another one, then the dependent one will be calculated first. Furthermore, there are some more tasks you need to do if you want to control the order by yourself:
1) Gather and build the correct order for formula chain before calculations, make sure that the formulas that are dependent to others which should be calculated first.
2) Use Cell.Calculate(CalculationOptions) with Recursive = false;

Thank you.