Macro in Excel 2016 runs MUCH slower when ANOTHER spreadsheet is open?

[Ed_Collins]

Greetings,

I purchased a new, lightning fast computer this week, to replace a six year old system I had. I also purchased Excel 2016, since I no longer had the original 2007 Excel installation disks to install onto the new system.

I've been playing with Excel 2016 now for a couple of days. Almost instantly, I noticed that with Excel 2016, one of my macros that took less than one second to run on my old, slow system using Excel 2007, now took about 26 seconds to run on my new system... with a much faster processor!

WTF?!!

The macro is actually quite simple. It doesn't write to the screen, it doesn't open any files, it doesn't loop through every row on the spreadsheet, etc.

I spent time researching the problem and quickly found out that many others have had the same or similar problems, but none of their suggested solutions worked for me.

However, I finally figured it out on my own. I noticed that if I CLOSE another spreadsheet that I just happened to have open at the same time, the macro would then run almost instantly... also less than one second to run!!

Again, WTF??

So my question is, does anyone know WHY having a second spreadsheet open (that incidentally doesn't contain any macros at all) causes the first spreadsheet's macro to take 26 times longer to run?

The code is much too long to post here. But note that it doesn't access any other spreadsheets or files or anything like that.

Idea: I think it might have something to do with Excel 2016's "feature" that another instance of Excel "appears" to be open, with each spreadsheet you have open. (With Excel 2016, you no longer have the option of having several spreadsheets open in the same window.) I say "appears," in quotes, because only one copy of Excel is actually open.

As always, thanks in advance for any replies.

[shg]

I could only speculate that the other open workbook had lots of volatile formulas that recalculated every time the code made a change in the workbook it was operating on.

I've not used Excel 2016 (or 2013), and there may be more afoot, but that aspect of behavior has never changed.

[nick93]

Idea: I think it might have something to do with Excel 2016's "feature" that another instance of Excel "appears" to be open, with each spreadsheet you have open. (With Excel 2016, you no longer have the option of having several spreadsheets open in the same window.) I say "appears," in quotes, because only one copy of Excel is actually open.

Excel 2013 is the same in this aspect (you also can't have several workbooks in a single window) and I never noticed anything of the kind.

Just curious, perhaps this might help, try adding
Application.Cursor = xlWait
in the beginning of the macro and
application.Cursor =xlDefault
at the end. See if it helps.

[Ed_Collins]

Just curious, perhaps this might help, try adding
Application.Cursor = xlWait
in the beginning of the macro and
application.Cursor =xlDefault
at the end. See if it helps.

Nick,

Thanks for the suggestion. I tried that and it did not help at all.

I'll continue to experiment in my attempt to pinpoint exactly WHY it runs so much slower, just because another workbook (that is just sitting there doing nothing at all), is open.

[shg]

(that is just sitting there doing nothing at all)

Every time you make a change to any cell, Excel recalculates all volatile formulas in all open workbooks.

If you want to see if that's the problem, set the EnableCalculation property to False for all worksheets in that other workbook.

[xlnitwit]

I'll continue to experiment in my attempt to pinpoint exactly WHY it runs so much slower, just because another workbook (that is just sitting there doing nothing at all), is open.

Hi,

Is it any other workbook being open, or one particular one?

[Ed_Collins]

Hi,

Is it any other workbook being open, or one particular one?

Good question. I opened up a bunch of other workbooks, and the macro ran fine, and so I was about to respond and say yes, it's just one particular workbook. But then I tried another one, that has a bunch of vlookup formulas, and sure enough, the workbook with the macro in question ran very, very slowly again.

So no, the problem isn't related to a particular workbook.

[Ed_Collins]

... set the EnableCalculation property to False for all worksheets in that other workbook.

shg, forgive my ignorance but how do I do that?

Recall that other workbook doesn't have any macro code at all... it's just an excel file with multiple worksheets, and yes, some of them do have formulas.

It doesn't make any sense to make any changes to that workbook. (In fact, most of the time it most likely won't even be open when the workbook with the macro code is open and running. It just happened to be open at the time.)

So I assume I need to add this EnableCalculation property to False code in the macro-enabled workbook. But when I do at the beginning of the macro, for the worksheet that is active, it doesn't help. It still runs horribly, horribly slowly, when the other workbook is open.

I assume my code is wrong. Obviously I need to specifically say to enable calculation to false to any and all other workbooks that may (or may not) be open at the time. Can you tell me how to do that?

[shg]

Please Login or Register  to view this content.

It's just to find out if that's the problem.

[Ed_Collins]

Okay shg, good work. (As usual.)

I had a little bit of a problem compiling your code but I got the following to work:

Please Login or Register  to view this content.

And it did work. With the above code, the macro now runs almost (but not quite) as quickly as it does when the workbook isn't opened at all.

So I guess that's the solution. I just need to turn off calculations for any workbooks that may or may not be open. Thanks.

[shg]

Or write fewer volatile formulas.

[Ed_Collins]

Or write fewer volatile formulas.

Ah. Yes. But if a user is using my workbook (the one with the macro), I wouldn't have any control over whatever workbooks they may or may not have open at the time. The other workbooks that are open wouldn't be mine.

I wouldn't want my macro to crawl like a snail and take forever, just because they happen to have a bunch of volatile formulas in their own open workbooks.

They wouldn't necessarily know the solution to the problem was to simply close their workbooks, and it would reflect back on me, thinking it was my macro that was just slow.

This is why, even after I figured out what the problem was (another workbook being open), I wanted to know why.

Thanks again.

[shg]

You're welcome.