Article ID: 159983
Article Last Modified on 8/17/2005
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0 Standard Edition
- Microsoft Excel 98 for Macintosh
This article was previously published under Q159983
SYMPTOMS
When you record a Microsoft Visual Basic for Applications macro that adds a defined name to a Microsoft Excel workbook, the following line of code may be recorded twice in the module:
ActiveWorkbook.Names.Add Name:="DefinedName", RefersToR1C1:= _ "=SheetName!Range" ActiveWorkbook.Names.Add Name:="DefinedName", RefersToR1C1:= _ "=SheetName!Range"
Where "DefinedName" refers to the name you typed in, "SheetName" is the name of your worksheet, and "Range" is the range of cells that "DefinedName" refers to.
CAUSE
This line of code is recorded twice because it is defined once when you click Add in the Define Name dialog box, and again when you click OK. Clicking both buttons accomplishes the same thing; they both assign the defined name.
RESOLUTION
If you want a recorded macro to record only one line of code for adding a single defined name to your workbook, only click OK in the Define Name dialog box when recording the macro. It is not necessary to click Add.
NOTE: If you are adding multiple defined names to your workbook, then click Add after entering the information for each defined name except the last name you add; click OK for the last defined name.
REFERENCES
For more information about the Add method in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type Add, click Search, and then click to view "Add Method (Workbooks Collection)."
NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:
176476 OFF: Office Assistant Not Answering Visual Basic Questions
For more information about the Add Method of the Names Collection in Microsoft Excel 7.0, click the Office Assistant, type define name, click Search, and then click to view "Add Method (Names Collection)".
Additional query words: XL97 XL98 XL7 XL5 XL
Keywords: kbprb KB159983