empty arguments recalculation

[Jorge Suzan]

While using a macro for some user defined functions, it happens that it
returns an error, or in the worst case, excel (2007) stops running at all.

Through debugging I find that one or several arguments passed to VBA by
reference appear to be empty, although they are correctly referred.
I assume this empty arguments are non recalculated references.

Somehow, in some cases the function macro seems to be recalled as many times
as necessary to complete previous reading of the missing arguments. I assume
that this is because the worksheet is recalculated automatically until all
arguments are passed as required by the macro.

It is magic for me how the situation in some cases gets suddenly stabilized,
and the macro works when all inputs in the worksheet are quiet, but if a
precedent input changes the application stop running again, requiring to be
restarted once and once again.

My guess is that I have not been able to decipher completely the excel
recalculation rules, but I have not found anywhere some explanation about how
this work.

I will highly appreciate your assistance.
Thank´s for help
JS

 

[Joe User]

My guess is that I have not been able to decipher completely the excel
recalculation rules, but I have not found anywhere some explanation
about how this work.

I cannot help you, except to commiserate. I, too, have observed surprising
behavior in the Excel (2003) recalculation engine -- UDFs getting called
multiple times before parameteres have final values, and UDFs getting called
seemingly unnecessarily based on an intuitive understanding of dependency
graph.

Ostensibly, automatic recalculation should be driven by a dependency graph,
which detemines which cells must be recalculated if one cell's value
changes, and which also determines which cells must be recalculcated before
recalculating a particular cell.

But I know from careful instrumentation that that is not how Excel works, at
least not entirely.

Of course, if you enable Iteration calculation and you have purposeful
circular references, that can cause problems in the dependency graph.

But I never use such tricks. So I consider the observed Excel recalculation
(mis)behavior to be defective.

AFAIK, there is nothing we can do about it. Most of the time, the redundant
UDF calls are innocuous, since we are only interested in the last result.
But when they cause problems, my only solution has been to design the UDF
algorithm to tolerate them.

I have never had a situation where the redundant UDF calls stop the
recalculation engine; perhaps that's a new "feature" of Excel 2007, which I
am not familiar with. Assuming that the redundant UDF calls are a defect,
it would not surprise me if an Excel 2007 developer "improved" the
recalculation engine without realizing the consequences of this defect.

Perhaps you will have to design UDFs so that they never return Excel errors
:-(, always returning some valid value by default instead.


----- original message -----

 

[Charles Williams]

see http://www.decisionmodels.com/calcsecretsj.htm for a discussion of how
to handle this problem with UDFs

see http://www.decisionmodels.com/calcsecretsc.htm for a discussion of
Excel's calculation process


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com