MacXL: Custom Function May Not Calculate Expected Value (223314)

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

http://www.microsoft.com/partner/referral/

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 COMMAND+SHIFT+F9 to recalculate the values in all open workbooks.

MORE INFORMATION

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 OPTION+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 OPTION+F11 to return to Excel.
Type Test in cell A2, and then press ENTER.
Type the following formula in cell A10, and then press RETURN:
=FindTextUp(A9)
The formula returns a value of 7.
Type Another test in cell A5, and then press RETURN.

The formula still returns a value of 7, when a value of 4 is expected. In this example, the FindTextUp function explicitly 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)