Volatile Function Cache Lifetime Demystified

There has been some confusion and misconceptions about what the VFCL (Volatile Function Cache Lifetime) setting does exactly in Excel Services - even internally in our team. You can find this setting on the Trusted Location administration page under the "Calculation Behavior" section.

This setting governs one thing - the amount of time Excel Services will wait before recalculating sheets that contain volatile information when a new session is opened. This last bolded part is where people sometimes misunderstand what the setting is about exactly.

Excel Services recieves an OpenWorkbook() request in the following cases:

1. When a user causes the EWA (Excel Web Access) Web part  to open a new session (this happens the first time a user accesses a workbook on a certain EWA, when the session times out and the user requests it be reopened or when the user selects the "Reload workbook" option in the EWA drop-downs).

2. When a user of the Excel Web Services calls the OpenWorkbook() API method.

When this type of request is received by Excel Services, it first checks to see if the workbook is already loaded (and since it shares out loaded workbooks, it usually just serves those copies up immediatly). If, however, the workbook contains a volatile function in it (for example, =RAND() or =NOW()), Excel Services also checks to see when the last time the shared copy was recalculated. If that last time was more than what the VFCL is set up as, Excel Services will cause an implicit recalculation of the workbook and serve up the new calculated version as the result.

What this means for you:

1. If your workbook has volatile function calls in it and you want them to update, it's not enough to call one of the GetXXX() functions - you need to actually cause a recalculation first. This can be done by simply calling the .CalculateWorkbook() method. Once that request comes back, the workbook has been recalculated and the cells that contain volatile functions should display the most up-to-date values.

2. If you have a trusted location that contains a workbook with calls to volatile functions and you want that workbook to update frequently when people open it up for viewing, you can reduce the VFCL setting to whatever number suites your scenario.

Hope this clears up this setting a little more.