none
Excel 2013 poor performance with Range.Copy RRS feed

  • General discussion

  • I have an application that generates a Printable Form. It was originally deployed with Excel 2007 and then updated for Excel 2010. Running this application on Excel 2013 it is 424% slower! I generated a test application to validate the performance issue.  I tested this application by running the compiled code on a CF-52 laptop with Office 2013 Pro Plus and it did 1,000 copies in 23.32 seconds. Then I deinstalled Office 2013 and installed Office 2010 Pro Plus and rebooted.  Ran the same executable (I actually ran it 3 times with each office to make sure compilers times did not skew the data) and it did the 1,000 copies in 5.49 seconds.

    It may be a change is some obscure default setting in Excel but I can not find it.

    Here is the code I used to test with:

    namespace Excel_Performance_Problem
    {
        class Program
        {
            static void Main(string[] args)
            {
                Stopwatch Watch = new Stopwatch();
                string line;
                int NumberOfLines = 1000;
                Console.WriteLine("Doing {0:D} lines...", NumberOfLines);
                Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.Visible = false;
                Excel.Workbook Workbook = xlApp.Workbooks.Add();
                Excel.Worksheet Worksheet = Workbook.Worksheets.get_Item(1);
                
                Excel.Range CopyRange = Worksheet.get_Range("A1", "J2");
                CopyRange.Value = "Test";
                CopyRange = Worksheet.get_Range("A1", "J1");
                Excel.Range WorkingRange;
                Watch.Start();
                for (int i = 1; i < NumberOfLines; i++)
                {
                    WorkingRange = Worksheet.get_Range("A" + i, "J" + i);
                    CopyRange.Copy(WorkingRange);
                }
                TimeSpan ElapsTime = Watch.Elapsed;
                xlApp.Visible = true;
                Console.WriteLine("Time for {0:D} lines: {1}", NumberOfLines, ImageTime(ElapsTime));
                line = Console.ReadLine();
                xlApp.Quit();
            }
    
            static string ImageTime(TimeSpan Time)
            {
                string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                Time.Hours, Time.Minutes, Time.Seconds,
                Time.Milliseconds / 10);
    
                return elapsedTime;
            }
        }
    }
    

    Monday, May 18, 2015 6:54 PM

All replies

  • Hi Paul,

    I can understand that you wonder why that happens. And to figure out the details is difficult... but doesn't really matter.

    I'm not an expert in C, but experienced enough to reproduce the same in VBA. See code below.

    During some short tests, my Sub Test_Paul_LaRoux (which does the same as your code) needs between 2 and 3.5 seconds with XL2010... I guess my machine is faster as your laptop.

    But IMHO even 2 seconds for just 1000 rows is unacceptable!

    The basic question is why it needs so long to execute and how can we make it faster:

    a) It is slow to access many (single) cells in a loop
    b) When you copy a cell, you copy anything (values, colors, number formats, conditional formats, etc.)
    c) You should disable the screen output when the sheet is visible, do not hide the application
    d) A much faster way (especially when you deal just with data) is not to copy the cells, just write the data into the cells. And if possible use an array for that.
    e) If possible use a template file that already has the necessary formatting.
    f) Apply the formats to the whole data in one step, after you've written the data.

    My Sub Test_Andreas_Killer needs around 0.05 seconds to execute, means it is around 4000% faster!

    So even if XL2013 is slower (for what reason ever), it doesn't matter anymore, the code will execute faster as a blink of your eye.

    Don't believe me? Guess how long it will need to write the numbers from 1 to 10.000 into A1:J1000 then run Sub Test_Andreas_Killer_Data from below.

    Maybe you are also interested in this article:
    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Andreas.


    Option Explicit
    
    Sub Test_Paul_LaRoux()
      Dim Wb As Workbook
      Dim CopyRange As Range, WorkingRange As Range
      Dim i As Long
      Dim T As Single
      Const NumberOfLines = 1000
    
      'Prepare
      Application.Visible = False
      Application.EnableEvents = False
      Set Wb = Workbooks.Add
      Set CopyRange = Wb.ActiveSheet.Range("A1:J1")
      CopyRange.Value = "Test"
     
      'Start timings
      T = Timer
      For i = 1 To NumberOfLines
        Set WorkingRange = Wb.ActiveSheet.Range("A" & i & ":J" & i)
        'Copy anything
        CopyRange.Copy WorkingRange
      Next
     
      'Stop timings
      Debug.Print Format(Timer - T, "0.00") & " seconds"
     
      'Clean up
      Application.Visible = True
      Application.EnableEvents = True
    End Sub
    
    Sub Test_Andreas_Killer()
      Dim Wb As Workbook
      Dim CopyRange As Range, WorkingRange As Range
      Dim i As Long
      Dim T As Single
      Const NumberOfLines = 1000
    
      'Prepare
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      Set Wb = Workbooks.Add
      Set CopyRange = Wb.ActiveSheet.Range("A1:J1")
      CopyRange.Value = "Test"
     
      'Start timings
      T = Timer
      For i = 1 To NumberOfLines
        Set WorkingRange = Wb.ActiveSheet.Range("A" & i & ":J" & i)
        'Write values only
        WorkingRange = CopyRange.Value
      Next
      'Copy the cell formatings
      CopyRange.Copy
      Wb.ActiveSheet.Range("A2:J" & i - 1).PasteSpecial xlPasteFormats
     
      'Stop timings
      Debug.Print Format(Timer - T, "0.00") & " seconds"
     
      'Clean up
      Application.EnableEvents = True
      Application.ScreenUpdating = True
    End Sub
    
    Sub Test_Andreas_Killer_Data()
      Dim Wb As Workbook
      Dim Data
      Dim i As Long, j As Long
      Dim t As Single
      Const NumberOfLines = 1000
     
      'Prepare
      Set Wb = Workbooks.Add
     
      'Start timings
      t = Timer
      ReDim Data(1 To NumberOfLines, 1 To 10)
      For i = 1 To UBound(Data)
        For j = 1 To UBound(Data, 2)
          Data(i, j) = j + (i - 1) * 10
        Next
      Next
      Wb.ActiveSheet.Range("A1").Resize(UBound(Data), UBound(Data, 2)) = Data
     
      'Stop timings
      Debug.Print Format(Timer - t, "0.00") & " seconds"
    End Sub

    Monday, August 3, 2015 10:04 AM
  • I tested this application by running the compiled code on a CF-52 laptop with Office 2013 Pro Plus and it did 1,000 copies in 23.32 seconds. Then I deinstalled Office 2013 and installed Office 2010 Pro Plus and rebooted.  Ran the same executable (I actually ran it 3 times with each office to make sure compilers times did not skew the data) and it did the 1,000 copies in 5.49 seconds.

    Now I'm at home and i run the same code within some virtual machines:

    Machine Test_Paul_LaRoux Test_Andreas_Killer
    Excel 2010 Win 7 6,8 Seconds 0,05 Seconds
    Excel 2013 Win 8.1 13,4 Seconds 0,05 Seconds

    All timings at the 1st run, but from the 2nd run the execution is faster (for whatever reason):

    Machine Test_Paul_LaRoux Test_Andreas_Killer
    Excel 2010 Win 7 1,7 Seconds 0,05 Seconds
    Excel 2013 Win 8.1 5,8 Seconds 0,05 Seconds

    Andreas.

    Monday, August 3, 2015 2:43 PM