XL5: Function Procedure Not Updated Correctly on Worksheet

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

  -and-

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

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

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

Note that 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