XL5: Can't Define Name with Same Name as a Subroutine

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel version 5.0, you cannot define a new name that is the same as the name of a currently running Microsoft Visual Basic for Applications subroutine. If you attempt to do so, you will receive the following error message:

   Run-time error '1004':
   Add method of Names class failed

STATUS

This is a known problem in Microsoft Excel 5.0. This problem was corrected in Microsoft Excel for Windows 95, version 7.0 and later.

MORE INFORMATION

When you run a Visual Basic subroutine, Microsoft Excel creates a temporary name for the subroutine in memory. As long as this temporary name exists, it is not possible to define a new name that is the same as the temporary name. You can, however, redefine an existing name.

If you attempt to define a new name while an identically named subroutine is running, you will receive the error message shown above.

Visual Basic Code Example

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 engineers 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/default.asp

To run the subroutines, position the cursor in the Sub line for the subroutine you want to run and either press the F5 key or choose Start from the Run menu.

   'The Test1 subroutine will fail if the name "Test1" does not already
   'exist in the workbook.

   Sub Test1()
     
       ActiveWorkbook.Names.Add Name:="Test1", _
           RefersToR1C1:="=Sheet1!R1C1:R10C10"

   End Sub

   'The Test2 subroutine runs the MakeName subroutine, which attempts to
   'define the name "Test2". As with the above subroutine, it will fail if
   'the name "Test2" does not already exist in the workbook.

   Sub Test2()
       MakeName                              'runs the Test2 subroutine
   End Sub

   Sub MakeName()
     
       ActiveWorkbook.Names.Add Name:="Test2", _
           RefersToR1C1:="=Sheet1!R1C1:R10C10"

   End Sub