XL: Macro On Workbook Is Not Defined Error While Updating Links

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for the Macintosh, version 5.0

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

CAUSE

Case 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.

WORKAROUNDS

Method 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:

1. Quit Microsoft Excel.

2. Start Microsoft Excel, and then open the workbook that contains the link.

3. 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.

4. On the Tools menu, click Add-Ins.

5. In the Add-Ins dialog box, click Browse. In the Browse dialog box, locate and select the add-in that you moved.

6. Click OK.

7. 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.

8. 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.

REFERENCES

Microsoft 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

   macros, running

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:

   assigning macros