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

ID: Q115209

The information in this article applies to:

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.

WORKAROUND

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 professionals 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/support/supportnet/overview/overview.asp

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.00a 5.00c 7.00a 97 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