XL97: Run-time Error '1004' Accessing Locally Defined Name

 Last reviewed: February 27, 1998
Article ID: Q159957
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

In a Visual Basic for Applications macro, when you attempt to access a locally defined name using the following syntax in Microsoft Excel 97: 

   Workbook.Names(<name>)
where <name> is a locally defined name, you receive the following run-time error: 

   Run-time error '1004':
   Application-defined or object-defined error

CAUSE

This problem occurs when you reference a locally defined name, and you fail to qualify the name with the name of the worksheet. For example, if you create a locally defined name called Sheet1!MyName in Microsoft Excel 97, the following line of code fails: 

   MsgBox Activeworkbook.Names("MyName").Name
This syntax is incorrect because the locally defined name is not qualified with the name of the worksheet.

NOTE: Microsoft Excel 5.0 and 7.0 allow this syntax, and they return the expected results if the sheet that contains the name is the active sheet when you run the macro.

RESOLUTION

To resolve this behavior, qualify the locally defined name with its worksheet name. The following examples illustrate the correct syntax to use when you reference a locally defined name: 

   MsgBox Activeworkbook.Worksheets("Sheet1").Names("MyName").Name

   -or-

   MsgBox Activeworkbook.Names("Sheet1!MyName").Name

STATUS

This behavior is by design in Microsoft Excel 97.

Additional query words: XL97 level 8.0 8.00
Keywords : kbcode kbprg xlvbainfo xl97vbmigrate
Version : WINDOWS:97
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 27, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.