Force custom function with 0 parameters to evaluate

Posted by: pedro-rodrigues on 8 September 2017, 2:05 pm EST

  • Posted 8 September 2017, 2:05 pm EST


    Let me start by setting up the scenario: 

    - I'm using Spread to build a configuration tool for management reports. These reports make use of the built-in functions, and also some (a lot of, in fact) custom functions that get data from an accounting software's database.

    - Besides these custom functions, there're some parameters that are added to the spread as custom names (ie, variables), that are used as one of the parameters in most of the custom functions.

    - Up to this, no problems (or close to none <img src="/emoticons/emotion-1.gif" alt="Smile" />)


    My problem is, some of the<span class="Apple-style-span"> used custom functions don't have any parameters. When I call SheetView.Recalculate(), these functions don't get recalculated. I even tried marking these parameterless as volatile, to no avail (the IsVolatile() override isn't even called).</span>

    Granted, the return value of these functions rarely changes, as they're usually functions that get static data from the database, such as a company's name, or address. The problem is, these functions never get calculated, which means that whenever I open a previously saved report, the cell containing any of these functions gets the well-known value of #NAME?, and it never changes. The only way to get it calculated is by deleting the corresponding cell's contents, and inputting them again. 

    I know that by calling SheetView.RecalculateAll() will also recalculate these functions, but I can't do that, or I'll penalize the performance.

    I've also seen some workarounds by putting a dummy parameter in these functions to force a recalculation, but I also can't do that, or the users would shoot me <img src="/emoticons/emotion-1.gif" alt="Smile" />.

    Is there any other way to accomplish this?



  • Replied 8 September 2017, 2:05 pm EST

    Hi again


    I checked those two posts, and tried to change my parameterless functions to accept one optional parameter.


    But, still no change. The function gets evaluated once when added to a cell, but never again. If I save the report, and reopen it, I get #NAME? again <img src="/emoticons/emotion-6.gif" alt="Sad" />


    Using the workaround in works, but I really hate this solution, as it forces me to analyze all the cells to determine whether it contains a parameterless function. 


    Any other ideas?




  • Replied 8 September 2017, 2:05 pm EST

    The spreadsheet provides a RecalculateAll method for evaluating all formulas.

    The spreadsheet provides a Recalculate() method for evaluating just the formulas that are out of date.  This includes formulas that reference cells or custom names whose values have changed since the last evaluation.  This also includes formulas that contain volatile functions.  This also includes formulas that have a circular reference.

    There are no methods for recalculating specific cells or for recalculating just cells whose formulas contain specific custom functions.

    The spreadsheet provides an IsVolatile() method in the FunctionInfo class to indicate whether a function is stable or volatile.  Formulas containing volatile functions get evaluated every time a recalculation cycle occurs (regardless of whether or not parameters to the volatile function have changed).

    In your scenario, your custom functions are pulling information from a database.

    If the information in your database rarely changes then you could leave your custom functions marked as stable and call the RecalculateAll() method when the database changes.

    If the information in your database frequently changes then you will probably want to mark your custom functions as volatile.  In which case, the formulas using the custom functions would be recalculated every time a recalculation cycle occurs.

    You mentioned that the formulas containing your custom functions are evaluating to #NAME? error after loading from a file.  It sounds like you forgot to make your FunctionInfo derived classes serializable.  At parse time, a custom function's name is stored in the parsed expression.  At evaluation time, the name is looked up in the custom function table.  If the name can not be found then the formula evaluates to #NAME! error.  If you forgot to make your custom function serializable then the custom function will not be saved to the file.  When the file is later loaded, the custom function will be missing from the custom function table and any formulas using the custom function will evaluate to #NAME? error.

  • Replied 8 September 2017, 2:05 pm EST


    While working on the reported issue, I came across 2 previous post with some detailed overview of custom function and parameters. May this should help


  • Replied 8 September 2017, 2:05 pm EST

    Hi bobbyo


    I'll try setting my function as Serializable and see how it goes.


    But, I still have my function marked as volatile, shouldn't it be recalculated at every Recalculate()? (even it the database value rarely changes)

  • Replied 8 September 2017, 2:05 pm EST


    If a function is marked as volatile (i.e. IsVolatile() method returns true) then every formula containing the function should be evaluated every time a recalculation cycle occurs (this includes every time the Recalculate() method is called).  This volatile feature is working correctly in our tests.  If the volatile feature is not working correctly for you then could you provide a sample application (or sample code) to reproduce the issue?

    Note that if you forget to make the custom function serializable and you save the spreadsheet to a file then the custom function's definition (including the stable/volatile flag) will be lost.  When you reload the file, the custom function will be undefined (and thus not marked as volatile).

  • Replied 8 September 2017, 2:05 pm EST

    Hi bobbyo,


    Sorry for not replying sooner, I've been busy with other projects here.

    Anyway, I have yet to try to make my function Serializable, as I've solved the issue in some other way (by forcing these formulas to be evaluated after being loaded). Since I don't really like the solution I have so far, I'll try some other way, when I get the time for it, and will post here when I can (don't wait up! <img src="/emoticons/emotion-1.gif" alt="Smile" />).




Need extra support?

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

Learn More

Forum Channels