Cleaning up recorded macros

Earlier in this section, you saw how recording your actions while you issued a single command (the File ^ Page Setup command) can produce an enormous amount of VBA code. In many cases, the recorded code includes extraneous commands that you can delete.

It's also important to understand that the macro recorder doesn't always generate the most efficient code. If you examine the generated code, you'll see that Excel generally records what is selected (that is, an object) and then uses the Selection object in subsequent statements. For example, here's what is recorded if you select a range of cells and then use the buttons on the Formatting toolbar to change the numeric formatting, and apply bold and italic:

Range("A1:C5").Select Selection.NumberFormat = "#,##0.00" Selection.Font.Bold = True Selection.Font.Italic = True

If you use the Formatting dialog box to record this macro, you'll find that Excel records quite a bit of extraneous code. Recording toolbar button clicks often produces more efficient code.

Remember: This is just one way to perform these actions. You can also use the more efficient With-End With construct, as follows:

Range("A1:C5").Select With Selection

.NumberFormat = "#,##0.00" .Font.Bold = True .Font.Italic = True End With

Or you can avoid the Select method altogether and write the code even more efficiently, like this:

With Range("A1:C5")

.NumberFormat = "#,##0.00" .Font.Bold = True .Font.Italic = True End With

If speed is essential in your application, you'll always want to examine any recorded VBA code closely to make sure that it's as efficient as possible.

You will, of course, need to understand VBA thoroughly before you start cleaning up your recorded macros. But for now, just be aware that recorded VBA code isn't always the best, most efficient code.

About the Code Examples

Throughout this book, I present many small snippets of VBA code to make a point or to provide an example. Often, this code might consist of just a single statement. In some cases, the example consists of only an expression, which isn't a valid instruction by itself.

For example, the following is an expression:


To test an expression, you must evaluate it. The MsgBox function is a handy tool for this:

MsgBox Range("A1").Value

To try out these examples, you need to put the statement within a procedure in a VBA module, like this:

Sub Test()

' statement goes here End Sub

Then put the cursor anywhere within the procedure and press F5 to execute it. Also, make sure that the code is being executed within the proper context. For example, if a statement refers to Sheet1, make sure that the active workbook actually has a sheet named Sheet1.

If the code is just a single statement, you can use the VBE Immediate window. The Immediate window is very useful for executing a statement "immediately" — without having to create a procedure. If the Immediate window is not displayed, press Ctrl+G in the VBE.

Just type the VBA statement and press Enter. To evaluate an expression in the Immediate window, precede the expression with a question mark (?). The question mark is a shortcut for Print. For example, you can type the following into the Immediate window:

The result of this expression is displayed in the next line of the Immediate window.

Was this article helpful?

0 0

Post a comment