When I wrote the previous post about returning personal data from UDFs, I suddenly realized that I never fully explained what volatility means in Excel Services UDFs.
Since I know your life would not be complete without this information, I decided to make a post about it.
On the client and on the server, UDFs come in two flavors – the volatile ones and the non-volatile ones. The best way of defining a volatile UDF is this:
If a function can return different results for the same exact set of parameters then it’s volatile.
As an example, lets take two Excel functions, RandBetween() and Sum(). For a given set of parameters Sum() will always return the same result, nothing external other than the parameters will affect the result. However, RandBetween() takes two parameters and even if the two parameters are the same, the result may change from one execution to the other.
Excel provides support for volatile user defined functions by allowing users to call the Volatile() method on the Application object. Excel Services takes a somewhat different approach and uses a declarative model to do this:
[UdfMethod(IsVolatile = true)]
public double MyMethod()
That’s all one needs to do to get the method to be volatile.
This brings me to how volatile functions behave. In Excel, a function that is marked as volatile gets calculated every time the workbook gets calculated for some reason. This includes pressing F9. When a file is saved and it has a cell that contains a call to a volatile function, that cell will be marked as dirty when saving which will make Excel recalculate it when it loads the file (as long as auto-recalc is on).
The server behaves almost exactly the same. There is one difference though. For better performance, the server will refrain from recalculating workbooks with volatile functions too often. By default, the first user to load a workbook with dirty cells will cause a recalc (again, only if auto-recalc is turned on). Subsequent users who can share the results of that user will get the old results. Once a prescribed amount of time has passed (5 minutes by default), the server will again recalculate the cells. What this means is that if a cell contains a call to NOW(), the first user to open it at, say, 12:34:56, will see that time in the cell. A user coming 20 seconds later will also see 12:34:56 in that cell. However, if a user comes in 6 minutes later, the result seen in the workbook will be 12:40:56. This setting (the 5 minutes) is controllable from the Trusted Location setting configuration form of Excel Services.