Good morning everyone,

Im currenly finishing up an input system used to forecast calls based upon a couple of pieces of information input by the user. The program is almost ready apart from a niggling issue i have with the calculation of the file.

If i have the calculation set to Auto then it recalcs every time something is changed. Now according to my web research i am led to believe this could be due to a volatile function/formula somewhere. The problem is that unless SUM has all of a sudden become volatile then i cannot find it anywhere. The only other formulas are =A1 types.

I have a couple of processes that enter some formulae and then paste values after but even those are only VLOOKUPS. Ive managed to get round the issue in code by only calculating the sheet im working in but it still does a full recalc at the end of the process which seems to take about 20-30s.

Would any of you happen to know of any other reason why this might be the case. My only other option can i can think to get this finished is to code out all of the formulas altogether.

Hope this makes sense, let me know if not.

Thanks

EV

Hi,

This sounds correct, but SUM() is definitely not volatile.

Only volatile functions will recalculate when precedents have not changed. Do you have any named formulas in the workbook? If yes then check if any of them contain volatile functions. The most common volatile functions are OFFSET(), NOW(), TODAY() and INDIRECT(). Of course there are others, and you can find a wealth of information about Excel calculations at Charles Williams' site:
How the Excel Smart Recalculation Engine works - Decision Models


If you have conditional formatting formulas then these are also volatile by design so overloading a workbook with conditional formatting will impact performance.

Another consideration might be if you have event handler VBA code which could be calling a recalculation.

I hope that gives you some ideas - if not then perhaps you could remove any sensitive information and zip/attach the workbook for us to look at? 20 to 30 seconds is a long calculation period - if nothing else, there is a good chance we could reduce it to less than 1 second with some careful redesign and formula improvement.

Hi Colin,

Thanks for getting back to me. I did manage to find a workaround but its not ideal. By turning calc off on open i then recalc the sheets as necessary during various process's. This does get round the problem of the file calculating everytime a user inputs info but turns it off for the whole app while the file is open.

i have attached a zipped file with randomised data in it that works.Please ignore the colour scheme, i tend to play around with randomness when building a new system. Also i know my VBA is a long way from textbook, but it works (for the most part).

if you have any questions please let me know

Hi,

I had a look and I didn't experience any volatile behaviour. That is, if I changed something, only dependant cells were recalculated.

Also, I'm using a laptop at the moment and a full build and recalc only took a few seconds, not 20-30 secs. Did you remove a significant volume of data/formulas when you attached the file?