Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

[Code]

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Main()

Debug.Print "Excel version : " & Application.Version
Dim n As Long
n = GetTickCount

For y = 1 To 500
For x = 1 To 100
Cells(y, x).ClearContents
Next
Next

Debug.Print GetTickCount - n & " millisec"
End Sub

20 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Alexander R. commented  ·   ·  Flag as inappropriate

        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).

      Feedback and Knowledge Base