In a recent post, I showed how to record a macro to save yourself time and effort with that repetitive task. The recorder has captured all of your keystrokes, intentional and otherwise. Objects are selected first, then acted upon. Defaults are applied indiscriminately every time the recorder followed you into a dialog. Actions you take and undo are all recorded. The result is syntactically accurate, but inefficient.

Let's clean up the code to make it run faster and smoother, without the flashing shown on screen. Go to the VB Editor and open the code module containing your recorded macro. The code recorded in the previous example is shown below.

That's a lot of code for the small number of actions that were done while recording. We centered the text in the selected cells, changed the font to Arial, changed the font size to 12 points, and changed the text to red. Every time an action was taken, properties which were not changed during that action were still recorded, despite being unchanged. For example, the font's ColorIndex was twice "changed" to xlAutomatic, before finally being changed to 3 (red in the default Excel color palette). Removing the redundant and default statements greatly reduces the code.

This macro didn't have the usual repeated cycle of select an object, format the selection, select another object, so I've included another sample below.

By avoiding the sequence of selections, the code is smaller and runs faster, and the screen doesn't change or even flash with each subsequent selection change.

A technique for streamlining code is to group operations performed on each object within With/End With blocs. The first recorded macro above was recorded with several With/End With blocks. But frequently when recording macros, we are carrying out operations as we think of them, and operations on the same object may be scattered around the macro. In this case, we can construct our own blocks, first rearranging the code, then forming the blocks.

Reordering...

Blocking...

Note that you can nest one With/End With block within another.

For a more detailed look at optimizing recorded macros and using Excel's object model more effectively, see my colleague Tushar Mehta's Beyond the macro recorder.

In a series of upcoming posts, I'll cover the following VB topics:

• Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
• VBE settings
• VBE features
• Debugging and error proofing