empty arguments

[Jorge Suzan]

While codifying a macro for some user defined functions, it happens that they
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.

Somehow in some cases the function macro seems to be recalled as many times
as necessary to complete the 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 any
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 any assistance.

Thank´s for help
JS

 

[Tim Williams]

Hard to make suggestions with no code.

Tim

 

[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