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?
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 :/
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
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?
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 :/
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.
HansV wrote: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.
Last edited by Rudi on 25 Jul 2013, 05:57, edited 1 time in total.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
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.
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?
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....
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
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!
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...