Finding the volatile function in my Excel sheet

Question

I have a fairly large Excel sheet which keeps track of our golfcompetition. To speed up the sheet (for use on my old laptop) I am trying to limit the number of automatic calculations. E.g. the total score is only calculated if you enter the last hole.

However, my Immediate Window shows me there is a lot of calculation done after every entry. There seems to be a volatile function somewhere. I have searched and replaced all functions known to be volatile (I used to use INDIRECT and OFFSET). I have checked all my named ranges for OFFSET (and removed it). I have even replaced all ROW and COLUMN functions, even though MS claims they are not volatile.

Still Excel recalculates the whole sheet after every entry, and I cannot seem to find the source.

I use Excel 2016 (Dutch version) under Windows 7. I use a couple of UDF's (which give me feedback on the calculations going on) for simple calculations. All data for the UDF's is passed as parameters.

Is there any way to track the source of volatile calculations in a sheet?

My sheet can be found on http://www.wacgolf.nl/docs/WAC%202016.xlsm It is filled with testscores until sheet '25-6'. Upon entering any score in that sheet, the whole worksheet is recalculated.

I have isolated the calcChain.xml file, which should contain the calculation chain. But I fail to see where my problem starts. Any help is highly appreciated.

Answer 1

After some searching I concluded that it weren't my UDF's that were volatile. It turned out to be a reference in the form:

INDEX([named range],[row],[col])

If I replace that reference with a direct one ([sheet]![col][row]) the calculations stop. Whether it is the INDEX function or the named range or the combination of the two, I don't know. I use a lot of named ranges and a lot of INDEX references to the named ranges. I cannot replace them all to find out what is the matter. Btw, The named range is a fixed range. Offset() is nowhere used in my sheet.

Answer 2

I now think this is probably wrong, see the comments below. UDFs are apparently considered non-volatile unless specifically marked as volatile.

Expanding on my comment, you're absolutely right about the volatile functions. But those volatile functions are your own UDFs - Excel cannot know whether they are volatile or not, and therefore has to treat them as volatile, recalculating them every time you make any change to the workbook. And because VBA is slow relative to worksheet functions, that will really slow your calculation down.

Try to replace your UDFs with worksheet functions if at all possible. You'll see a huge improvement in performance.