XL5: Function Procedure Not Updated Correctly on Worksheet

ID: Q121328

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you use a Visual Basic function procedure in a cell on a worksheet, and you then use the result of this function as the argument for the same function in another cell, the function that references the result of the function returns an incorrect value.

CAUSE

This problem only occurs if the following are true:

The function procedure uses one of the worksheet functions available in the Application object, such as MINVERSE.

The argument that you use in the function procedure is a cell range, rather than a numeric value.

For example, if you enter the following on a worksheet

A1: 2
A2: =Test(A1)
A3: =Test(A2)

where the function Test is defined as follows


   Function Test(R) as Variant
      Test=Application.Minverse(R)
   End Function

the inverse value of cell A1, 0.5, is correctly returned in cell A2, and the inverse value of cell A2, 2, is correctly returned in cell A3. However, if you then change the value in cell A1, cell A3 returns the value 0.

NOTE: Because this is a calculation problem, the correct value is returned in cell A3 when you recalculate the worksheet.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

WORKAROUND

To work around this problem, when you use a cell range in the function procedure that uses a worksheet function, return the Value property of the argument as in the following example:

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.


    Function Test(R) As Variant
       ' Function returns value of function argument R
       Test = Application.MInverse(R.Value)
    End Function

REFERENCES

For more information about Using Worksheet Functions In Visual Basic, choose the Search button in the Visual Basic Reference and type:

functions

Additional query words: zero


Keywords          : 
Version           : 5.00
Platform          : WINDOWS 
Issue type        :

Last Reviewed: July 29, 1999