Chapter 5 Optimizing for Size and Speed

Visual Basic is an extremely flexible programming language: there are often several ways to accomplish the same task. When you first start to program, or when you write a macro that will run only once, you'll probably be satisfied with simply "getting the job done." When you write a macro that will be used many times — such as a macro that prepares a weekly report, or an Auto_Open macro that runs every time you open a workbook — or when you write a macro that will be used by other people, you'll probably want to optimize the macro so that it requires less time and memory to run. The techniques described in this chapter will help you write smaller, faster macros.

Contents

• Minimizing OLE References
• Using Collection Index Numbers
• Minimizing Object Activation and Selection
• Removing Unnecessary Recorded Expressions
• Minimizing the Use of Variant Variables
• Using Specific Object Types
• Using Constants
• Using Worksheet Functions
• Using Special-Purpose Visual Basic Methods
• Turning Off Screen Updating

For information about accessing external data, see Chapter 13, "Accessing External Data."