XL: Names.Add May Get Recorded Twice

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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"

CAUSE

This line of code gets recorded twice because it is defined once when the Add button in the Define Name dialog box is clicked and again when the OK button is clicked. 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, click only the OK button in the Define Name dialog box when recording the macro. It is not necessary to click the Add button.

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.

STATUS

This behavior is by design of Microsoft Excel.

REFERENCES

For more information about the Add Method of the Names Collection, click the Office Assistant, type "define name", click Search, and then click to view "Add Method (Names Collection)".