Can't Define Name with Same Name as a Subroutine

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