Microsoft Office Tutorials and References
In Depth Information
Working with Code Windows
Choose Page Layout➜Page Setup➜Orientation➜Landscape.
Select Developer➜Code➜Stop Recording (or click the blue square in the status bar).
Excel stops recording your actions.
To view the macro, activate the VBE (pressing Alt+F11 is the easiest way) and locate the project
in the Project Explorer window. Double-click the Modules node to expand it. Then double-click
the Module1 item to display the code window. (If the project already had a Module1 , the new
macro will be in Module2 .) The code generated by this single Excel command is shown in Figure
7-6. Remember that code lines preceded by an apostrophe are comments and are not executed.
You may be surprised by the amount of code generated by this single command. (I know I was
the first time I tried something like this.) Although you changed only one simple setting in the
Page Setup tab, Excel generates more than 50 lines of code that affects dozens of print settings.
This code listing brings up an important concept. The Excel macro recorder is not the most
efficient way to generate VBA code. More often than not, the code produced when you record a
macro is overkill. Consider the recorded macro that switches to landscape mode. Practically
every statement in that macro is extraneous. You can simplify this macro considerably by
deleting the extraneous code. Deleting extraneous code makes the macro easier to read, and the
macro also runs a bit faster because it doesn’t do things that are unnecessary. In fact, you can
simplify this recorded macro to the following:
.Orientation = xlLandscape
I deleted all the code except for the line that sets the Orientation property. Actually, you can
simplify this macro even more because the With-End With construct isn’t necessary when
you’re changing only one property:
ActiveSheet.PageSetup.Orientation = xlLandscape
In this example, the macro changes the Orientation property of the PageSetup object on
the active sheet. By the way, xlLandscape is a built-in constant that’s provided to make things
easier for you. The variable xlLandscape has a value of 2 , and xlPortrait has a value of 1 .
The following macro works the same as the preceding Macro1 :
ActiveSheet.PageSetup.Orientation = 2