ID: Q161677
The information in this article applies to:
In Microsoft Excel 97, unlike in earlier versions of Microsoft Excel, there is no menu command to record a macro that uses relative cell references. However, you can click Relative Reference on the Stop Recording toolbar.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/refguide/
NOTE: The ScreenTip for Relative Reference does not change to indicate whether relative recording is already available. The only indication that relative recording is started is the appearance of the Relative Reference button. When the button has a sunken 3-dimensional appearance, relative recording is started.
If the Stop Recording toolbar does not appear when you begin recording a macro, you must display it. To display the Stop Recording toolbar, point to Toolbars on the View menu, and then click Stop Recording. When you do this, Microsoft Excel inserts an extra line of code in your macro. Note that when you run the recorded macro, the Stop Recording toolbar is displayed. To avoid this, follow these steps:
1. On the Tools menu, point to Macro, and then click Macros.
2. In Macro Name list, click the name of the macro that you recorded
and click Edit.
This step starts the Visual Basic Editor.
following line of code:
Application.CommandBars("Stop Recording").Visible = True
NOTE: If you enable relative recording, Microsoft Excel does not retain the setting for the next session of Microsoft Excel.
By default, recorded macros use absolute cell referencing, which means that exact cell locations are recorded into the macro. The macro works only with the exact cells addresses you use when you record the macro.
When you record a macro with relative cell referencing, the actions recorded in the macro are relative to the starting cell location.
The following examples record the same action with absolute, and then relative cell referencing.
To record a macro with absolute cell referencing, follow these steps:
1. Create a new workbook in Microsoft Excel.
2. Select cell C1 on the worksheet.
3. On the Tools menu, point to Macro, and then click Record New Macro.
4. In the Macro Name box type "Absolute_Recording" (without the quotation
marks), and then click OK.
5. On the Stop Recording toolbar, check the appearance of the Relative
Reference button. If the Relative Reference button has a sunken
3-dimensional appearance, click it to disable relative recording.
6. Type "test" (without the quotation marks) and click Enter (green check
mark).
7. On the Edit menu, click Copy.
8. Select cell C3.
9. On the Edit menu, click Paste.
11. To view the contents of the macro you recorded, point to Macro on the
Tools menu, and then click Macro. In Macro Name list, click "Absolute
Recording" and click Edit.
This step starts the Visual Basic Editor.
The macro is similar to the following:
Sub Absolute_Recording()
'
' Absolute_Recording Macro
' Macro recorded 12/27/96 by <your username>
'
'
ActiveCell.FormulaR1C1 = "test"
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
End Sub
This macro enters the word "test" (without the quotation marks), in the
active cell. Then, the macro copies and pastes the entry you typed in
cell C3 regardless of the active cell location when you started the
macro.
Microsoft Excel" on the File menu.
To record a macro with relative cell referencing, follow these steps:
1. Create a new workbook in Microsoft Excel.
2. Select cell C1 on the worksheet.
3. On the Tools menu, point to Macro, and then click Record New Macro.
4. In the Macro Name box type "Relative_Recording" (without the quotation
marks) and click OK.
5. On the Stop Recording toolbar, check the appearance of the Relative
Reference button. If the Relative Reference button does not have a
sunken 3-dimensional appearance, click it to enable relative recording.
6. Type "test" (without the quotation marks) and click Enter (green check
mark).
7. On the Edit menu, click Copy.
8. Select cell C3.
9. On the Edit menu, click Paste.
11. To view the contents of the macro you recorded, point to Macro on
the Tools menu and click Macro. In Macro Name list, click "Relative
Recording" and click Edit.
This step starts the Visual Basic Editor.
The macro is similar to the following:
Sub Relative_Recording()
'
' Relative_Recording Macro
' Macro recorded 12/27/96 by <your username>
'
'
ActiveCell.FormulaR1C1 = "test"
Selection.Copy
ActiveCell.Offset(2,0).Range("A1").Select
ActiveSheet.Paste
End Sub
This macro enters the word "test" (without the quotation marks) in the
active cell. Then, the macro copies and pastes the entry you typed to a
cell location two rows down from the active cell.
Microsoft Excel" on the File menu.
For more information about recording macros, click Contents and Index on the Help menu, click the Index tab in Excel Help, type the following text
macros, recording
Additional query words: XL97
Keywords : kbprg kbui kbdta kbdtacode xlui KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: May 17, 1999