Volatile UDFs

I had a discussion with a client recently about using volatile functions (User defined ones that is).

(volatile as in it calculate any time Excel calculates, not anything to do with our financial markets!)

My view is to avoid them so that Excels smart calculation can do its stuff, and so that cell auditing works.

Their view was pulling variables in directly from certain cells reduces the number of parameters and therefore simplifies the function.

I think both views are valid and it comes down to relative priorities. But the performance of the function has an impact, big slow VBA clonkers would steer me away from volatile, but if it was a low overhead thing, then maybe not so much of an issue.

Anyway it struck me that it would be worth knowing what the difference was between a volatile function and a non volatile one. Its about 200 per hour!

In my extremely limited testing a volatile function would get called 200 times more than a non volatile per hour.

I used this simple xll available here to just keep incrementing every time Excel calculated whilst I was building a model.

Its dead simple just:

long int lCount = 0;
EXPORT long int VolatileCalcCount ( void )
return ++lCount;

You could do the same in VBA, this is just less intrusive. Just add that as a worksheet function somewhere out of the way and watch it increment as you work.

Excel basically recalcs any time there is any change, so I must have done about 200 edits in the hour. Thats only 3 a minute – doesn’t sound like much – they must have been big ones ;-).

Let us know what score you get



One Response to “Volatile UDFs”

  1. Charles Says:

    I hope they were using Named Ranges to pull the data directly, but even so its a maintenance nightmare as well as slowing calculation.

    I nearly always use manual calc mode when building anything other than trivial models, so the constant recalc of volatile functions every time I change anything does not occur.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: