Issue with UDF and when it will recalculate

[TomCon]

I have discovered the following about a UDF and when it recalculates. Which i find quite confusing. Can anybody make sense of this, and better yet, how do i get it to work "as it should".<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If i have a UDF with two range arguments,<o></o>
MyUdf(range1, range2)<o></o>
<o></o>
The UDF has Application.Volatile(False)<o></o>
<o></o>
When i put the following formula in a cell<o></o>
=MyUdf(A1,B1)<o></o>
it recalculates whenever A1, or B1 change, so that seems "correct".<o></o>
<o></o>
When i put the following formula in a cell<o></o>
=MyUdf((OFFSET(A1,C1,0),B1)<o></o>
It does NOT recalculate if cell C1 changes. Why not? If the OFFSET formula is naked in a cell, it will recalculate if C1 changes. Why is this "hidden" from the Excel recalc engine if it is an argument to MyUdf?<o></o>
<o></o>
And, even worse, i have discovered that, in the second case, now if cell B1 changes, myUdf still does not recalculate. This seems really confusing. Why does the OFFSET function in arg1, cause Excel to not recalculate MyUdf if cell B1 changes?<o></o>
<o></o>
I have confirmed all of the above by setting breakpoints. Both of the above seem "wrong" to me at least. It seems like it should be easy for Excel to recognize that MyUdf needs to be reevaluated in either case, above (and especially the second). Scary that you can have wrong answers before your eyes and not realize it.<o></o>
<o></o>
So, now, assuming my statements above are correct, how do i get MyUdf to recalcuate if EITHER of the data for its arguments change, that is, if either B1 or C1 change, in the formula above? I do not want to set Volatile to TRUE as i have many calls to the UDF.<o></o>

Thanks!<o></o>
Tom<o></o>
<o></o>

 

[Derek Brown]

I would expect this to be a problem as it is being passed as an argument to your UDF and is not an actual function in the worksheet.
Here are some useful references about volatile functions:
http://www.decisionmodels.com/calcsecretsi.htm
http://www.ozgrid.com/News/GoodVsBadDesignSpeedUpEvents.htm
http://www.dailydoseofexcel.com/archives/2005/04/29/using-volatile-functions/

 

[xenou]

I'm not really sure but my first thought was that OFFSET() is a volatile formula, so any UDF that relies on it probably needs to be volatile also.