XL: Names.Add May Get Recorded Twice

ID: Q159983

The information in this article applies to:

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: 

   ARTICLE-ID: Q176476
   TITLE     : 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 

Keywords          : kbdta PgmOthr KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 2, 1999