Examining Macros
The best way to get an idea of how macros work is to examine an
existing macro. To do that, display the View tab. In the Macros group,
click the Macros button, and then click View Macros.
Tip
In the Macro dialog box, you can display the macros available
in other workbooks by clicking the Macros In box and selecting a
workbook by name or selecting All Open Workbooks to display every
macro in any open workbook. If you select either of those choices,
the macro names that are displayed include the name of the workbook
in which the macro is stored. Clicking This Workbook displays the
macros in the active workbook.
The Macro dialog box displays a list of macros in your workbook.
To view the code behind a macro, you click the macro’s name and then
click Edit to open the Visual Basic Editor.
Keyboard Shortcut
You can also open and close the Visual Basic Editor by
pressing Alt+F11.
Excel macros are recorded by using VBA. Consider, for example,
the code for a macro that selects the cell range C4:C9 and changes the
cells’ formatting to bold. The first line of the macro identifies the
cell range to be selected (in this case, cells C4:C9). After the macro
selects the cells, the next line of the macro changes the formatting
of the selected cells to bold, which has the same result as clicking a
cell and then clicking the Bold button in the Font group on the Home
tab.
To see how the macro works, you can open the Macro dialog box,
click the name of the macro you want to examine, and then click Step
Into. The Visual Basic Editor opens, with a highlight around the
instruction that will be executed next.
To execute an instruction, press F8. The highlight moves
to the next instruction, and your worksheet then changes to reflect
the action that resulted from executing the preceding
instruction.
You can run a macro without stopping after each instruction by
opening the Macro dialog box, clicking the macro to run, and then
clicking Run. You’ll usually run the macro this way; after all, the
point of using macros is to save time.
In this exercise, you’ll examine a macro in the Visual Basic
Editor, move through the first part of the macro one step at a time,
and then run the entire macro without stopping.
Set Up
Start
Excel, open the VolumeHighlights_start workbook, click the Enable
Content button on the Message Bar (if necessary), and save the
workbook as VolumeHighlights. Then follow the
steps.
-
On the View tab, in the
Macros
group, click the Macros arrow
and then, in the list that appears, click View Macros.
The Macro dialog box opens.
-
Click the HighlightSouthern macro, and then, to
display the macro code, click Edit.
The Visual Basic Editor opens, with the code for the
HighlightSouthern macro displayed in the Module1 (Code)
window.
-
In the Visual Basic Editor window, click the
Close button.
The Visual Basic Editor closes, and Excel displays the
VolumeHighlights workbook.
-
In the Macros list, click
View Macros.
The Macro dialog box opens.
-
Click the HighlightSouthern macro, and then click
Step Into.
The macro appears in the Visual Basic Editor, with the first
macro instruction highlighted.
-
Press the F8 key.
Excel highlights the next instruction.
-
Press F8 again.
The macro selects the Atlantic row in the table.
-
Press F8 twice.
The macro changes the Atlantic row’s text color to
red.
-
Click the Visual Basic Editor Close button.
A warning dialog box opens, indicating that closing the
Visual Basic Editor will stop the debugger.
-
Click OK.
The Visual Basic Editor closes.
-
In the Macros list, click
View Macros.
The Macro dialog box opens.
-
Click the HighlightSouthern macro.
-
Click Run.
The Macro dialog box closes, and Excel runs the entire
macro.
-
On the Quick Access Toolbar, click the Save button.
Excel saves your work.
Clean Up
Close the VolumeHighlights workbook.
