Asked by:
wrong calculation order using UDFs
Question

Hello,
we have a XLL using the capi 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 buildin 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, B9You see especially that B3 isn't the first cell.
So why does this happens ?
tia
Hendrik Schmieder
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/writingefficientvbaudfspart10volatilefunctionsandfunctionarguments/

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 capi 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