User Defined Functions Behaving Weird

[Omar]

Hi there,

I have been using the RiskSim user defined functions that utilizes the Excel
Rand function and it was working perfectly fine.

Recently I have noticed that it is starting to act in a very weird manner.
Whenever you start using the formula, it gives a #value error and then when
you copy and paste it again on the same cell or to other cells it works fine.

Has anybody came across a similar behavior of user defined functions beofre
- the issue seems very odd to me and I can not understand the rationale
behind it.

If somebody is really interested, I could send him the Add-in that I have
created and he can take a closer look at it.

Thanks and regards,

 

[Bill Renaud]

You should have an error handler included in your code. Set a breakpoint in
your code in the error handler, then look at the locals window to check the
value of all incoming variables when the error occurs. You may have an input
variable that is not initialized or something. I remember seeing an article
somewhere before that explained that the recalculation engine sometimes
calls functions, even though all values are technically not available yet.
It then calls them again later to complete the recalculation of the
worksheet. This may be a function of what version of Excel you are running.

You should check the value of all input variables carefully before allowing
your function to begin calculating whatever it is supposed to do.
Does your function refer to values or ranges of values (i.e. tables or lists
on other worksheets) that are not passed into the function via the
arguments? Is an object reference properly set to these other variables?
Single-step through the code and double-check the value of each variable
(function arguments as well as local variables) in the locals window. I
usually find the problem fairly quickly after doing dedicated
single-stepping.