Cleaning up recorded macros

Earlier in this chapter, you see how recording your actions while you issue a single command (the Page

Layout Page Setup' . Orientation command) produces an enormous amount of VBA code. This is an example of how, in many cases, the recorded code includes extraneous commands that you can delete.

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:

Range("A1").Value

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, 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 Sheetl, make sure that the active workbook actually has a sheet named Sheetl.

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 in the Immediate window 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._

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 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 some buttons on the Home tab to change the numeric formatting and apply bold and italic:

Range("A1:C5").Select Selection.Style = "Comma" Selection.Font.Bold = True Selection.Font.Italic = True

The recorded VBA code works, but it's 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

.Style = "#,##0.00" .Font.Bold = True .Font.Italic = True E nd With

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

With Range("A1:C5")

.Style = "#,##0.00" .Font.Bold = True .Font.Italic = True E nd With

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

You, 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.

4 PREV

NEXT

0 0

Post a comment