Datasheet

Macro Basics
702
Macro Basics
You can create macros two ways:
✦ Use Excel’s macro recorder to record your actions as you undertake
them in a worksheet.
✦ Enter the instructions that you want followed in VBA code in the Visual
Basic Editor.
Either way, Excel creates a special
module sheet that holds the actions
and instructions in your macro. The macro instructions in a macro module
(whether recorded by Excel or written by you) are stored in the Visual Basic
for Applications programming language.
You can then study the VBA code that the macro recorder creates and edit
this code in the Visual Basic Editor, which you open by clicking the Visual
Basic command button on the Developer tab (when this optional tab is dis-
played on the Ribbon) or by pressing Alt+F11.
Recording macros
With Excel’s macro recorder, you can create many of the utility-type macros
that help you to perform the repetitive tasks necessary for creating and edit-
ing your worksheets and charts. When you turn on the macro recorder, the
macro recorder records all your actions in the active worksheet or chart
sheet as you make them. Note that the macro recorder doesn’t record the
keystrokes or mouse actions that you take to accomplish an action — only
the VBA code required to perform the action itself. This means that mistakes
that you make while taking an action that you rectify won’t be recorded as
part of the macro; for example, if you make a typing error and then edit it
while the macro recorder is on, only the corrected entry shows up in the
macro without the original mistakes and steps taken to remedy them.
The macros that you create with the macro recorder can be stored either as
part of the current workbook, in a new workbook, or in a special, globally
available Personal Macro Workbook named
PERSONAL.XLSB that’s stored in
a folder called
XLSTART on your hard disk. When you record a macro as part
of your Personal Macro Workbook, you can run that macro from any work-
book that you have open (this is because the PERSONAL.XLSB workbook is
secretly opened whenever you launch Excel, and although it remains hidden,
its macros are always available). When you record macros as part of the cur-
rent workbook or a new workbook, you can run those macros only when the
workbook in which they were recorded is open in Excel.
36_037385 bk08ch01.qxp 11/17/06 11:08 AM Page 702