Is This Thing Efficient

You might think that recording a macro would generate some award-winning VBA code — better than you could ever write manually. Think again. In many cases, the recorder spits out lots of extraneous garbage, and it often generates code that's less than efficient.

Don't get me wrong. I'm a staunch supporter of the macro recorder. It's a great tool for helping you learn VBA. Except for simple macros, however, I've never used a recorded macro without fixing it up a bit (usually quite a bit).

To demonstrate just how inefficient the macro recorder's code can be, try this:

1. Turn on the macro recorder.

2. Choose FileOPage Setup.

The Page Setup dialog box appears.

3. Make sure the Page tab is selected.

4. Change the page orientation to Landscape and click OK.

5. Turn off the macro recorder.

To take a look at the macro, activate the Module1 sheet. This single — and very simple — command generates the following code:

Sub Macro1()

' Macrol Macro

' Macro recorded by John Walkenbach

With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With

ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(l) .BottomMargin = Application.InchesToPoints(l)

.HeaderMargin = Application.InchesToPoints(0.

.5)

.FooterMargin = Application.InchesToPoints(0.

.5)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlLandscape

.Draft = False

.PaperSize = xlPaperLetter

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = 100

.PrintErrors = xlPrintErrorsDisplayed

End With

End Sub

You may be surprised by the amount of code generated by this single command. (I was, the first time I tried something like this.) Although you changed only one setting in the Page Setup dialog box, Excel generated code that reproduces all the settings in the dialog box.

This is a good example of macro-recording overkill. If you want a macro that simply switches the page setup to landscape mode, simplify this macro considerably by deleting the extraneous code. This makes the macro faster and easier to read. You can simplify this macro as follows:

Sub

Macro1()

With ActiveSheet.

. PageSetup

.Orientation

= xlLandscape

End With

End

Sub

I deleted all the code except the line that sets the Orientation property. Actually, you can simplify this macro even more because you don't really need the With.End With construct:

Sub Macro1()

ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

In this case, the macro changes the Orientation property of the PageSetup object on the active sheet. All other properties are unchanged. By the way, xlLandscape is a built-in constant that makes things easier. I discuss built-in constants in Chapter 7.

Rather than record this macro, you could enter it directly into a VBA module To do so, you have to know which objects, properties, and methods to use. Although the recorded macro isn't all that great, by recording it you realize that the PageSetup object has an Orientation property. This example shows how the macro recorder can help you learn VBA.

This chapter nearly sums it up when it comes to using the macro recorder. The only thing missing is experience. Eventually, you discover which recorded statements you can safely delete. Better yet, you discover how to modify a recorded macro to make it more useful.

0 0

Post a comment