Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
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.
Learn more about Allen...
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.
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!