Macros Run Slower in Newer Excel?

Written by Allen Wyatt (last updated April 13, 2021)
This tip applies to Excel 2010


11

Dave has a Windows XP system that still has an old version of Excel 97 on it. That system has a macro that is used to process information in a worksheet. It is able to process 500 rows of information in about 12 seconds. On a newer machine, running Excel 2010 under Windows 7, the same macro operating on the same data takes approximately 3 to 4 minutes to run. Both machines have the same amount of memory (4 Gb) and comparable processors. Dave wonders why the macro runs so much slower on the newer version of Excel.

It's very hard to make anything but the most general of comments without actually seeing the code, but that doesn't mean there isn't anything to say. :>) The first thing you'll want to do is check to see how your macro is doing its work. For instance, does it step through all the rows of the worksheet to do its processing? (Even though the macro is processing 500 rows of information doesn't mean it isn't stepping through every row on the worksheet.) The reason this might be critical is because earlier versions of Excel only used 65,000+ rows in a worksheet, whereas the latest versions can handle 16 times as many rows (over 1,000,000). If your macro was taking 12 seconds to run before, 16 times as long would be 192 seconds, which is between 3 and 4 minutes.

There are also reports that Excel 2010 communicates rather regularly with the printer driver, in a way not done in previous versions of Excel. You can figure out if this is slowing down your macro by turning off the communication at the start of your macro:

Application.PrintCommunication = False

At the end of your macro, remember to set the property back to True so that Excel can again communicate with the printer.

It could also be that there is something different with the newer machine, even if the difference is subtle. For instance, the newer machine could have different background programs running that the older machine—anything from an anti-virus program to voice recognition software. In addition it might have some different Excel add-ins loading that aren't on the earlier system. You wouldn't think that such things would affect macro performance, but they really could. You won't know, of course, until you track down what is loading, disable it, and then try running your macro again.

Also, it could be that the newer machine has some external devices that it needs to poll periodically which could be slowing things down. I have a system in my office that has an external hard drive, connected through a USB port. At times, the system itself (Windows) needs to go out and power-up the external drive, and things essentially stop on the computer while this happens. Guilty devices could be USB attachments, scanners, network drives, etc. It can be very frustrating, especially when it slows down something that shouldn't take long at all.

A good suggestion in this instance is to not rely solely on what you find with a single Excel 2010 system. If you have access to several machines—perhaps even some out of the office—try the macro on them to see what happens. If it runs faster, then you know that there is something unseen at play on the slow 2010 system.

If you find that everything really is "equal," then you may need to get into the VB Editor on the system and start doing some timing of the various parts of your macro. This is tedious, but it can help you narrow down where, exactly, the macro is bogging down.

