Gurs has an interesting benchmark he has been running on various systems and Excel versions over the years. His results seem to show a massive performance decline in later Excel versions.
Looking at his benchmark and its VBA code you can see that has ScreenUpdating ON and repeatedly calculates in Automatic mode. Each calculation triggers some RANDBETWEEN functions with a number of dependent cells.
Gurs does not want to speed up his benchmark because that would destroy his historic speed comparisons.
But the problem is that a large portion of the time in his benchmark is taken by screen updating, and so his benchmark results vary significantly depending what part of the worksheet is actually visible on the screen, and hence how many visible cells get refreshed at each calculation.
Running Gurs Benchmark with different Excel versions but on the same PC
I ran Gurs benchmark on my desktop PC with Excel 2003 to Excel 2013., with a constant screen area visible (rows 1:118 and columns A:BF). Since I cannot install Excel 2016 on the same PC as previous versions without causing unwanted problems I used a VM on my desktop and also ran the benchmark on my Surface Pro 3.
This shows loops per second (higher is better) with Screen Updating On and Off, the ratio of OFF to ON and the ratio to Excel 2003.
The results for Excel 2003, 2007 and 2010 show that:
- Excel 2007 and 2010 are slower than Excel 2003
- Screen Updating On is 15-20 times slower than Screen Updating Off
But something changed with Excel 2013!
- Screen Updating ON gets significantly faster
- Screen Updating OFF gets significantly slower
My visual impression is that Excel 2013 does not try to update the screen on every iteration when the update frequency is high, and this is the reason for the change.
And although I don’t have an exact comparison for Excel 2016 it looks comparable to Excel 2013.
Creating a Pure Screen Updating Benchmark
So my next step was to try to create a pure screen updating benchmark.
In column A I put 28 =RAND() formulas, and then in columns B:V i put very simple formulas that linked back to column A.
This gives me 616 cells that will change on each calc, and its easy to keep all 616 cells visible on the screen. The VBA code times 10000 calculations with Screen Updating Off and again with Screen Updating Nn. The difference between these is the time taken by the screen updating.
Sub ScreenTest() ' ' time screenrefresh ' Dim i As Long Dim tStart As Double Dim tEnd As Double Dim tScreenOn As Double Dim tScreenOff As Double With Application .Calculation = xlCalculationManual .ScreenUpdating = False tStart = MicroTimer For i = 1 To 10000 .Calculate Next i tEnd = MicroTimer tScreenOff = (tEnd - tStart) .ScreenUpdating = True tStart = MicroTimer For i = 1 To 10000 .Calculate Next i tEnd = MicroTimer tScreenOn = (tEnd - tStart) End With MsgBox "Off " & Int((tScreenOff) * 1000) & _ " On " & Int(tScreenOn * 1000) & _ " Diff " & Int((tScreenOn - tScreenOff) * 1000) & " Millisecs" End Sub
The timing results for this test of screen updating are:
For this (very extreme) benchmark Excel 2013 screen updating is about 180 times faster than previous versions.
But with screen updating turned off Excel 2013 runs this benchmark 5 times slower!
I think what has happened is that Excel 2013 is still doing all the work to update and format the values, but has added a check to limit the frequency of requests to Windows to actually repaint the screen.
Note: this does NOT mean that Excel 2013 runs everything 5 times slower: this is a very extreme case benchmark.