Custom fucntion evaluate isnt triggered for every cell

Posted by: lior.sharabi on 13 March 2022, 9:16 pm EST

    • Post Options:
    • Link

    Posted 13 March 2022, 9:16 pm EST - Updated 29 September 2022, 6:46 am EST

    Hi everyone

    we created custom function and implement the evaluate function

    public class PyramidFunction extends CustomFunction {
    	public PyramidFunction() {
    		super("PA.Pyramid", FunctionValueType.Text, createParameters());
    	static Parameter[] createParameters() {
    	public Object evaluate(Object[] objects, ICalcContext iCalcContext) {

    we put 2 cells with the pyramid function, both of them on the same column:

    when i’m putting a breakpoint on the evaluate function it’s pausing only for the first cell!.

    when i’m moving the second cell to be in another column it will call evaluate also for him.

    after i played with it, it’s seems like cells with same syntax that are on the same column will call evaluate function only once.

    is this on purpose?

    how can we avoid it?


  • Posted 14 March 2022, 8:01 pm EST


    We do not face the issue at our end with the attached sample.

    The same custom formula is put in the C1 and C2 cells and the evaluate method is called for both.

    If you are doing anything else then please let us know and modify the sample accordingly.

  • Posted 15 March 2022, 5:05 am EST - Updated 29 September 2022, 6:46 am EST


    thanks for the reply.

    I did a small project for reproducing the issue,

    please have a look

    i made 2 json strings, one with function cells in the same column and one without.

    when you switch between them you can see that the evaluate called once for the first json and twice for the second

  • Posted 16 March 2022, 10:32 pm EST


    Thank you for the sample attached.

    We can observe the difference and escalated this to the developers for investigation.

    [Internal Tracking ID: DOCXLS-5712]


    Prabhat Sharma.

  • Posted 20 March 2022, 2:13 pm EST


    As per the developers, the reason for the different evaluation times between the same and different columns is “calc engine cache”. GcExcel would store the cache of the formulas in the same column which has the same parameters.

    And we would not create cache if they are in the same row. So in SameCol, we call evaluate only once and in DifferentCol, we call evaluate twice. That’s due to the cache of formula design.


    Prabhat Sharma.

  • Posted 22 March 2022, 3:34 am EST


    thank you for the answer

    can we somehow disable the cache so it will call evaluate for every cell?

  • Posted 22 March 2022, 7:39 pm EST


    We are discussing this with the developers and will let you know the updates soon.


    Prabhat Sharma.

  • Posted 23 March 2022, 10:16 pm EST


    As per the developers, there is no way to disable the cache and it is not recommended too. It would cause bad performance when calculating.

    Could you explain why you want to disable it when the formula are exact the same?


    Prabhat Sharma.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels