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

AET Excel Utilities

Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.

Update
So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!


I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.

But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.

So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).

Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).

Worksheet Tools

Rows And Columns

Formula Tools

Deletion Tools

Object Tools

Export Tools

Text Tools

Number Tools

Time And Date

Chart Tools

Path And Folder

Workbook Tools

Developer Tools

Fun And Games

Other Utilities

Cell Menu

Row Menu

Column Menu

Sheet Menu

Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.

In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.

April Fools, Excel-style.

Thought I’d share my two favorite pranks with you.

Number one: Take a screenshot of an MVP from their own website, subtly change it somehow, then email it back to them saying “Did you really say this, or was your site hacked”.

Here’s an example. Spot the original.
Jon Originaljon

Number two: Take a screenshot of someone’s spreadsheet next time they walk away from their desk, then paste it neatly over the real thing – like this one that I’m in the process of doing:

 
screenshot

 
 

Hopefully you’ll catch them before they quit Excel forcibly using End Task. (But don’t worry if you don’t…they’ll be none the wiser.)

My only problem is that I’m forced to play this last prank on myself, which kinda ruins the surprise. But I’ve still got that first one to get me through.

Any new Excel-related April Fools pranks out there from anyone? I still have a chapter to write of my Evil Genius book on arrays or something unimportant like that, but I’m running out of steam. So I’ll just put your nastiest pranks in there instead. That outta help folk out with career progression, one way or the other…

I want a global feed of them there comments

I wish more Excel blogs had a global site-wide comments feed that you could subscribe to, like this one does. (Daily Dose of Excel: http://www.dailydoseofexcel.com/comments/feed/)

Comments are where the party is. Sure, you can subscribe to comments on blogs post by post, but sometimes the article doesn’t seem that relevant to you at the time, so you don’t bother. And then someone posts some gem that you wished you had known about. And you missed it, because it wasn’t in the original piece.

And on a site like this one that predates my interest in Excel by like over a decade, I find comments from the Comment Feed often alert me to helpful stuff I’ve missed. But retrospectively subscribing one post at a time in order to catch them ain’t a serious option. So a global comments feed is one of the handiest ways for people to consume great content – often NOT written by the original author – for years to come.

Here’s the site-wide comments feeds for the main sites I know about that have ‘em:
Chandoo: http://feeds2.feedburner.com/CommentsForPointyHairedDilbert-Chandoo
Contextures: http://feeds.feedburner.com/ContexturesBlogComments
ExcelXOR: http://excelxor.com/comments/feed/
Newton Excel Bach: http://newtonexcelbach.wordpress.com/comments/feed/
Bacon Bits: http://feeds.feedburner.com/CommentsForBaconBits
PowerPivotPro: http://www.powerpivotpro.com/comments/feed/
Excel & UDF Performance (Charles Williams): http://fastexcel.wordpress.com/comments/feed/
YourSumBuddy: http://yoursumbuddy.com/comments/feed/
MyOnlineTrainingHub (Mynda Treacy): http://www.myonlinetraininghub.com/comments/feed
Jon Peltier: http://feeds.feedburner.com/CommentsForPtsBlog
RAD Excel (Colin Legg): http://feeds.feedburner.com/CommentsForRadExcel
Andrew’s Excel Tips: http://andrewexcel.blogspot.com/feeds/comments/default

My favorite used to be the Bacon Bits comments feed: most of those comments were as risque as Mike’ articles. But then he had to go spoil things by adding a spam filter.

Ah well…the Captcha almost makes up for it:
 
Bacon Bits Captcha

Anyone else have any great global comments feeds on the main Excel sites out there worth sharing?

Today() ain’t so bad…

Finding non-VBA alternatives for the volatile TODAY and NOW functions is tricky, but here’s an example of some creative workarounds for the Evil Boss’ report generator.
 
Non volatile today 2
 
 
That last one that uses a dropdown I pulled from a at the ExcelHero LinkedIn group . It’s pretty clever: what you do is create a small Table somewhere, and populate it using the naughtily volatile =TODAY() function, as well as the words “Please choose…” .
 
Please Choose
 
(An alternate wording to “Please choose…” would be “Choose wisely…”).

Then you point a dropdown list at that Table. And then you assign the name TodaysDate to the cell that the dropdown lives in, and use that instead of TODAY() throughout the rest of your workbook.

The beauty of dropdowns is that while they may reference that volatile function, as soon as the Evil Boss makes a choice, the choice gets written to the cell as a string, and not as a reference to that volatile function. So it IS today’s date, but it is NOT volatile. Of course, you need a big obnoxious message pointing at it so that:

  1. The Evil Boss remembers to fill it out today; and
  2. The Evil Boss also remembers to update it tomorrow

But obnoxious is my middle name.

The Amsterdam Excel Summit Last-minute discount

Hi everyone,

Our event is coming real soon now and we’re very much looking forward to it. We have outstanding speakers and excellent content, so everything is lined up to make this a superb Excel event.

To entice the undecisive Excel lovers to make up their minds and subscribe after all, we decided to make it even more attractive to attend.

As of March 27st, 2015 we offer a € 200 last-minute discount per attendee for both days and € 100 for one day!

Register now at http://topexcelclass.com/index.php/amsterdam-excel-summit/registration/ and meet us on April 13th and 14th in Amsterdam.

Regards,

Jan Karel Pieterse

www.jkp-ads.com

What help forum would you recommend, and why?

One of the key premises of my book is that Excel has many passionate online user communities. And on that subject, I say:

If you don’t utilize them, you are missing out. If you tap into them, your work isn’t just based on what you know, but on what the collective we know. Which is pretty much everything, between collective us.

One of the things I cover in the book is how to get great results out of a help forum. Ah, but which one?

In the past, I’ve spent some time hanging out at Ken Puls’ ExcelGuru forum – although not too much for the last couple of years. More recently, I’ve been hanging out on the Chandoo forum, although not for the last 6 months due to spending more and more time on the book.

I guess why I started hanging out on the Chandoo forum more and more was that the volumes of questions were good but not overwhelming, meaning you could not only keep an eye on all the questions, but all the answers. Which really fostered a great little community among the regular respondants, I thought.

Of course, I always end up on the Mr Excel forum when Googling – and when I still had my VLOOKUP training wheels on, I learnt some incredible things there from the likes of Fazza and Aladin Akyurek (who has taken part in 68k posts, would you believe). But I’ve never spent much time monitoring content there, largely because of the overwhelming volume of it.

Increasingly I find Google directing me to Stack Overflow. For some reason I don’t really ‘get’ SO. I find it kind of scary looking, from a newbie’s perspective. Anyone feel the same? (Obviously not Dick – he’s been hanging out there for 6 years 6 months, and was last seen there 10 hours ago. Not that I’m stalking.)

Anyways, what forum would you recommend, Hive Mind, and why? I haven’t finished this part of the book yet, and would appreciate your inspiration and wisdom, if you have some to contribute.

6. Doug

VLOOKUP: V is for Volatile.

Well THAT sure got your attention.

I was doing a bit of research for the book (the only bit, mind), and I came across this great old thread on VLOOKUP vs INDEX/MATCH.

There’s a few false starts in terms of test methodology until Jon von der Hayden puts things back on the right track about halfway through the thread.

Conclusion is that INDEX/MATCH is probably way to go, but there’s not a great deal in it.

But I think there’s another important point in the INDEX/MATCH combo’s favor that has been overlooked in this thread: and that’s this bit in bold:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The fact that a VLOOKUP functions points at an entire table of data – even though it only ever actually makes use of two columns of it (the key column on the far left, and the nth column given by the col_index_num argument) – means that any time you change any cell in that table, your VLOOKUP is going to recalculate. And then so are ALL formulas downstream of those VLOOKUPS. It’s exactly as if VLOOKUP is a volatile function, where changes to that that Table is concerned.

Whereas with INDEX/MATCH, your functions will ONLY recalculate if you change something in the specific columns that the INDEX and MATCH combo references.

Given that, I’ll take INDEX/MATCH any time.

One thing still puzzles me hugely about VLOOKUP though: the recalculation time incurred from updating the lookup table seems much much longer than the recalculation time incurred from changing an input parameter for the VLOOKUP itself. And the same goes with INDEX/MATCH.

Take the situation where you’ve got a VLOOKUP and Table that looks like this:
 
vlookup issue
 
 

The KEY column is just the numbers 2 through 1,048,576 sorted in random order. And the Value column is just the row number. I won’t post a sample file because it runs to about 20MB, but it’s easy to set up yourself in about 20 seconds.

Changing the orange input cell triggers the VLOOKUP, which gets the right answer pretty quick. I haven’t timed it, but we’re talking ‘blink of an eye’… even when I tell it to look for a number that I happen to know is right at the bottom. Really fast.

But if I type anything in that middle column in the Table – which also causes our VLOOKUP to recalc – Excel acts really really sluggish. Maybe a second of delay in some cases, plus it makes my screen flash.

Which is weird, because it’s the same one VLOOKUP that gets triggered in both cases.

Why would that be?

—UPDATE—
If you haven’t already, go and read Charles Williams’ awesome article Developing Faster Lookups – Part 1 – Using Excel’s functions efficiently – updated

Look for the bit “Large dependency ranges trigger unnecessary Lookups”.

Read it, and don’t weep.