UDF's & puzzling recalculation behavior

[Brian McCarthy]

Looking for some help to better understand how excel handles recalculations
on UDF's. I've created several UDF's in a module and they work just fine, in
terms of returning the correct values. I'm passing all required arguments to
the functions. Becuase the worksheet is large and the sheet can take 10
seconds or so to recalculate, I've set the calculation mode to manual.

At times, when I open the workbook, the cells calling the UDF's are all
showing zero. F9 does not always repopulate the cells. Also, I have another
worksheet in the workbook which derives some data indirectly from the cells
calling the UDF's. If I am in that worksheet and the data looks right, and I
F9, the cells calling the UDF's fall back to zero values; same with
ctrl-alt-F9. I have gone back to the main sheet and ctrl-alt-F9 and that
seems to regenerate the right results. In my search for a solution, I've
checked the logic in the functions & have found that opening the module and
doing something as simple as adding a debug.print statement to one function
causes the worksheet to return the proper values to all the cells calling the
UDF's.

I'm sure there's a logical explanation out there, but it is lost on me so far.

Thanks!

 

[Duke Carey]

try adding the following to the udf code

application.volatile

 

[Brian McCarthy]

I thought of that, but I was afraid it would essentially negate the effect of
going to manual calculation. I'll try it.

 

[Brian McCarthy]

As expected, it causes a full recalc any time anything in the workbook is
changed, so that's not really desirable. Also, it dis not fix the problem
caused by recalculating when in another sheet.