XL: Link Broken Moving WorkSheet Linked to Function Procedure

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, if you move a worksheet that contains a link to a function procedure, and then move the module containing the function procedure to a new workbook, the worksheet cell containing the link to the function procedure may display the #NAME error value.

CAUSE

If you move a worksheet containing a link to a function procedure, and then move the module containing the function procedure to a new workbook, the link to the function procedure is lost. As long as the original worksheet remains open, and you do not recalculate the cell containing the link, the result of the function procedure is displayed in the cell that contains the link. However, if you recalculate the cell containing the link, the #NAME error value is displayed in the cell containing the link. For example, if the workbook and module you move are the only sheets contained in the workbook, the workbook closes when the last sheet is moved without saving any changes and the link is lost.

WORKAROUND

To move a worksheet containing a link to a function procedure and the module containing the function procedure to another workbook while maintaining the link, move both sheets at the same time as follows:

1. To select both the worksheet and the module sheet, click the worksheet tab, hold down the CTRL key, and click the module sheet tab.

2. From the Edit menu, choose Move or Copy Sheet. From the To Book box, select (new book) or the name of the workbook to which you want to move the sheets. Choose OK.

MORE INFORMATION

When you enter a user-defined function in a worksheet, you can enter a function contained in any open workbook. To enter a user-defined function that is contained in the current workbook, you can use just the function name, for example =Test(). However, to enter a user-defined function that is contained in another workbook, you must include the workbook name, for example =BOOK1.XLS!Test(). When you use the Function Wizard to insert a function, the available functions are listed with the correct reference.

When you move a worksheet that contains a link to a user-defined function that is contained in the same workbook, the link is broken if the reference to the function is using the function name only. Even if the module containing the function procedure is then moved to the workbook with the worksheet containing the link, the function cannot be referenced using just the function name. Once the module containing the function is moved, the function must be referenced with the module name and the function name, for example, =Module1.Test(). To insert the function with the correct reference automatically, do the following:

1. From the Insert menu, choose Function.

2. From the Function Category list, select User Defined.

3. From the Function Name list, select the desired function.

For more information about entering a user-defined function in a worksheet or an overview of creating a user-defined function, choose the Search button in Help and type the following:

    user-defined functions