Macros Run Slower in Newer Excel?

by Allen Wyatt
(last updated June 6, 2016)

9

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.)

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

Understanding Styles

Styles are a key concept in Microsoft Word. If you understand styles, you will find it much easier to use Word effectively.

Discover More

Sorting Single-Column Addresses

Got a document that contains a bunch of addresses? If you want to sort the addresses, then you are in for a surprise because ...

Discover More

Changing Spacing Between Table Cells

Need to adjust the space between individual cells in a table? Word gives you a good deal of control over this spacing, as ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Counting Commas in a Selection

If you have a range of cells in which you want to count all the commas, there are several ways you can derive the figure you ...

Discover More

Determining the Length of a String

Macros are great for working with strings, and one of the most commonly used string functions is Len. This tip explains how ...

Discover More

Setting Program Window Size in a Macro

The macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel appears ...

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}] 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 4?

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.