Code slower in Excel 2013?

[ErikJan]

Using some code to copy cells from an Excel workbook to a new workbook. Looks like this...

Code: Select all

For i = 1 To Num
  Range("A1").Offset(OrgIndex, NmIfI).Copy
  Workbooks(PK).Worksheets("Sheet1").Range("A1").Offset(i, 0).PasteSpecial xlPasteValues
  ...
Next

Calculation, ScreenUpdating and Events are off. In Excel 2003, 2007 and 2010 this worked fast enough. In Excel 2013 however this is extremely slow... Any idea what changed here?

[ErikJan]

Anyone with a clue?

[HansV]

Not many users of Office 2013 yet, I fear...

[Jan Karel Pieterse]

Can't you move the Copy statement outside of the for loop, in the current code sample you give it pastes the same information each time

[ErikJan]

Where the "..." are I'm doing 5-10 more copies-and-pastes for other (non-adjacent) cells...

I'll try to find some time to create a test sheet and will post that here...

[bjsatola]

I have the SAME EXACT PROBLEM. Not the copying-and-pasting, but 2013 Excel is slow and I cannot figure out why. There's unfortunately not so much information out there. I have a rather large project (good coding practice used) that was very fast in 2010. It performed lots of data analysis and printed the results to an Access database etc. But now the same exact file runs agonizingly slower in 2013, grrrrrr!

Anyone figure out any reasons why this is? There seems to be a lot of people having poor performance problems from the upgrade, but there is no good advice on it. People keep telling folks to turn of the graphics visualization like that would cause what I'm seeing :/

[Rudi]

Interesting thread here...Recalculation twice as slow.

So what has happened is that the overhead for each VBA call to the Excel object model has increased from Excel 2010 to Excel 2013, but the calculation speed is the same.

[HansV]

Welcome to Eileen's Lounge!

The thread Rudi pointed to appears to indicate that the combination of Excel 2013 / Windows 8 is particularly slow. Which Windows version are you using?

[bjsatola]

Running 2013 Office-64 on a Windows-8 64-bit Thinkpad W520 powerhouse (i7-XM, 16GB, etc.).

According to Rudi's link, the example:
>> Took about 4 seconds in Excel 2010, and about 8 seconds in Excel 2013 running on a Win-7 64-bit (they used Office-32 in both cases)
>> Took me 15.1 seconds in Excel 2013 for Office-64, ugh...

Looks like I have the worst combination of system and office suite T_T Hopefully they come-up with a fix for this soon, but looking at the progression of the Office performance maybe not. There hasn't been an update to that MSDN thread in sometime :/

Thanks for the info guys :) Cheers

[Rudi]

The first time i ran it it was slow...11 seconds.
Then subsequent runs were quicker as seen...

I am on Windows 8 Enterprise and Office 2013 Professional Plus 32-Bit running on Lenovo T510 (8GB RAM)

2013-07-25_06h54_11-horz.jpg

[HansV]

VBA code is "compiled" the first time it is run, so if you want to time a macro, you should always run it several times and discard the timing of the first run.

[Rudi]

VBA code is "compiled" the first time it is run, so if you want to time a macro, you should always run it several times and discard the timing of the first run.

I ran the macro about 6 times in two different sessions (between a reboot). The times in both sessions were similar to my screenshot. The first session first run was 11.6 sec and second session first run was 11.8 sec. Subsequent runs were in the region of between 6-8 seconds, the fastest being 6.2 and the slowest being 7.9 secs.

[HansV]

There will always be variation between runs (after the first one), even if you have no other applications open, because Windows has so many background processes running all the time. So it's best to average the timings of several runs after the first one.

[bjsatola]

I ran it 25 times, and not too much of a difference... I heard that the Wind8-64 and Office-64 is the worse combination though :/

[HansV]

It depends on the operations that you're executing. The 64-bit version of Excel is fast at some operations, and frustratingly slow at others.

[ErikJan]

6.6 seconds Excel 2013 (32 bit) on Win7 64bit

[FrankAu]

It seems with Office 2013 some of the Application default settings have been changed as I get a substantial performance improvement when I use the following ...

xlSheet.Application.Calculation = xlCalculationManual
xlSheet.Application.ScreenUpdating = False
xlSheet.Application.IgnoreRemoteRequests = True
<<<perform lots of operations on the worksheet>>>
xlSheet.Application.IgnoreRemoteRequests = False
xlSheet.Application.ScreenUpdating = True
xlSheet.Application.Calculation = xlCalculationAutomatic

The critical statement is the disabling of Screen Updating!

It seems with the increased focus on collaboration (multiple users editing a common workbook etc) MS have "decided" to enable Screen Updating for linked processes even if Excel is invoked with the "/automation -Embedding" switches. Performance after this change is still not fantastic - but it is much more tolerable. I wonder if there are some other application level settings that could be used to stop Excel from performing other irrelevant processing?

[Rudi]

Hi Frank,

Welcome to Eileen's Lounge.

ErikJan did mention that ScreenUpdating was set to off. However there are a number of options that can be switched off to improve the codes performance (as you specify).

I from time to time use a preset code block (as below) to do exactly this when the workbook is large or has MANY formulas in it. I am sure there are a few other things that can be done to improve performance, including the limited (or wise) use of loops and some good code practices to optimize the code....

Code: Select all

Sub OptimiseVBA()
    Dim screenUpdateState As Boolean, calcState As String
    Dim eventsState As Boolean, statusBarState As Boolean
    Dim displayPageBreakState As Boolean
    
    'Get current state of various Excel settings
    screenUpdateState = Application.ScreenUpdating
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    statusBarState = Application.DisplayStatusBar
    displayPageBreakState = ActiveSheet.DisplayPageBreaks    'Sheet-level setting
  
    'Turn off some Excel functionality to run code faster
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayStatusBar = False
    ActiveSheet.DisplayPageBreaks = False    'Sheet-level setting
    
    On Error GoTo EH:
    '===================
    'CODE BLOCK HERE...
    '===================
    
Reset:
    'After your code runs, restore state
    ActiveSheet.DisplayPageBreaks = displayPageBreakState    'Sheet-level setting
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState
    Application.Calculation = calcState
    Application.ScreenUpdating = screenUpdateState
    Exit Sub
EH:
    Resume Reset
End Sub

[FrankAu]

G'day Rudi,

I have used similar when the VBA is running (in) and updating the Excel workbook that the user has open - especially when lots of formulas exist as you mentioned.
What was curious about Office 2013 is my VBA is running in an Access 2013 context and linking to an "embedded" Excel 2013. I had expected that in that circumstance Screen Updating would not even be contemplated by Excel but this assumption now appears faulty.

Given the Excel session will be trashed I didn't bother to log the current state before riding roughshod over it :-).

BTW on a few occasions I have seen an inadvertent double click on a half built .xlsx leads to the backgrounded Excel coming to the foreground and allows the user to interact with the worksheets!! I expect eventually my Access VBA will choke because it can't shut down the embedded object - which is why I now tell it to IgnoreRemoteRequests too!

Cheers, Frank.

[Jan Karel Pieterse]

What does turning off animations do for the speed:

(Windows setting):

Control panel > System and security > System > Advanced system settings (in upper-left side) > Advanced tab > Settings in Performance box > Visual effects tab:
Uncheck the "Animate controls and elements inside windows."

I'm looking for the VBA alternative, which I know is there...