Microsoft Office Tutorials and References
In Depth Information
The Macro Recorder
h The macro recorder always creates Sub procedures. You can’t create a Function
procedure by using the macro recorder.
h The code that is generated depends on certain settings that you specify.
h You’ll often want to clean up the recorded code to remove extraneous commands.
What the macro recorder actually records
The Excel macro recorder translates your mouse and keyboard actions into VBA code. I could
probably write several pages describing how this translation occurs, but the best way to show
you is by example. Follow these steps:
Start with a blank workbook.
Make sure that the Excel window isn’t maximized.
You don’t want it to fill the entire screen.
Press Alt+F11 to activate the VBE window.
Note: Make sure that this window isn’t maximized. Otherwise, you won’t be able to see
the VBE window and Excel’s window at the same time.
Resize and arrange Excel’s window and the VBE window so that both are visible. (For
best results, minimize any other applications that are running.)
Activate Excel, choose Developer ➜ Code ➜ Record Macro and then click OK to start the
Activate the VBE window.
In the Project Explorer window, double-click Module1 to display that module in the code
Close the Project Explorer window in the VBE to maximize the view of the code window.
Your screen layout should look something like the example in Figure 7-13. The size of the
windows depends on your video resolution. If you happen to have a dual display system, just put the
VBA window on one display and the Excel window on the other display.
Now move around in the worksheet and select various Excel commands. Watch while the code is
generated in the window that displays the VBA module. Select cells, enter data, format cells, use
the Ribbon commands, create a chart, manipulate graphic objects, and so on. I guarantee that
you’ll be enlightened while you watch the code being spit out before your very eyes.