Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Macro Slows Down on More Powerful Machine

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 97, 2000, 2002, and 2003.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

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

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.