Article ID: 159957
Article Last Modified on 10/21/2000
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q159957
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:
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 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
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.
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
MsgBox Activeworkbook.Names("Sheet1!MyName").Name
Additional query words: XL97 level 8.0 8.00
Keywords: kbprogramming kbdtacode KB159957