jeffreyweir
Active Member
God I hate Excel 2013. I'm finding that vba projects run much slower in 2013 than in 2010. And some of my code fails altogether in 2013, but runs fine in 2010. And don't get me started on the time Excel takes to start up, and that blasted "Streaming Office" nonsense that displays any time you open one of the Office 13 programs up.
For instance, in the Project Costing Model I put together (which you can access at https://www.dropbox.com/s/yklnxuyqtby7bc8/costing-model_final.xlsm but I can't upload because it's a touch over 1mb) if I run it in Excel 2010 and click the Refresh Option/Scenario icon in cell c8 on the first input sheets (called Option 1| Scenario 1 ) , the routine that it triggers executes pretty fast. (This routine executes an SQL query that does some number crunching on a temp copy of the file, then writes the result to a hidden data sheet that a whole bunch of pivots run off).
Now if I run the same routine in Excel 2013, the code errors out, because for some reason Excel inexplicably changes worksheets from the input sheet to the Summary sheet. And so when my code tries to reference a named range local to the initial sheet, I get a Type Mismatch error.
It's switching sheets in a module called ClearScenario, even though this module does not instruct Excel to switch sheets at all. Rather, it merely unprotects a whole bunch of them, refreshes some pivots, then protects them again, like so:
If I put a breakpoint at that first line, and step through and execute the lines one at a time, then Excel doesn't change sheets (and nor should it). But if I don't step through and instead just get Excel to execute the code, then it changes sheets inexplicably.
I added a Debug.Print ActiveSheet.Name statement between all the Unprotect and Protect statements, to see what was going on.
For the Unprotect, I got this:
Option 1|Scenario 1
Option 1|Scenario 1
Scenario Overview
FTEs by Role
NPV view
Total FTEs
Summary
...which is interesting, because we see that it handles the first two unprotects just fine, then does the mysterious sheet changing during the 3rd one.
And for the re-Protecting, I got this:
Summary
Summary
Summary
Summary
Summary
Summary
...which shows that it's just the rapid unprotecting of sheets that is the problem, not the protecting. I added a DoEvents in there to see if that gave Excel a chance to 'catch it's breath' so to speak, but it made no difference.
I read at http://msdn.microsoft.com/en-us/library/office/ff837594.aspx#xl15WhatsNewProtectingsheets that Excel 2013 is a LOT slower when it comes to protecting and unprotecting sheets.
And at http://www.mrexcel.com/forum/excel-...-bulk-sheets-confirmed-performance-issue.html the poster says:
There is aknown issue with Excel 2013 and a protect/unprotect all macro for large numbersof sheets. In my case, I have about 150 sheets that I run a protect all macrounder a WB Open event. In 2007 it takes seconds and in 2013 it takes about 2 to 3 minutes.
Per MS development team..." this is a result of the change in Office 2013 to use a more secure encryption algorithm that takes longer to run. As a result, it takes longer to encrypt the password for password protected sheets. For single sheet protection operations this isn’t noticeable, but when add-ins or macros unlock/lock sheets in bulk the result can be noticeable. The product group has concluded that it’s something they don’t intend to fix in this version and unfortunately there are no workarounds other than to minimize the number of times you lock/unlock worksheets and only do it when they need to be modified"
But some of my other routines that don't have anything to do with sheet protection also execute a lot slower.
What's your experience with big VBA projects in 2013?
By the way, if you're interested in how my project costing model works, there's a training document I put together in PowerPoint that steps though the main stuff at https://www.dropbox.com/s/fk4bvppe929lmlr/Project Costing Model overview 20130823.pptx
For instance, in the Project Costing Model I put together (which you can access at https://www.dropbox.com/s/yklnxuyqtby7bc8/costing-model_final.xlsm but I can't upload because it's a touch over 1mb) if I run it in Excel 2010 and click the Refresh Option/Scenario icon in cell c8 on the first input sheets (called Option 1| Scenario 1 ) , the routine that it triggers executes pretty fast. (This routine executes an SQL query that does some number crunching on a temp copy of the file, then writes the result to a hidden data sheet that a whole bunch of pivots run off).
Now if I run the same routine in Excel 2013, the code errors out, because for some reason Excel inexplicably changes worksheets from the input sheet to the Summary sheet. And so when my code tries to reference a named range local to the initial sheet, I get a Type Mismatch error.
It's switching sheets in a module called ClearScenario, even though this module does not instruct Excel to switch sheets at all. Rather, it merely unprotects a whole bunch of them, refreshes some pivots, then protects them again, like so:
Code:
'We need to unprotect any sheets that contain pivots, so that pivots can be refreshed.
Sheet1.Unprotect Password:=gsPassword
Sheet2.Unprotect Password:=gsPassword ''should already be unprotected...this is just in case the user has locked it
Sheet5.Unprotect Password:=gsPassword
Sheet9.Unprotect Password:=gsPassword
Sheet11.Unprotect Password:=gsPassword
Sheet15.Unprotect Password:=gsPassword
Sheet17.Unprotect Password:=gsPassword
For Each pc In ActiveWorkbook.PivotCaches
pc.Refresh
Next
Sheet1.Protect Password:=gsPassword
Sheet5.Protect Password:=gsPassword
Sheet9.Protect Password:=gsPassword
Sheet11.Protect Password:=gsPassword
Sheet15.Protect Password:=gsPassword
Sheet17.Protect Password:=gsPasswordIf I put a breakpoint at that first line, and step through and execute the lines one at a time, then Excel doesn't change sheets (and nor should it). But if I don't step through and instead just get Excel to execute the code, then it changes sheets inexplicably.
I added a Debug.Print ActiveSheet.Name statement between all the Unprotect and Protect statements, to see what was going on.
For the Unprotect, I got this:
Option 1|Scenario 1
Option 1|Scenario 1
Scenario Overview
FTEs by Role
NPV view
Total FTEs
Summary
...which is interesting, because we see that it handles the first two unprotects just fine, then does the mysterious sheet changing during the 3rd one.
And for the re-Protecting, I got this:
Summary
Summary
Summary
Summary
Summary
Summary
...which shows that it's just the rapid unprotecting of sheets that is the problem, not the protecting. I added a DoEvents in there to see if that gave Excel a chance to 'catch it's breath' so to speak, but it made no difference.
I read at http://msdn.microsoft.com/en-us/library/office/ff837594.aspx#xl15WhatsNewProtectingsheets that Excel 2013 is a LOT slower when it comes to protecting and unprotecting sheets.
And at http://www.mrexcel.com/forum/excel-...-bulk-sheets-confirmed-performance-issue.html the poster says:
There is aknown issue with Excel 2013 and a protect/unprotect all macro for large numbersof sheets. In my case, I have about 150 sheets that I run a protect all macrounder a WB Open event. In 2007 it takes seconds and in 2013 it takes about 2 to 3 minutes.
Per MS development team..." this is a result of the change in Office 2013 to use a more secure encryption algorithm that takes longer to run. As a result, it takes longer to encrypt the password for password protected sheets. For single sheet protection operations this isn’t noticeable, but when add-ins or macros unlock/lock sheets in bulk the result can be noticeable. The product group has concluded that it’s something they don’t intend to fix in this version and unfortunately there are no workarounds other than to minimize the number of times you lock/unlock worksheets and only do it when they need to be modified"
But some of my other routines that don't have anything to do with sheet protection also execute a lot slower.
What's your experience with big VBA projects in 2013?
By the way, if you're interested in how my project costing model works, there's a training document I put together in PowerPoint that steps though the main stuff at https://www.dropbox.com/s/fk4bvppe929lmlr/Project Costing Model overview 20130823.pptx