If you decide to go this latter route, you'll find it worth your while to invest in a good Excel VBA programmer's reference book. There are several on the market, so shop around a bit. (You can't go wrong with anything authored by John Walkenbach, and I've heard good things about Excel 2007 VBA Programmer's Reference.)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12440) applies to Microsoft Excel 2010.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Retrieving the Last Value in a Column

Need to get at the last value in a column, regardless of how many cells are used within that column? You can apply the ...

Discover More

Extracting Hyperlink Information

In Excel, a hyperlink consists of two parts: the text displayed for the link and the target of the link. You can use a ...

Discover More

Selecting to the Bottom of a Column in a Macro

Need to select a range of cells in a column? This tip can help, as it shows how to select from a specific cell all the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Replacing Some Formulas with the Formula Results

Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...

Discover More

Generating a Keyword Occurrence List

Need to pull a list of words from a range of cells? This tip shows how easy you can perform the task using a macro.

Discover More

Documenting Changes in VBA Code

Your company may be regulated by requirements that it document any changes to the macros in an Excel worksheet. Your ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 6?

2021-02-19 08:33:48

Pedro B

I'm using also version 1902 in Windows 7 and 2012 in Windows 10 (2101 since a few minutes ago) and all my Excel macros that export data to Word documents got about 6x slower in Win 10. This is a big problem because I often have projects that involve generating thousands of docs like this, today is the case.

By debugging the code I confirmed what was obvious from observing the execution, that the Range method Find.Execute was now very slow.
Since all my macros of previous projects that do this processing were affected in Win 10 / Excel and Word v2012, the problem had to be in Win 10 or Office, because the macros continue to work fine in Win7 / v1902.

Even if CPU usage by Excel and Word were low, as was overall CPU usage, I took one more look at the Task Manager and the only thing I could see were some very frequent spikes in Microsoft Defender (less than 20% of one CPU though). Turned off real-time protection and the performance went back to normal.
Go figure because no file is written while the find and replace is running, only at the end of replacing tens of tags in the documents but still each execution of Range.Find.Execute was very slow.


2021-01-25 22:37:06

Robert Benz

Well I found that the version 2012 is more than 100 times slower than version 1902 in processing a do loop in finding a string in an column of a sheet. It is so slow I am wondering if the Do Loop is to be avoided if not removed as it totally breaks and finally crashes the Excel program.


2017-03-30 08:37:31

Chris

Thanks for this tip. Was running macro on 3 different machines. 2 of them took over 2 min to run and the 3rd took less than 10 seconds. I disabled the network on one of the slow machines and macro ran in less than 10 seconds. Tried adjusting excel settings but couldn't figure it out. Finally realized fast computer had a pdf printer as default vs the network printer. A google search led me to this article and putting Application.PrintCommunication = False in my macro fixed it. Also added Application.ScreenUpdating = False and gained a little more speed.


2016-06-06 19:29:38

Rob

You have gone from a Windows XP / Excel 97 system to a Windows 7 / Excel 2010 system, with the same amount of memory (4 Gb) and comparable processors.

I'm no hardware expert, but it has been my experience that new operating systems and software generally require faster processors and more RAM. This may be contributing to some of the slowness.


2015-04-14 05:51:48

John

I'm also stumped by wildly different performance for the same functions and files on different PCs. We have 4 that are all the same model, same configuration, and same software (built from a "golden image". One of the four took about 100 times longer to calculate a sheet with a few thousand functions in it. A second PC, that had been running the excel/vba process quickly also became about as slow as the worst PC when it was "upgraded" from Office 2010 to 2013. When a sheet is calculated with multiple cells calling the same VBA function, does the function "compile" multiple times, or does it compile once and run multiple times? What are the steps in the process where things could be affected by SSD read issues or slow sectors on a hard drive? What else am I missing?


2013-01-15 07:13:10

Dave K

Thanks for the ideas there. I'll try them out. A couple of comments...

Presumably to output to the status bar less frequently implies a progress calculator to decide when to output the message. Does this not exchange one bit of processing for another?

Mu macro runs sequential processing of a number of rows of data. There is no search involved. While there may be 500 rows to process, they are copied individually to a second workbook into a sheet which has only one record or row of data. Five values calculated in a second sheet are copied back to the original.

At the end of the day, my question is not so much about how to make the macro more efficient (although that always helps) but more about why the exact same code on the exact same files should run so much slower on the later version of Excel running on a later version of Windows.

This is becoming academic at this point as my colleague is so pleased at having reduced the processing time from a couple of days to a couple of minutes.

I will say this though. Another colleague has a particularly troublesome workbook that derives data using VLOOKUP and other interesting functions. On his PC (later s/w and o/s versions) it takes up to 30 minutes to open and recalculate. On mine... a few seconds. It definitely seems to me as if some system overhead is getting in the way.

Anyway, thanks for your contributions, everyone. They are much appreciated.

Dave


2013-01-14 11:31:45

Aldo Santolla

What I find to help speed up searches through large data lists is to set a range variable for the list and use that variable to do the searching. It's like puting the entire list into memory.

For example...

Dim MyDataList as Range

Set MyDataList = WorkSheetName.Range("Where_My_Data_Is")

This may help. I used it on a data list with over 2200 rows and over 30 columns of data, and I don't even notice it working.


2013-01-14 08:55:07

Jeff C

It sounds like you don't want to get rid of the status bar, so perhaps you can update the status less frequently (at 10% intervals, for example).


2013-01-14 04:32:57

Dave K

Hi guys. Thanks for the tips. The structure of my code is as follows:

"Dim" statements for 2 integer counters and 3 worksheet variables;
turn screen updating off (already there, thanks Aldo);
assign source and target worksheets to variables;
count the number of rows to process using the following code:
iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ;

within the loop of the program...
output the number of the current row being processed to the status bar (this is very useful for watching the speed and progress) with the code
Application.StatusBar = "Processing row " & i
directly assign values, for example:
wsTarget.Range("E6").Value = wsSource.Range("C" & i).Value
(17 cell values are assigned);
followed by 5 similar assignments to return the results to the Source sheet;
exit program loop;
select one cell and display a "processing complete" message;
clear the status bar;
turn on screen updating;

That's it! No rocket science there.
I'll try running the process on other PC's and see what happens.

Thanks for your help,
Dave.


2013-01-13 12:29:49

Thomas Papavasiliou

Also try to avoid the "Select" command


2013-01-13 03:32:28

Aldo Santolla

One thing I do if the macro I create runs into some heavy work is to turn off Screen Updating in the macro...

Application.ScreenUpdating = False

This will suspending the worksheet screen from updating with data until you turn on screen updating again...

Application.ScreenUpdating = True


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.