Microsoft KB Archive/121328

{|
 * width="100%"|

XL5: Function Procedure Not Updated Correctly on Worksheet

 * }

-

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