I have written a function that requires a random number as an argument. If I call the function in cell a1, and pass cell b1 as the only argument, where b1 contains "=Rand()", a recalc in excel 2007 causes b1 to change, but my function in a1 does not change. Here is a simplified form of the function which reproduces the problem:
If i do not declare x as Double, the issue goes away...sometimes. Any ideas?
Please Login or Register to view this content.
Last edited by avr5iron; 07-29-2011 at 11:12 AM.
Is there any reason the function is not volatile?
I believe if you change the volatility to TRUE, the FUNCTEST2 value will change with recalc.
As i mentioned, this is a simple version of a much more complex function. The actual function is part of a simulation, and will be called in a large number of cells. In the interest of speed, I only want to calculate the cells where the argument for the function has changed.
In doing additional testing, I have noticed something I don't understand. I'm posting a workbook which illustrates my issue with the actual function. The function in cell a1, corrRand2of2() is the one with the problem. It calls 2 other user defined functions. In the code of corrRand2of2(), I have created 2 sections...one that calls my user defined functions, and another that uses analogous (albeit less accurate) application functions. If I comment out the code using application functions and leave the reference to my functions active, the function will not be triggered on successive recalcs of the workbook...even though the argument in cell b1 changes. If I comment out the reference to my functions, and make the application function section active, the function does recalc with each workbook recalc.
Any insight would be geatly appreciated.
If you remove the Application.Volatile False statements, it behaves as you would expect.
Last edited by shg; 07-29-2011 at 09:49 AM.
Entia non sunt multiplicanda sine necessitate
I would prefer to leave application.volatile(false). The function will be called in many cells. In many instances the argument will not change. I do not want the function to recalc when its arguments are unchanged. This works with the application based functions, but not with the user defined functions called from corrRand2of2().
Not setting application.volatile false isn't the equivalent of setting application.volatile true; add a breakpoint and convince yourself.
I added breakpoint and tested. It worked perfectly. Thank you.
I should add that I was surprised by the behavior, though Application.Volatile is intended for UDFs, not functions called from VBA.
I'm surprised, as well. I was of the opinion that the statement would have no impact when calling a udf from a user defined sub or function. I feel like I am seeing inconsistent results in different functions. I really thought the only impact of including application.volatile (false) in my functions would be to insure that they would not recalc when called from excel and when arguments contained in cell references did not change. I actually thought it was necessary for that purpose, but your test convinced me it's not.
For most UDFs I write, I have a VBA function with a UDF wrapper. The wrapper is where Application.Volatile would appear, among other things.
I don't have any background information on this and I can't find any formal documentation, so everything I'm about to write is purely observational and not a definitive guide!
I think the most logical place to start is the "Application.Volatile" topic in the VBA helpfile. The topic gives the syntax as expression.Volatile(Volatile) where Volatile is described as:
The problem is that the helpfile does not define the terms 'volatile' and 'nonvolatile', so we have to define them ourselves. These are terms given by Microsoft in the helpfile, so I'll assume them to be the industry standard. As shg pointed out, a third possibility is that Application.Volatile is not contained in the UDF at all, so there are three possible scenarios:True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True
- Volatile -- where the UDF has Application.Volatile True
- Other -- where the UDF has no Application.Volatile statement enforced.
- Nonvolatile -- where the UDF has Application.Volatile False
'Volatile' is well understood. A volatile function always recalculates whenever a calculation event occurs, regardless of whether or not any of it's precedent cells have recalculated or changed.
'Other' is also well understood. When people talk about a function which is not volatile (not to be confused with "nonvolatile"), this is the category they're referring to. When a calculation event occurs, these functions only recalculate if a precedent cell has recalculated or changed. These are the most common type.
'Nonvolatile' functions, such as the FUNCTEST2 function posted at the start of this thread, are explicitly marked with Application.Volatile set to False. From the testing I've done (in Excel 2010) with calculations set to automatic, these functions only calculate when:As you observed already on this thread, they do not recalculate when a precedent cell is recalculated - even if that cell contains a volatile function - they only recalculate when the dependency tree is rebuilt.
- The formula is evaluated (eg F2>ENTER)
- CTRL+ALT+F9 or CTRL+SHIFT+ALT+F9
- Physically change the value of a precedent cell
- Workbook.ForceFullCalculation is set to True
There's a wealth of information on Charles William's site about Excel calculations, but I couldn't see anything specifically on the 'nonvolatile' function genre. I had a google and found a couple of links with some relevance:
http://www.tech-archive.net/Archive/.../msg01159.html
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
Last edited by Colin Legg; 07-29-2011 at 06:15 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks