none
I'd like to know the reason why Excel 2016 is 3 TIMES slower than Excel 2010 to do 'ClearContents'. RRS feed

  • Question

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

    My customers want to know the reason why the speed of the process has become worse.
    Generally thinking , a customer will expect faser performance for a new version. But the opposite situation occured here.
    Hence they want to know the explanation for that.

    Could you give me the URL in which such description is written?

    [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

      

    Wednesday, December 6, 2017 6:19 AM

All replies

  •          

    Hi ぴよこ さん,

    I could reproduce your issue.
    I have a machine both Excel 2010 and 216 installed in it.
    Excel 2016 is slower than 2010. I suppose it causes from some fault/degradation of Excel 2016.

    I recommend to send a feedback to Excel User Voice.
    https://excel.uservoice.com/

        
        

    Private Sub btn_ClearContents_Click()
        Application.ScreenUpdating = False
        Dim startTime As Date: startTime = DateTime.Now
        Dim n As Long: n = GetTickCount
        ' ---
        Dim y, x As Long
        For y = 1 To 500
            For x = 1 To 100
                Cells(y, x).ClearContents
            Next
        Next
        ' ---
        'Debug.Print GetTickCount - n & " millisec"
        
        Dim endTime As Date: endTime = DateTime.Now
        Range("A1").Value = startTime
        Range("A2").Value = endTime
        Range("A3").Value = "= A2 - A1"
        Range("B1").Value = "Excel version: " & Application.Version
        
        MsgBox "[Clear Contents] completed"
    End Sub

    Regards,


    Ashidacchi



    Wednesday, December 6, 2017 7:43 AM
  • Thank you for your reply Ashidacchi.

    I didn't know about the Excel user voice. Thank you for the useful information and having trying to reproduce the problem! I'll post this to the site.

    Wednesday, December 6, 2017 8:12 AM
  • Hello,

    I could also reproduce the issue using your code.  

    I suggest you use Range("A1:CV500").ClearContents instead of looping through each cell. 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 5:37 AM
    Moderator
  • @Chenchen Li,

    It would be an idea for this test VBA. But looping through each cell is a TEST for checking how slow/fast "ClearContents" is, comparing Excel 2016 with 2010. So, it would not solve the slowness with Excel 2016 (ClearContents).

    Regards,

    Ashidacchi

    Thursday, December 7, 2017 6:56 AM