I am trying to improve the calculation time on a workbook that has been around for a long time.
I am doing this 'incrementally' by looking at various sheets and have come across one that is taking longer than I would expect.
I have distilled the issue down and have attached a sample workbook where I have recreated the situation.
Essentially, it boils down to a defined name that is used to determine the 'End of File' of a data range, and is then used in various places to create dynamic ranges for other formulae.
I have designed the attached workbook to take a non-trivial amount of time to calculate (I'm not looking for a discussion on the merits of SUMIF vs SUMPRODUCT vs Arrays etc).
I have included a very simple timer in a module to assist.
On my machine, if I run the FullCalc() sub as is, it takes about 36 seconds (this is not the fasted machine, but it doesn't matter for this purpose).
If I then change the defined name:
Bottom = 10001
When I re-run the calc timer, I get about 13 seconds.
So it takes between 2.5 and 3 times longer with the formula in the defined name.
My expectation was that it would be almost identical, since Excel only needs to evaluate 'Bottom' once (and gets the value '10001') so the times would be very similar.
However, clearly that is not the case.
As far as I know, MATCH is not a volatile function, so that shoudn't be a factor?
So, why does it take so much longer with the formula in the defined name?
Any comments or thoughts appreciated.