Microsoft KB Archive/213540

= XL2000: Run-time Error '1004' Accessing Locally Defined Name =

Article ID: 213540

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213540





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

Workbook.Names

where is a locally defined name, you receive the following run-time error:

Run-time error '1004':

Application-defined or object-defined error



CAUSE
This behavior 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, 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.

This behavior is by design of Microsoft Excel.

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

Keywords: kbdtacode kbprb kbprogramming KB213540

-

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

© Microsoft Corporation. All rights reserved.