Macro On Workbook Is Not Defined Error While Updating Links (117231)
The information in this article applies to:
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0c
- Microsoft Excel for the Macintosh 5.0
This article was previously published under Q117231 SYMPTOMS
When you open a Microsoft Excel workbook that contains an external link,
and then re-establish links in the workbook, you may receive the following
error message:
<Macro name> on <Workbook name> is not defined or is too complex
where <Macro name> is the name of a macro in an external workbook called
<Workbook name>.
CAUSECase 1: The Object Is Assigned to a Macro
This problem occurs when you open a workbook that contains an object that
is linked to a macro in another workbook, and you re-establish the links in
the workbook.
When you assign a macro to an object, and the macro is in a different
workbook, a link to the workbook that contains the macro appears in the
Links dialog box. If you update this link by clicking Update Now in the
Links dialog box, and the workbook that contains the macro is closed, the
error message appears because Microsoft Excel cannot update the link.
This behavior is by design of Microsoft Excel.
Note that this problem also occurs if you re-establish links in a workbook
that contains an object that is linked to a macro in another workbook, and
the workbook you open also contains a reference to a named range in the
workbook.
When you open this type of workbook, Microsoft Excel prompts you to re-
establish links in the workbook.
NOTE: When you open a workbook that contains an object that is linked to a
macro in another workbook, and the workbook you open does not contain any
other external links, Microsoft Excel should not prompt you to re-establish
the links when you open the workbook. Microsoft Excel prompts you to update
links if the "Ask to Update Automatic Links" check box is selected on the
Edit tab in the Options dialog box.
The message to re-establish links does not appear when either of the
following conditions are true:
- You open a workbook that contains a reference to a named range in the
same workbook, and the workbook you open does not contain an external
link or object that is linked to a macro in another workbook.
-or-
- You open a workbook that contains an object that is linked to a macro in
another workbook, and the workbook does not contain an external link or
reference to a named range in the same workbook.
Case 2: Calling a Custom Function in an Add-In
This problem occurs when you open a workbook that contains a formula that
calls a custom function in an add-in, and then re-establish the links in
the workbook. This problem occurs when all of the following conditions are
true:
- When you open the workbook, the add-in is not open.
-and-
- You move the add-in after you create the formula that calls the custom function in the add-in.
-and-
- When you are prompted to re-establish links, you click Yes.
-and-
- When Microsoft Excel cannot find the add-in, you locate it (in the new
location) in the File Not Found dialog box, and then click OK.
WORKAROUNDMethod 1: The Object Is Assigned to a Macro
To work around the problem when the object is assigned to a macro, do any
of the following:
- On the Tools menu, click Options, click the Edit tab, and clear the "Ask
to Update Automatic Links" check box.
Note that this setting affects all workbooks.
-or-
- Before you open the workbook that contains the object, open the workbook
that contains the macro that is assigned to the object.
-or-
- When you are prompted to re-establish the links in the workbook that
contains the object that is assigned to a macro in another
workbook, click No.
-or-
- Use a cell reference instead of using a named reference in the workbook
that contains the object that is assigned to a macro in another
workbook.
Note that this workaround applies only to references to cells within
the workbook. It does not apply to external references.
Method 2: Calling a Custom Function in an Add-In
To work around this problem when you open a workbook that calls a custom function in an add-in, do the following:
- Quit Microsoft Excel.
- Start Microsoft Excel, and then open the workbook that contains the
link.
- When you are prompted to re-establish links, click No.
NOTE: Any formulas that call custom functions in a moved add-in display
the full path to the previous location of the add-in.
- On the Tools menu, click Add-Ins.
- In the Add-Ins dialog box, click Browse. In the Browse dialog box,
locate and select the add-in that you moved.
- Click OK.
- In the Add-Ins dialog box, make sure the add-in is selected and click
OK.
Formulas no longer display the incorrect path to the moved add-in;
because the add-in is in random access memory (RAM), the formulas
contain only the call to the custom function.
- Save the workbook.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
REFERENCESMicrosoft Excel 97
For more information about assigning a macro to a graphic object or to a
button on a sheet, click the Index tab in Microsoft Excel Help, type the
following text
and then double-click the selected text to go to the "Run a macro from a
button or graphic control" topic.
Microsoft Excel 5.0
For more information about assigning a macro to a graphic object or to a
button on a sheet, click the Search button in Help and type:
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbbug kberrmsg kbProgramming KB117231 |
---|
|