VBA Programming Series...18

Recording Options

When recording your actions to create VBA code, you have several options. Recall that the Developer➪Code➪Record Macro command displays the Record Macro dialog box before recording begins, as shown in Figure.

The Record Macro dialog box, shown in Figure, lets you specify a few aspects of your macro. In the following sections, I describe these options.

Macro name

You can enter a name for the Sub procedure that you are recording. By default, Excel uses the names Macro1, Macro2, and so on for each macro you record. I usually just accept the default name. If the macro works correctly and I want to save it, I give it a more descriptive name later on. You, however, may prefer to name the macro upfront — the choice is yours.

Shortcut key

The Shortcut key option lets you execute the macro by pressing a shortcut key combination. For example, if you enter w (lowercase), you can execute the macro by pressing Ctrl+W. If you enter W (uppercase), the macro comes alive when you press Ctrl+Shift+W.

You can add or change a shortcut key at any time, so you don’t have to set this option when recording a macro.

Store Macro In

The Store Macro In option tells Excel where to store the macro that it is recording. By default, Excel puts the recorded macro in a module in the active workbook. If you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in your Personal Macro Workbook.

Your Personal Macro Workbook is a hidden workbook that opens automati-cally when Excel starts. This is a good place to store macros that you’ll use with multiple workbooks. The Personal Macro Workbook is named PERSONAL.XLSB. This file doesn’t exist until you specify it as the location for a recorded macro. If you’ve made any changes to this file, Excel prompts you to save it when you exit.

Description

If you’d like to add some descriptive comments to your macro, use the Description box. You can put anything you like here or nothing at all. As far as I’m concerned, the Description option is a waste of time because it’s just as easy to add comments to the recorded macro.

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 gener-ates 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 master VBA. Other than 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 the Page Layout➪Page Setup➪Orientation➪Landscape command.

3. 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()

With ActiveSheet.PageSetup

.PrintTitleRows = “”

.PrintTitleColumns = “”

End With

ActiveSheet.PageSetup.PrintArea = “”

With ActiveSheet.PageSetup

.LeftHeader = “”

.CenterHeader = “”

.RightHeader = “”

.LeftFooter = “”

.CenterFooter = “”

.RightFooter = “”

.LeftMargin = Application.

InchesToPoints(0.708661417322835)

.RightMargin = Application.

InchesToPoints(0.708661417322835)

.TopMargin = Application.

InchesToPoints(0.748031496062992)

.BottomMargin = Application.

InchesToPoints(0.748031496062992)

.HeaderMargin = Application.

InchesToPoints(0.31496062992126)

.FooterMargin = Application.

InchesToPoints(0.31496062992126)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 300

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlLandscape

.Draft = False

.PaperSize = xlPaperLetter

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = 100

.PrintErrors = xlPrintErrorsDisplayed

.OddAndEvenPagesHeaderFooter = False

.DifferentFirstPageHeaderFooter = False

.ScaleWithDocHeaderFooter = True

.AlignMarginsHeaderFooter = True

.EvenPage.LeftHeader.Text = “”

.EvenPage.CenterHeader.Text = “”

.EvenPage.RightHeader.Text = “”

.EvenPage.LeftFooter.Text = “”

.EvenPage.CenterFooter.Text = “”

.EvenPage.RightFooter.Text = “”

.FirstPage.LeftHeader.Text = “”

.FirstPage.CenterHeader.Text = “”

.FirstPage.RightHeader.Text = “”

.FirstPage.LeftFooter.Text = “”

.FirstPage.CenterFooter.Text = “”

.FirstPage.RightFooter.Text = “”

End With

End Sub

You may be surprised by the amount of code generated by this single com-mand. (I was the first time I tried something like this.) Although you changed only one print setting, Excel generated code that sets many other print-related properties.

This is a good example of macro-recording overkill. If you want a macro that just switches the page setup to landscape mode, you can simplify this macro considerably by deleting the extraneous code. This makes the macro faster and easier to read. Here’s how the macro looks after I deleted the irrelevant lines:

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 your code easier to read. This constant has a value of 2, so the following statement works exactly the same (but isn’t as easy to read):

ActiveSheet.PageSetup.Orientation = 2

Rather than record this macro, you can 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 real-ize that the PageSetup object has an Orientation property. Armed with this knowledge (and probably some experimentation), you can write the macro manually.

This article 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.