Names.Add May Get Recorded Twice (159983)

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)".