Excel 2013 VBA Unreasonably Slow

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
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
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
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?

Paste and Transpose icon in QAT crash Excel 2013

Hi all

I add a new page on my site with a workaround for this problem. I hope that there are no more of this problem icons for the QAT.

Not easy find the problem with bugs like this, you not think that an Excel icon in the QAT can crash Excel.

Paste and Transpose icon in QAT crash Excel 2013

Regards Ron de Bruin


A new tool: Trusted Document Manager

Hi everyone!

I have just published a new tool today, Trusted Document Manager. This little tool enables you to manage your list of trusted documents. Currently, Excel only allows you to either leave the list intact, or delete the entire list. This means all of your currently trusted documents become untrusted again so you have to enable macro’s on all of them once again. The tool allows you to remove just one file, remove an entire folder or even an entire drive. Also it offers to possibility to remove files which no longer exist from the list.

This is what the tool looks like:




Jan Karel Pieterse

Office 2013 VBA help files

Hi All,

Microsoft announced today that the Help files for Office 2013 VBA have been made available for download.
The help files are for:

Office Shared

Note that these files do not offer context sensitive help (F1), they are presented as stand-alone help files which you have to open manually.

I invite you all to give your opinions about VBA help. Make sure you include the Office version with your comments.

What’s New for Excel 2013 Developers

What’s New

Apps for Office in Excel

Microsoft Excel 2013 brings the web to Excel. New apps for Office offer a wealth of interesting, new scenarios and solutions in an easy-to-use environment for dynamic web application development. You now can use popular dynamic languages (such as Python, PHP, Perl, and JavaScript) and web development tools (such as Microsoft Visual Studio 2012) for application development.

This has potential, but I have yet to see an “interesting” app. No, I don’t consider showing my sales data on a Bing map or displaying a Wikipedia page interesting.

New Worksheet Functions

And you can now use web service functions (in the Web functions category) to anonymously access REST Web services.

This makes me happy. I don’t know how I’m going to use it yet, but I know it’s going to be good.

Single Document Interface

SDI means that each workbook will have its own top-level app window and will have its own corresponding ribbon.

This makes me sad. I’m sure I’ll get used to it. I’m used to it using Word, but I don’t care about Word and I almost never have more than one Word document open. Now that Alt+Tab and Ctrl+Tab are going to be the same thing, I need a new shortcut.

Protecting sheets takes longer

Protecting sheets takes much longer than in previous Excel versions…This is by design, so there is no workaround.

This makes me really sad. Of all the worksheets in all the world, what percentage of them need this level of protection? I tried to figure out that percentage in Excel, but it keeps showing zero because it only has 15 digits of precision (that means it’s really small). So the rest of the world gets to slow down for a handful of sheets that need this protection. You can protect your crappy worksheets all you want, you big Wall Street fatcats, but protecting formulas that aren’t right doesn’t make them right.