我想知道为什么Excel 2016比Excel 2010慢3个时间来执行'ClearContents'。 [英] I'd like to know the reason why Excel 2016 is 3 TIMES slower than Excel 2010 to do 'ClearContents'.

问题描述

我的客户报告说，我们的产品（Excel Addin）在2010年到2016年更新Excel之后变得更慢。

通过分析问题，结果发现这个问题的主要原因很慢性能是'ClearContents'。

我设置了两个相同的硬件条件机器。我在其中安装了Win10。然后我在机器A上安装了Excel 2010.
$
我在机器B上安装了Excel 2016。

通过使用下面的代码，我测量了ClearContens的时间。 / p>

在Excel2010（A）上花费2859毫秒。在Excel2016（B）上需要9719毫秒。 

我的客户希望了解为什么流程的速度变得更糟的原因。

一般来说，客户会期望新版本的faser 性能。但是这里发生了相反的情况。
b $ b因此他们想知道对此的解释。

你能否给我写下这些描述的网址？

[代码]

私有声明函数GetTickCount Lib" kernel32" （）As Long

Sub Main（）

   

    Debug.Print"Excel版本：" &安培; Application.Version

    Dim n As Long

    n = GetTickCount

   

   对于y = 1到500

   适用于x = 1至100

           单元格（y，x）.ClearContents

       下一个

   下一个

       

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

  

解决方案

         

嗨よこさん，



我可以重现你的问题。

我有一台Excel 2010和216机器安装在里面。

Excel 2016比2010年慢。我想这会导致Excel 2016出现故障/退化。



我建议发送反馈到  Excel用户语音 。

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 
下一个
下一个
'--- 
'Debug.Print GetTickCount  -  n& "毫秒" 
 
 Dim endTime As Date：endTime = DateTime.Now 
 Range（" A1"）。Value = startTime 
 Range（" A2"）。Value = endTime 
范围（"A3"）。值=" = A2  -  A1" 
范围（"B1"）。值=" Excel版本：" &安培; Application.Version 
 
 MsgBox" [Clear Contents] completed" 
结束子

问候，

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

  

解决方案

         

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,

这篇关于我想知道为什么Excel 2016比Excel 2010慢3个时间来执行'ClearContents'。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！