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.
| Start | 0.00 | 0.00 | |
| End | 18,700.37 | 18,700.37 | Current 2013 |
| Start | 0.00 | 0.00 | |
| 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.
| Start | 0.00 | 0.00 | |
| 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 | 0.00 | 0.00 | |
| 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?
