Microsoft KB Archive/223312

= XL97: Custom Function May Not Calculate Expected Value =

Article ID: 223312

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q223312





SYMPTOMS
When you calculate your worksheet, some cells may appear to have calculated the wrong value.



CAUSE
This problem occurs when the following conditions are true:
 * A cell on the worksheet contains a custom function.

-and-


 * The custom function contains one or more arguments that refer to a range of cells on the worksheet.

-and-


 * The result of the custom function depends on more cells than it directly references.

This behavior is by design of Microsoft Excel. When Excel calculates the cell that contains the custom function, it recalculates all cell ranges that are passed as arguments to your custom function. If the result of your function depends on cells that are not explicity referred to by the function, then the function may not be recalculated when those cells are recalculated.



WORKAROUND
Use one of the following methods to work around the problem.

Method 1: Modify Your Function So That All Relevant Cell Ranges Are Passed
Modify your function to accept as arguments all of the cells necessary to calculate the result of the function.

Method 2: Make Your Custom Function Volatile
Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

If you make your custom function volatile, this problem will not occur. To make your custom function volatile, add the following line of code to the function:

Application.Volatile NOTE: If you make your custom function volatile, it will recalculate every time you make a change to a value or recalculate an open workbook. This could impact the performance of your worksheet model.

Method 3: Force Excel to Recalculate All Open Workbooks
Press CTRL+ALT+F9 to recalculate the values in all open workbooks.



Example of the Problem
To illustrate this problem, follow these steps:  Close and save any open workbooks, and then open a new workbook. Start the Visual Basic Editor (press ALT+F11). On the Insert menu, click Module.  Type the following code into the module sheet:

' This function counts the number of blank cells by starting from the cell ' referred to by the rngStartCell argument and moving up the column.

Function FindTextUp(rngStartCell As Range) As Single Dim iIndex As Integer For iIndex = 0 To 100 If rngStartCell.Value <> "" Then FindTextUp = iIndex Exit Function Else Set rngStartCell = rngStartCell.Offset(-1, 0) End If   Next iIndex End Function  Press ALT+F11 to return to Excel.</li> Type Test in cell A2, and then press ENTER.</li> Type the following formula in cell A10, and then press ENTER:

=FindTextUp(A9)

The formula returns a value of 7.</li> Type Another test in cell A5, and then press ENTER.</li></ol>

The formula still returns a value of 7, when a value of 4 is expected. In this example, the FindTextUp function explicity refers to cell A9. However, the function may depend on cells A1:A8, depending on the data entered in the worksheet.

If you implement method 1 as described in the "Workaround" section of this article, the function will calculate the expected result. The following line of code illustrates how to modify the function in this example so that the expected result is calculated:

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single Now, if you replace the function call in step 7 with the following function call, the function will always return the expected result.

=FindTextUp(A9,A1:A8)

Additional query words: XL97 udf user defined function recalc

Keywords: kbdtacode kbprb KB223312

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.