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.