Microsoft KB Archive/115209

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

Article ID: 115209

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q115209



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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.



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.

