XL: Macro Error Using GET.DEF Function to Return Local Name

 Last reviewed: February 2, 1998
Article ID: Q115209
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, when you use the GET.DEF() function to return a local name from a workbook, you receive a macro error message similar to the following if you include the sheet name in the def_text argument (the information referenced by the name): 

   Macro error at cell:
   [Book1]Macro1!A1

CAUSE

The macro error occurs when you include the sheet name in the def_text argument, and you do not include the sheet name in the document_text argument (the sheet that contains def_text).

For example, the following function causes a macro error: 

   =GET.DEF("Sheet2!R1C1","Book2")
This error only occurs when the def_text argument is not on the first sheet in the workbook. For example, the following does not cause a macro error: 

   =GET.DEF("Sheet1!R1C1","Book2")
Note that this problem does not occur when you use the GET.DEF() function to return a global name.

WORKAROUNDS

To work around this problem, do not include the sheet name in the def_text argument, even though the reference of the defined name includes the sheet name. Instead, include the sheet name, with or without the workbook name, in the document_text argument, to specify the name of the sheet that contains the referenced information.

Use the following example if the defined name is contained in the same workbook as the function: 

   =GET.REF("R1C1","Sheet2")
Use the following example if the defined name is located in a workbook other than the workbook that contains the function: 

   =GET.DEF("R1C1","[Book2]Sheet2")
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/supportnet/refguide/

MORE INFORMATION

You can use the GET.DEF() function to return the name, as text, that is defined for a particular area, value, or formula in a workbook.

If there is more than one name defined for the def_text argument, the first name is returned. A global name that references def_text is returned before a local name that references def_text.

If you use the GET.DEF() function, and you do not use the document_text argument, the document_text argument is assumed to be the active macro sheet. However, if you specify the workbook name without a sheet name for the document_text argument, the def_text argument is assumed to be located on the first worksheet in the workbook.

REFERENCES

For more information about the GET.DEF function, choose the Search button in Macro Functions Help and type: 

   GET.DEF

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98 XL97 XL7 XL5
err msg
Keywords : kbcode kberrmsg kbprg
Version : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb
Solution Type : kbworkaround

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 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.