Can't Define Name with Same Name as a Subroutine

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q111281

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

Back to the top

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.

Back to the top

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.

Back to the top

Visual Basic Code Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. The following subroutines illustrate the various types of situations that may cause this error to occur. Enter the following subroutines into a new module sheet in a new workbook.

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

Back to the top

APPLIES TO

Microsoft Excel 5.0c
Microsoft Excel 5.0a for Macintosh

Back to the top

kbbug kbcode kbfix kbprogramming KB111281

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top