Page 305 - Excel Workbook for Dummies

32_798452 ch22.qxp 3/13/06 7:48 PM Page 288
288 Part VII: Macros and Visual Basic for Applications
controls in this dialog box to run or edit a macro, or even to play the macro back
a step at a time. (The latter is especially helpful when a macro does something
unintended and you want to find out where exactly it went off track.)
12. Click the PERSONAL.XLS!Company_Name macro in Macro Name list box before
you select the Run button.
As soon as you select Run, Excel closes the Macro dialog box and runs the
Company_Name macro, which enters Mind Over Media, Inc. in cell A1 of Sheet2
in centered 12-point bold text in a merged cell that includes the cell range, A1:E1.
13. Select cell G1 in Sheet3 and then run the Company_Name macro using the short-
cut key combination, Ctrl+Shift+C, that you assigned to it.
Excel runs the macro, but this time something unanticipated happens: The pro-
gram enters the correctly formatted text in cell G1 while still creating the merged
cell in the cell range A1:E1 instead of G1:K1 (which would then include the com-
pany name).
Excel does this when you run the Company_Name macros in any cell other than
A1 of a worksheet because the macro recorder automatically records the cell
references in a macro as absolute rather than relative references (unlike when
copying formulas). To fix this macro, you will have to rerecord it with relative
references in Exercise 22-2.
14. Exit Excel and select the No button when the alert dialog box appears asking you
if you want to save your changes to Book1.
As soon as you select No in the first alert dialog box, Excel displays a second
alert dialog box, this one prompting you to save changes to the Personal Macro
Workbook, PERSONAL.XLS, that contains your Company_Name macro. Normally,
you would select the Yes button to save your macro for use in other workbooks
during other work sessions. However, as you need to rerecord the Company_
Name macro using relative references to make it run properly, here you will
select No and not bother to save it.
15. Select the No button in the alert dialog box asking if you want to save your
changes to the Personal Macro Workbook.

Recording macros with relative cell references

Now you understand why the Stop Recording toolbar contains a Relative Reference
button. You need to use this button before you start recording any sequence in a
macro that requires relative rather than absolute cell references. Try using this button
when rerecording the Company_Name macro in Exercise 22-2 to see what difference it
makes when playing the macro back in cells other than A1 in the worksheet.

Try It

Exercise 22-2: Recording a Macro Using Relative Cell References
Launch Excel and then use Sheet1 of the new Book1 workbook to practice rerecording
the Company_Name macro, this time using relative cell references:

1. Choose the Tools➪Macro➪Record New Macro menu command to open the
Record Macro dialog box.
2. Replace the temporary macro name, Macro1, in the Macro Name text box with
Company_Name (be sure to enter an underscore character between Company