I’m not the kind of guy to upgrade too soon. I spend a lot of time in VBA, so new features in Excel don’t generally inspire me. Recently a client of mine upgraded to 2013 with, let’s say, disasterous consequences.
First, there’s the Single Document Interface (SDI). That’s where every document is in it’s own application container. But not really. It’s all depends on where you draw the line. If you have two linked documents open and you calculate, both documents go into the calculation tree. So it’s not exactly separate application containers. Its main purpose is to allow you to put one spreadsheet on one monitor and a different spreadsheet on another monitor. A worthy goal.
My problem with SDI is that I was still using Excel 2003 menus for five addins. They don’t work so well in SDI. It forced me to rewrite them using the RibbonUI – not a bad thing, but my client probably wasn’t including that cost in the cost of upgrade. But we have fancy new ribbon icons, so all is well, right?
Next I learn that Excel uses SHA1 to encrypt spreadsheets instead of the previous method, which I assume was some sort of XOR with a one-character password. With the new encryption scheme, protecting and unprotecting a worksheet in code takes a touch longer. And by touch a mean a shit-ton.
I don’t know what exactly to do about the protection problem. My code opens templates, unprotects them, writes stuff, combines them into a final report, and reprotects them. The first thing I did was get stingy about my protection. Whereas before I was pretty liberal, protecting and unprotecting whenever I needed to, now I’m unprotecting and reprotecting exactly one time. That made a little difference, but not enough. As an experiment, I removed all protection from the templates and removed all the unprotecting and reprotecting from my code. Here’s the before and after timing of that.
| Process | Cum Time | ThisProcess | Description |
| Start | 0 | ||
| End | 18,700.37 | 18,700.37 | Current 2013 |
| Start | – | ||
| End | 10,149.81 | 10,149.81 | No protection 2013 |
That cuts it in half, although I can’t release this into the wild with no protection. My client reports that it takes ~90 seconds on his machine. Not surprisingly, my desktop has better specs than his company-issued machine. But I think it all scales. I can cut his time down to ~45 seconds by removing the protection. Even if we could stomach having unprotected templates, that’s not good enough.
I sprinkled some splits around the code to see if there was one area causing the problem. Then I ran the same code in Excel 2010. There must be something else in 2013 that’s taking a disproportionately long time compared to 2010.
| Process | Cum Time | ThisProcess | Description |
| Start | – | ||
| Open Templates | 1,544.60 | 1,544.60 | |
| Fill and validate dump | 1,630.98 | 86.38 | |
| Dump page setup | 1,763.43 | 132.45 | |
| Store dump properties | 2,062.55 | 299.12 | |
| Fill solutions | 2,186.50 | 123.95 | |
| Fill waves | 4,380.67 | 2,194.16 | |
| Sort Solutions | 4,388.46 | 7.79 | |
| Fill Finals | 9,937.29 | 5,548.83 | |
| End | 9,937.60 | 0.31 | No protection splits 2013 |
| Start | – | ||
| Open Templates | 513.72 | 513.72 | |
| Fill and validate dump | 547.94 | 34.22 | |
| Dump page setup | 571.68 | 23.74 | |
| Store dump properties | 666.54 | 94.86 | |
| Fill solutions | 735.07 | 68.54 | |
| Fill waves | 2,146.86 | 1,411.78 | |
| Sort Solutions | 2,154.69 | 7.83 | |
| Fill Finals | 4,288.38 | 2,133.69 | |
| End | 4,288.79 | 0.40 | No protection split 2010 |
Nope. Everything just takes longer as far as I can tell. I’m going to increase my number of splits, but I’m not hopeful I’ll find the golden key.
Have you experienced performance problems with 2013? What caused them? How did you solve it?
yes, I have, even as far back as 2007. when you have to iterate through rows of data and do a find on a certain value in each row, forget it. it’s takes at least 2-3 times longer than 2003.
I wish I knew how to fix it. in one instance I rewrote the a code module to get everything into arrays, then used the match function. this was actually faster than in 2003 using find by a wide margin.
2003 took about 40 seconds for the entire operation
2010-2013 took over a 90 seconds to run the entire operation
2010-2013 about 20 seconds to run the code module before I rewrote it
now in the arrays, 2010-2013 takes about 3 seconds to run the new code. and with the use of some pivot tables, 12 seconds to run the entire operation
I’ve both 2010 and 2013 on my PC at home and 2013 is noticeably slower when I shut it down. Not that bad. It was a fair bit slower at work but my PC there was low performance. Now I use a better PC, it seems quick enough…
Not sure if this is correct but I was under the impression that, on my old PC, 2013 was trying to save everything to OneDrive. And there were some “features” with the version of 2013 I had at the time, that have since been fixed in the version on my new PC. Just conjecture though. Being able to use multiple monitors was worth the speed loss, at least in my case.
Sorry I can’t help with anything but hunches and guesses.
Usually if something is running that slowly its because of cell-by-cell operations …
But I don’t know why your code runs slower on 2013 either: usually the difference in VBA performance between versions is caused by Read/write overhead, but in the benchmarks of 2013 I did a while ago it looked fairly similar to 2010.
https://fastexcel.wordpress.com/2012/11/09/getting-cell-data-with-vba-and-c-benchmarking-excel-2013/
Some things to check:
– Conditional Formatting
– “Improved” anti-virus checking
– close all Com Addins
– Pagination
Dick, your tables could do with some headers. Took me a bit of examination to realize that the left column is the cumulative time and the right is segment by segment.
I’ve only noticed problems with the protect/unprotect time in one of my applications, which is ridiculously slow. Almost unworkable. Thankfully the place I designed the app for still only has Excel 2010 so its not an issue. Yet. But when they upgrade….
If things are going to stay this slow in 2016, I think we need some new level of protection.
What’s more, when unprotecting some sheets in this application in order to refresh a pivot, Excel 2013 switches sheets half way through for no reason whatsoever. Here’s the offending code. Tell me what’s in there that should cause a sheet select change…
I had to add this bit around it to stop 2013 straying
Jeff, I ran exactly on this issue in a recent project. Worse yet, Excel doesn’t change everytime to the same sheet, the behavior is random. It could change, at the end of the code, to sheet A, B or C, but not the original Sheet.
@Andrew
By default Excel tries do save everything on OneDrive, and I also don’t like that. However, you can change that in Options/Save. Check ‘Save in the computer by default’. I also recommend unchecking ‘Show more locals to save, even if it is necessary to connect’ and check ‘Don’t show Backstage when opening or saving files’.
*my Excel is in portuguese, not in english. I translated the expressions above.
@Felipe
Thanks!
On my PC at work, the “Save to Computer by default” option is unchecked. Can’t recall if I did it, but probably did. Will check my home PC too just to be sure :-)
The Backstage option got turned off long ago. It was driving me crazy. Was not aware of the additional places to save option, I turned it off now and will see how things go.
I don’t have Excel 2013, (I use this on 2010) but how about:
Then your code can make all the changes it likes without having to protect / unprotect.
In fact there are a whole load of exceptions to a general worksheet.protect:
https://msdn.microsoft.com/en-us/library/office/ff840611.aspx
@PeterB – Unfortunately there’s a whole heap of things you can’t do even with userinterfaceonly:=True. Refreshing PivotTables is one of them.
@Jeff OK, I don’t expose pivot tables to the end user. I have xlVeryHidden worksheets (unprotected) with pivot tables on them to do intermediary calculations.
I also can’t see anything in Dick’s post about pivot tables.
@PeterB – yes, there’s nothing about PivotTables in Dick’s post. The subject came up purely because of my code snippet above where I show that in Excel 2013 for some reason not only is unprotect slow, but it can cause Excel to strangely change the active sheet if you are unprotecting lots of them – with my unprotecting in this case being unavoidable because I needed to refresh a PivotCache.
This app uses PivotTables to report output to the end user. Apart from the slow unprotect/reprotect, it works a charm.
Dick,
regarding the slow-down due to the protection: As you mentioned, they changed the hashing to SHA1 which is way slower. If my application doesn’t need to be ultra-secure (which it wasn’t in 2003 anyway), I now leave away the password at all – but still apply the protection. This way the user cannot screw up the tool (unless he really wants to) – and the code is still fast…
Apart from this, I didn’t notice a large slowdown when migrating from 2010 to 2013…
Unfortunately, Microsoft no longer cares about execution speed and are only interested in pretty.
Hi Dick,
I recently ran into the protection Bug when my users upgraded to 2013.
I remove protection for two data sheets in the same place and this causes Excel to activate one of these sheets and leave it active when the code finishes.
If I only protect one sheet it works fine. It took a long time to track this error down as if I step through the code the issue doesn’t arise.
Paul
Dick –
Inspired in part by Steve Bullen’s and Rob Bovey’s admonition and reminder to look for code execution speed top-down, I’ve been moving away from VBA. Python is a very fast environment for development, and with a couple of other packages (NumPy, SciPy come to mind), very sweet indeed. All free, open-source, actively developed by folks who know what they are doing and are not hampered by (IMHO) idiotic MS lame-ness – Further, there are nice tie-ins with Excel.
Julia is a relatively new language which can tie into C and of course Python. It simply blazes for the sort of work I do. And there is always R.
A very sweet bridge from Excel to Python is DataNitro. One is able to quickly retrieve data in ways that simply cannot be done without crashing VBA. Check it out. Because it ties into Python, one can extend it in pretty much any direction. That through it one can DRIVE Excel and VBA, Julia, and C is huge, at least for me.
No, I am not some salesperson for the developers/investors/whatever. You, Chip, Jon, Tom O, Harald, Laurent know me well enough to know that I look for ways I can extend my Excel-involved modeling, and would NEVER cave to a company for an endorsement (though many, especially in pharma, have asked). Anyway, my 2 cents.
Highest regards, and so many thanks for your site and all you have done for our community over the years,
Dave Braden
Dave – I don’t see Python as a replacement for VBA, not unless Microsoft provide built in support for it anyway, but I do find it a convenient way to access very fast compiled routines in packages such as Scipy. I have been using ExcelPython, which is free and open source, and provides a convenient link to Python via VBA.
Another (possibly) interesting development is that I read recently that Microsoft are now developing for R, so it will be interesting to see what comes out of that.
Just been looking up Julia (which I hadn’t heard of before).
They have a nice introduction at their web-site (have a look at the performance comparison):
http://julialang.org/
and there is a good discussion of pros and cons at:
http://www.econometricsbysimulation.com/2014/04/a-weekend-with-julia-r-users-reflections.html
But how do you connect Julia with Excel?
So I had code that would execute within 2 seconds in Excel 2007 (with a few thousand cell edits, nothing extreme). In Excel 2013 it takes at least 90+ seconds for no apparent reason.
I’ve spent 2 days trying to figure out why, tried everything I could find on the subject: optimized code, disabled animations, registry edits, etc… Nothing helps.
And then I saw it, I could speed up and slow down the code simply by moving my mouse in or out of the Excel Window!
And so, here is the magic line of code:
Processing time in Excel 2013 after implementation? Back to 2 seconds!
OMG! WTF MS?
In my experience this has never been an issue in previous versions. But in Excel 2013 the arrow flashes when you do many cell edits and apparently takes up an enormous amount of processing power if you don’t explicitly tell it not to. The answer has literally been flashing itself at me for months …
I haven’t seen this anywhere else, so I hope this helps someone.
Yours truly, long time lurker,
Ambro53
Dick, ever get to the bottom of this?
Excel 2013 at work has been very naughty recently shutting down. It’s been taking forever. And when I checked the Task Manager, I was surprised to see a duplicate instance running in the Windows processes section. If I opened a new instance and closed it, that instance would show up too, etc, etc. Eventually they’d shut down but I could grow a beard while waiting for that to happen.
Anyway, just tried deleting my xlb file. Saw a heap of weird System files in the same folder. Opened some up with Notepad and saw all kinds of garbage. So I deleted them too.
And now Excel closes down in a blink, with no ghost instances showing in the Task Manager. Yippee.
No, I haven’t. We got it to a tolerable state, but no where near idea. I haven’t tried Ambro53’s comment yet, but I intend to. I’ll check my xlb file too.
Thanks Ambro53, it works like a charm. Reduced the code execute time from 12 to 3 seconds
Changing the cursor did not work for me.
We recently had a customer complaining about the performance of our comparison addin Synkronizer. When it started to highlight the cells with differences it just inexplicably bogged down. So we asked the customer for his (elaborate) workbooks to find the cause.. We got his books: each was sized around 6MB, 40 sheets, 20 code modules, and plenty of formatting..
We tracked and traced.. but couldn’t find fault with our code. Yes it had to highlight some 50’000 cells, but that should take a few seconds max. Not 10 minutes!
To isolate root and cause we created a simple vba routine that creates a blank workbook and sets the interior color of X cells to a specified color. For 100 cells that normally takes a couple of milliseconds.
But if his workbook was open it took a whopping 6 seconds for just highlighting 100 cells.. We weren’t calculating, nor reading or writing in his workbook, it was just open and minimized.
And yes we are carefull with ScreenUpdating, Events, Calculation, Selects and all the other usual suspects.. for several hours I was stumped and getting more and more frustrated.
We disabled his code. We replaced all his formulas with values. We removed all his (conditional) formatting.. All to no avail.
But his books did have plenty of graphs and lots of checkbox controls on his input pages… and that brought me to a setting I have never, ever had to use before:
Workbook.DisplayObjects=xlHide.
Et voila! Down from 6 to less than a second. Although not perfect it’s a whole lot better.
Those pesky 400 or so “objects” apparently are (globally) volatile and do not adhere to ScreenUpdating or whatever other settings to keep quiet.
I never traced exactly if the graphs or the checkboxes were to blame.. it’s just another Excel quirk that you need to workaround.
Btw: it’s not just xl2013 xl2010 behaves the same. Maybe OffTopic but maybe it will help somebody who read this.
Greetings from Amsterdam.
Jurgen
Did you mean .DisplayDrawingObjects? I don’t see .DisplayObjects.
typo! yes s/b DisplayDrawingObjects
Great find! I am really surprised that its still slow when the workbook is minimised: usually stuff like that only gets re-rendered when its visible on the screen.
Darn. It didn’t help my speed and I was hoping there was something I was missing. I didn’t really expect it to work since there aren’t many objects on there, but I’m desperate.
Dick, I’d be happy to have a look at it. Zip and mail!
Ambro53, you have my eternal gratitude for your tip! I’m running numerical model in VBA (pretty much just using the spreadsheet for I/O). There is minimal reading & writing of cells, but thousands-to-millions of FLOPs running in the background. This ran in the blink of an eye in 2010, but took several seconds in 2013. Turning off Application.Calculation and Application.ScreenUpdating did not help, but Application.Cursor = xlWait solved it! Thank you! – Erik
Ambro53 thanks so much!
Your solution worked perfect for me. Running Excel 2013 and a simple macro took ages with the cursor continuously ‘refreshing’ while the macro was running.
Not sure if it matters but I included ‘Application.Cursor = xlNormal’ at the end to revert cursor to normal.
Hello to all. I have developed an Excel VBA application which run pretty smooth with a Toshiba computer with Corei5 running Windows XP and Excel 2013. Unfortunately this computer was stolen, and I had to buy a new one, less powerful (corei3), running under Windows10 and Office365. Since that time The SAME application run awfully slowly . Having changed so many things at once, I was really not knowing who to blame. Thanks to your thread, I found the most guilty part : Protection ! (in fact other improvements may have brought their improvements but the last I tried and the obviously most important in my case was Protection). I read with interest the explanation from Peter ‘They changed the hashing to SHA1 which is way slower’. I feel this is a shame to make changes that may have such an impact on behavior without keeping the previous code alive : Microsoft should have created a new Worksheet.Protect Method and let the user decide to use the previous or new one !
Anyway thanks to all of you for your smart advices
Hello, I know I am late… but after few years my company is moving in from MS Excel 2010 to 2013 and we are running problems with macros taking a long, long time to run, I am not a MS Excel specialist but I do need to fix this problem, for MS Excel 2010, macros takes 2 sec to complete this operation, but for MS Excel 2013 takes like 2 min and is not acceptable for my users to wait a lot of time when they are working with this macro, here is the code:
Private Sub UnprotectAllSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect “passxxx”
Application.Cursor = xlWait ‘ — I put the code as Ambro53 said, I am not sure if is correct, but is not working
Next ws
End Sub
Sergio: There’s no easy workaround to this incredibly slow unprotection. That said, one way to speed things up is to reduce the amount of sheets that need to be protected/unprotected. I do this by firstly hiding any sheets that the user doesn’t need to actually see by setting ws.Visible = xlVeryHidden, and then just protecting/unprotecting particular sheets in need, rather than doing a blanket protect/unprotect across all sheets.
Hi guys,
The most effective method of resolving the speed of VBA in versions Excel 2013 and above tends to be UserInterFaceOnly placed in the “workbook module”.
It may slow down access to the workbook when you open. However, this should resolve speed issues after this point, where protection is required.
In the example below you would simply be changing the password conan:
Private Sub Workbook_Open()
Dim wSheetName As Worksheet
For Each wSheetName In Worksheets
wSheetName.Protect Password:=”conan”, UserInterFaceOnly:=True
Next wSheetName
End Sub
I experienced the same issue, but realised the .Unprotect and .Protect were inside the loop in the code, which looped through rows to update values. Once I kept them out of the loop, things were amazingly lightening speed.