Deactivate Activate Dont Select Select

The use of the Activate and Select methods are the two most common blunders I see. Activate is used to activate a workbook, worksheet, or range. Select is used to select a range. Once a range is selected, it can be manipulated using the Selection property of the Application object. Manipulating Excel objects using Activate, Select, and Selection is slow, error-prone, and usually results in code that is difficult, at best, to maintain.

The biggest offender of this guidance is Excel's macro recorder. This feature generates a lot of inefficient, error-prone, and unmaintainable code. To be fair, the macro recorder also creates a lot of value, because for many people, it's the only way to automate otherwise tedious, manual tasks.

Because many people learn by studying the output of the macro recorder, using Select and Selection are the most common bad habits exhibited by most beginning Excel developers.

If you've read this book sequentially up to this point, you know that you don't need to activate or select worksheets and ranges to manipulate them programmatically. Instead, you can create a variable of the appropriate object type (workbook, worksheet, or range), point the variable to the desired object, and then manipulate the object through the variable.

Listing 13.1 provides an example of some code recorded by the Macro recorder and an equivalent procedure that avoids using Select. These two procedures just go to various worksheets in a workbook, select a column, and make the font of the column bold. To demonstrate a quantifiable difference, I've also included a couple of procedures to time each procedure, both with and without screen updating.

0 0

Post a comment