Excel Macros For Dummies, 3rd Edition

Chapter 13

Ten Ways to Speed Up Your Macros

IN THIS CHAPTER

Disabling performance draining Excel features

Avoiding unnecessary macro actions

Optimizing your macro code for better performance

As your macros become increasingly robust and complex, you may find they lose performance. When discussing macros, the word performance is usually synonymous with speed. Speed is how quickly your VBA procedures perform their intended tasks.

You can take steps to improve the performance of your macros. In this chapter, you find ten ways you can help keep your Excel macros running at their optimum performance level.

Halt Calculations

Each time a cell that affects any formula in your spreadsheet is changed or manipulated, Excel recalculates the entire worksheet. In worksheets that have a large amount of formulas, this behavior can drastically slow down your macros.

If your workbook is formula intensive, you may not want Excel to trigger a recalculation every time your macro alters a cell value. You can use the Application.Calculation property to tell Excel to switch to manual calculation mode. When a workbook is in manual calculation mode, the workbook does not recalculate until you explicitly ...