Recalc for a UDF a little perplexing

[TomCon]

Can UDFs somehow be left in a "partially recalculated" state? I have a UDF that i "copied down" for about 3,500 rows. After the pause for recalc, and waiting a little extra to be sure that recalculation was done (no more "Calculating" in the status bar), what i was left with, was that approx every 50th or 100th row or so had #VALUE. But, these were not actually an error condition. All i did for each case was re-select the cell, hit return, and it calculated fine. Does anybody know what controlls that Excel apparently stopped calculating before it was actually through, and left a scattering of #VALUE in those cells, when all the inputs were perfectly valid?

Thanks!
Tom

 

[Leith Ross]

Hello TomCon,

A UDF will be calculated whenever the cell is edited. To have the UDF calculate whenever the sheet recalculates, you have to add Application.Volatile to the very beginning of your UDF. Keep in mind, having a lot of volatile UDFs on the sheet can slow things greatly.

Sincerely,
Leith Ross

 

[TomCon]

Its not a case of a Volitile UDF. The UDF only needs to be recalculated when its input cells change. But, when i "copy down" (for 3,000+ rows), I would think that each cell would at that point calculate. But, what this operation yielded (a few times in a row) is that "most" of the rows calculated, but a scattering of #VALUE remained in some rows. Why weren't those calcualted with the "Fill down" operation was my question. And, as i noted, there is not actually any error in those rows, as no correction is needed, just hitting return in the cell gets the right value. So, i am mystified as to why the fill-down op did not get the correct value in the first place. If anybody has any thoughts, please let me know!
Thanks!
Tom

 

[xenou]

There's no reason why Excel would "stop calculating". If you are sincerely interested, provide more information ... what is the UDF? What else is going on in this workbook?

 

[TomCon]

Well, ok, at least hearing that is telling me that it does not appear to be some "known" issue that i could fix. Maybe it is just a fluke. It is a fairly large workbook, with maybe a dozen sheets, each with about 3,000 rows, and each with many calls to one of several UDFs. In this case i did a "Fill down" and a smattering of formulas were left with #VALUE until i selected each and hit return. So, i thought, maybe a fluke, so i just filled down again, and the same thing happened. I did not track if it was exactly the same cells the second time that were #VALUE, but again it was around 30-50 out of about 3,000. In this case the UDF is a call to a web service that fetches a value and returns it to the cell. None of the UDFs are Volitile, and all have range arguemnts that get the data that is the input to the calculation. If anybody knows of a phenomena like this please let me know. Otherwise, i will consider it as some sort of fluke.

Thanks much!!

Tom

 

[xenou]

Could be the web service didn't respond or lost the packet? Can your UDF confirm a successful value was returned by the web service, and try again if not?

 

[TomCon]

Yes, i guess that is a good idea. I do not have a check. The function takes the web service response, parses it, and returns a value. So, if there were an error, it could indeed return an error value. And, if the error were spurious (over the web), perhaps it could explain this behavior. Thank you for your thoughts. Yes, prehaps i could make it try one more time. within each invocation, if the first attempt fails. I would not want to make it try infinitely!
Thanks!
Tom