Macro Slows Down on More Powerful Machine

by Allen Wyatt
(last updated August 2, 2016)

Paul Callander ran into an oddity with one of his workbooks. It contains a macro that populates two significant database sheets each time it is run. Under Excel 2000 (on a laptop with a P4 1.6 GHz CPU, 512 MB RAM, and Windows XP SP1) the macro takes about 14 minutes to run. Under Excel 2003 (on a desktop system with a P4 3.0 GHz CPU, 512 MB RAM, and Windows XP SP2) the macro takes about 33 minutes to run. Paul was wondering why the same macro runs slower on more powerful machines.

There are many reasons why this could be the case. For instance, it could be due to what else is running on the desktop machines. If the machine is doing many background tasks, then the amount of time available to Excel may be less than it is on the less-powerful laptop running without the same background tasks. The way to figure this out is to pull up the Task Manager on each system and compare what is running in the background.

Another potential slowdown occurs if the macro consistently tries to update the screen. If the laptop has a more powerful graphics card than the desktop unit, then the updating could occur quicker and the macro, therefore, run faster. The solution to this problem is to turn off screen updating at the beginning of the macro and turn it back on at the end using these two code lines, respectively:

Application.Screenupdating = False
Application.Screenupdating = True

It is also possible that the need to continually recalculate the worksheets is slowing down the macro. It is a good idea, if the worksheets include many calculations, to switch to manual recalculation at the beginning of the macro and back to automatic at the end. The following are the two code lines to perform each task, respectively:

Application.Calculation = xlManual
Application.Calculation = xlAutomatic

It is possible, as well, that the switch from Excel 2000 to Excel 2003 could be part of the problem. Recalculation of a worksheet is done based on dependency trees, which track what cells are dependent on what other cells. In Excel 2002 Microsoft changed how it did calculations, using more efficient algorithms to handle dependency trees. Thus, Excel 2002 and 2003 will recalculate worksheets faster than the same worksheets in Excel 97 and 2000. The glitch crops up when you open, in Excel 2003, a workbook created in Excel 2000. When first opened, Excel needs to regenerate the dependency trees from scratch using the new algorithms. For complex workbooks, this regeneration can take considerable time. If Paul's macro is loading an Excel 2000 workbook (old dependency tree algorithms) in Excel 2003 (new dependency tree algorithms), then Excel is trying to do the regeneration, as it should, thereby slowing down the processing of the workbook. The solution is to manually load the older workbook, allow Excel to complete its regeneration, and then use Save As to save the workbook. In subsequent uses of the macro, calculation should occur faster since the regeneration is already complete when the older workbooks are opened.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3072) applies to Microsoft Excel , 8, , 10, and .

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

Macro Fails after AutoFilter

When developing a macro that others may use, you might want to test it out to make sure it works properly if an AutoFilter is ...

Discover More

Choosing a Character Size in the Equation Editor

The Equation Editor is a handy tool, particularly for those who must include mathematical equations in their documents. ...

Discover More

Combining and Formatting Times

Excel allows you to store times in your worksheets. If you have your times stored in one column and an AM/PM indicator in ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

MORE EXCELTIPS (MENU)

Forcing a Macro to Run when a Worksheet is Recalculated

Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated ...

Discover More

Showing RGB Colors in a Cell

Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see the ...

Discover More

Determining if Calculation is Necessary

When processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn?t it be nice if ...

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 for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)