Microsoft KB Archive/111281

= Can't Define Name with Same Name as a Subroutine =

Article ID: 111281

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



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



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

Additional query words: 5.00a 5.00c routine XL5

Keywords: kbbug kbfix kbprogramming kbcode KB111281

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.