Apparently I forgot to post about UDF volatility

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.

Comments

• Anonymous
  May 07, 2007
  Hi, I really don't like the fact that they used the term "volatile" where it should relate to "deterministic" or not. They've messed up the terminology that's already in standard use in the industry. See: http://msmvps.com/blogs/greglow/archive/2007/05/07/deterministic-vs-volatile.aspx Regards, Greg

• Anonymous
  May 07, 2007
  Hi Greg, The "They" that used the term volatile are me. The main reason to use Volatile is that Excel client uses that terminology and, where possible, we try to keep terminology consistent. You also say in your blog post: "Volatile usually refers to a value that can be changed without the knowledge of the program" But this also holds true - volatile means that we have no way of knowing what the value is and it may change "underneath our feet" at any given moment. I do, however, agree that if we were starting from a tabula rasa I may have called it something else. Deterministic probably would not have been top of my list there though. I would have tried to give it a name that explains the action that is taken when such a UDF is encountered rather than what the UDF is supposed to behave like. So something not as dorky as AlwaysCalculate or AlwaysEvaluate would have probably been better.

• Anonymous
  January 07, 2008
  Hi Shahar, We seems to be running into some issues that appear to be related with udf volatility. We have hosted an Excel workbook in SharePoint 2007 using Excel Services that has a sheet that displays SharePoint list data using UDF. The workbook has several other sheets that use the data from 1st sheet to formulate different reports and graphs. When the user changes the data in SharePoint list, the Excel workbook does not get updated but if the user click 'Calculate Workbook', it updates the 1st sheet that consumes SharePoint list via udf. However other sheets in the workbook that reference the sheet with updated sharepoint list data still do not reflect the changes and they only do so if the user opens Excel Workbook in excel client and save it after refreshing the data by selecting Ctrl-Shift-Alt-F9. Only after this manual process, the page that hosts Excel workbook is refreshed and reflects the changes originated from SharePoint list. Are we doing something wrong? We would really like to have the SharePoint list changes to be reflected real time as user browses the various sheets in the excel services hosted workbook. Your help and guidence will be greatly appreciated. Thanks. Asad

• Anonymous
  January 07, 2008
  asadjafri: Can you show me the skeleton of the UDF? That is, the class definition, the method prototype + signature but no need for the actual code.

• Anonymous
  January 08, 2008
  Shahar, Below is the skeleton information: using System; using System.Collections.Generic; using System.Text; using System.Security.Principal; using System.Data.SqlClient; using Microsoft.SharePoint; using Microsoft.Office.Excel.Server.Udf; namespace XlUnlimitedUDFs {    [UdfClass]    public class XlUnlimitedUDFs    {        /// <summary>        /// Using the SharePoint API, read data from a        /// SharePoint list and return it as an object array.        /// </summary>        /// <param name="serverName">Server name.</param>        /// <param name="siteName">SharePoint site name.</param>        /// <param name="listName">SharePoint list name.</param>        /// <param name="viewName">SharePoint list view name.</param>        /// <param name="getTitles">Include column titles.</param>        /// <returns>Two-dimensional object array of list values.</returns>        [UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]        public object[,] getSharepointView(string serverName,                                           string siteName,                                           string listName,                                           string viewName,                                           bool getTitles)        {        }    } } Asad

• Anonymous
  January 08, 2008
  The comment has been removed

• Anonymous
  January 10, 2008
  Hi Shahar, Have you had a chance to think about my request above? Thanks. Asad