How to force an order of cell evaluation on Excel

Tag: excel Author: xy596710224 Date: 2009-07-21

I am using a custom Excel addin which exports several functions to Excel which do a lot of "behind the scenes" work. I want to force Excel to evaluate calls to these functions in a particular order. That is, if

A1 = AddinFunction("Foo")

and

B3 = AnotherAddinFunction("Bar")

then I want to force Excel to evaluate A1 before B3. How can I achieve that with mininum hacking, and preferably without using VBA?

Other Answer1

This boils down to how the expression tree evaluation is done in Excel. It needs a reason to evaluate your functions in that order. You could make B3 dependent on the result of A1, for instance B3 = IF(ISBLANK(A1), AnotherAddInFunction("Bar"), AnotherAddInFunction("Bar"))

Not done XLLs in a long time, so this is from memory.

comments:

Thank you, this will work at first evaluation (when A1 is empty), but not when I want to reevaluate the spreadsheet. Is there a way to force Excel to remove the output from the previous evaluations?

Other Answer2

If you can change the code to your XLL addin then you could add dummy 'trigger' arguments to the calls that you want to sequence (add an optional XLOPER arg). You then make the call dependent on the cell that you want it to follow by adding in a reference to the cell as the dummy trigger argument...

So B3 ends up as AnotherAddinFunction("Bar", A1);

the function itself ignores the trigger argument and B3 will be evaluated whenever A1 changes and always after A1 (as it depends on A1).

Of course if you can't change the XLL then this doesn't help...

comments:

I've ended up appropriating some arguments for this purpose, but it's not the solution I was looking for, in general.