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 )
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