Two Quicks Tips for Faster Macros

June 24, 2009

For someone who already knows these two lines of code, they are simple and obvious. Yet they are also vital to working with any large data set.┬áThe first time I discovered them, my macro’s runtime dropped from six minutes to three seconds. I now include them in almost any long-running macro I write, with a few exceptions we’ll get to in a minute.

This first line of code controls display changes while a procedure is running. At the beginning and end of your macro, insert these lines of code:

Application.ScreenUpdating = False

… your code …

Application.ScreenUpdating = True

This code is especially important for any macro that is generating a large amount of data, like a report generator.

Note that while any change in cell values will not update until the macro is finished, changes to the StatusBar will be displayed. This can be useful if your macro’s runtime is several minutes. Updating the StatusBar with the progress of the macro lets the user know that the program has not frozen.

The second line of code prevents Excel from constantly re-calculating the formulas in your workbook while your macro is running:

Application.Calculation = xlCalculationManual

… your code …

Application.Calculation = xlCalculationAutomatic

This is especially useful in workbooks pulling lots of Bloomberg data, but it will increase the speed of many complicated macros.

You have to be careful when you use this because sometimes it can back fire. For instance, let’s say Cell B2 is your pre-tax income, which will be updated in your macro, and B3 is your post-tax income, calculated as a formula. If you change B2, B3 will not change until after the macro has finished running. You can remedy this by temporarily turning Calculation back on.

While not perfect for every situation, these two pieces of code can cut the runtime of your macro enormously, and turn a seemingly impossible project into a snap.