by Allen Wyatt
(last updated June 23, 2018)
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
One of the big challenges when processing your data using macros is to make sure that you select all the data (and just ...Discover More
When you use a macro to do file operations, it works (by default) within the current directory. If you want to know which ...Discover More
Need to get rid of extraneous spaces before or after the text in a string? VBA provides three different functions you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.