Excel 2016 : ClearContents is 3 times slower than Excel2010
My customers reported me that my product (Excel Addin) becomes slower after they updated Excel from 2010 to 2016.
By analyzing the problem it turned out that main reason of the slow performance is 'ClearContents'.
I setup two same hardware condition machine. I installed Win10 into them. Then I installed Excel 2010 on the machine A.
And I installed Excel 2016 on the machine B.
By using the following code, I measured the time to ClearContens.
It takes 2859 ms on Excel2010 (A) . It takes 9719 ms on Excel2016 (B).
Generally thinking , a customer will expect faser performance for a new version. But the opposite situation occured here.
Private Declare Function GetTickCount Lib "kernel32" () As Long
Debug.Print "Excel version : " & Application.Version
Dim n As Long
n = GetTickCount
For y = 1 To 500
For x = 1 To 100
Debug.Print GetTickCount - n & " millisec"
Alexander R. commented
Just a tip: Regardless of possible performance issues in Excel 2016 vs 2010, you'd better NOT try to change the contents/formatting of adjacent cells within dense loops in VBA -- it's extremely inefficient way. Always try to operate directly with ranges or named ranges, if possible -- this will be executed almost instantly.
e.g. `Range(Cells(1, 1), Cells(500, 100)).ClearContents` or `Range(Names("MyNamedRange")).ClearContents` (if I rememeber this correctly, don't have Excel at hand)
Do like this with any other action that can be performed on ranges (either directly addressed or selected by user).