XL2000: Calculation Problems When Custom Function Creates a Defined Name (248179)

SYMPTOMS

When you press ENTER after typing a function into a worksheet, the worksheet may not be calculated correctly, and the message "Calculate" may remain in the status bar, instead of disappearing when you press F9.

CAUSE

This problem occurs when the following conditions are true:

You use a custom function in a cell.

-and-
You enter a parameter for the custom function that passes a reference to another cell.

-and-
The cell that you reference contains a "volatile" function such as =TODAY() or =RAND().

-and-
In the custom function, you create a defined name using that reference.

RESOLUTION

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

NOTE: Although SR-1 prevents the symptoms described earlier, it still does not let you use a custom function with the attributes described in the "Cause" section. Although "Calculate" no longer remains in the status bar, a custom function with these attributes always returns a value of zero.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

MORE INFORMATION

Microsoft Excel does not support defining a name within a custom function. You should design your custom functions so that they only use the return value to change the value or text of the cell that called the function.

NOTE: If the custom function passes a reference to something other than a volatile function, "Calculate" may appear in the status bar, but disappears when you press F9.

Functions that are recalculated automatically when data in the worksheet changes are called volatile functions. The following functions are volatile:

   AREAS()
   INDEX()
   OFFSET()
   CELL()
   INDIRECT()
   ROWS()
   COLUMNS()
   NOW()
   TODAY()
   RAND()