XL2000: Calculation Problems When Custom Function Creates a Defined Name

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.

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.

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

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.

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: