Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Functions Behaving Weird
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Functions Behaving Weird
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. -- Regards, Bill "Omar" wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weird recalculation of user defined function | Excel Worksheet Functions | |||
User Defined Functions Behaving Weird | Excel Discussion (Misc queries) | |||
User Defined Functions | Excel Worksheet Functions | |||
User Defined Functions - Help Text - Make it Easy for the User | Excel Programming | |||
excel functions and User defined functions | Excel Programming |