none
wrong calculation order using UDFs RRS feed

  • Question

  • Hello,

    we have a XLL using the c-api which defines several UD>F where the arguments are of type U
    and which are also registered as volatile.
    We also have a workbook with one sheet who use some of the UDFs,
    but none of the Excel build-in functions.

    Now when this workbook is loaded I see that the cells aren't calculated in the order of dependencies.
    That's our problem.

    More info:

    The used range is B2:G11, but not all cells have a formula.

    - Every formula references B2, but since B2 is a string literal, this isn't a problem.

    - Every formula except the one in B3 references B3, so B3 should be the first to be calculated.

    - The formulas in D2:G2 has no other cell references.

    - The formulas in D3:G3 additional references the cell one row above

    - The formulas in C6:G6 has no other cell references.

    - The formulas in B7:B11 has no other cell references.

    - The formulas in C7:G11 additional references every cell in D2:G3,
       the also reverence the cell in row 6 and same column and the cell in column B and same row.


    This describes all cell references in the sheet,

    but when the Workbook is loaded, Excel tries to calculate the cells in following order:

    C6, D6, E11, C11, E2, G10,
    E10, D3, D9, B7, E7, D2,
    C10, E8, G11, G3, B3, C9,
    B11, F3, F9, G6, F11, D8,
    G9, D11, E3, F10, D7, B8,
    F2, F7, E6, C7, C8, E9, F6,
    G7, F8, G2, G8, D10, B10, B9

    You see especially that B3 isn't the first cell.

    So why does this happens ?

    tia


      Hendrik Schmieder

    Thursday, September 6, 2018 12:51 PM

All replies

  • Difficult to comment without your file to hand, could be various reasons. Maybe something in the following will lead to a solution for you -

    http://www.decisionmodels.com/calcsecretsj.htm

    https://fastexcel.wordpress.com/2012/02/02/writing-efficient-vba-udfs-part-10-volatile-functions-and-function-arguments/

    Friday, September 7, 2018 6:37 PM
    Moderator
  • Sorry for the delay.

    the first link looked promising.

    We use  xlCoerce  on the arguments and this works for normal worksheet functions, but not for the

    macro sheet functions.

    So tried the c-api function xlfIsblank which looks like the equivalent for the VBA function  ISEMPTY for macro sheet functions.

    But the call always returned false even if the the argument was a reference to a yet uncalculated cell with a formula.

    So currently I use Q instead of U when registering macro sheet functions to force the correct order of calculation.

    Backdraw is that I lost in this case the information whether an argument was a reference and if yes to what cells

    the reference was pointing to.

    with best regards

        Hendrik Schmieder

    Thursday, December 6, 2018 10:35 AM