Dealing with Uncalculated Cells

During the recalculation of a worksheet, Microsoft Excel uses sophisticated heuristics to determine which cells are scheduled to be recalculated and in which order. This means that if you enter a function on a worksheet that calls a DLL and that function tries to look up the value of another cell, you cannot be sure whether the value has been recalculated yet. You need to be concerned about this only if all of the following conditions apply:

• You are writing a DLL function that will be entered on a worksheet.
• That function looks at the value of other cells on the worksheet.
• The DLL is not a macro type (the data type string does not contain "#"). For more information on this see Register (Form 1) "Handling Uncalculated Cells" in Chapter 2.

If your DLL functions are not meant to be entered on worksheets, or if they do not try to find out the values of cells elsewhere on the worksheet (for example, using xlCoerce), you do not need to worry about the possibility of uncalculated cells.

Here is an example scenario: A DLL function called GetB5 returns the value of cell B5. The function would look like this:

LPXLOPER WINAPI GetB5(void)
{
    static XLOPER xResult, xReference, xNum;

    xReference.xltype = xltypeSRef;
    xReference.val.sref.count = 1;
    xReference.val.sref.ref.rwFirst = 
        xReference.val.sref.ref.rwLast     = 4;
    xReference.val.sref.ref.colFirst = 
        xReference.val.sref.ref.colLast = 1;

    xNum.xltype = xltypeInt;
    xNum.val.w = xltypeNum;

    Excel4(xlCoerce, &xResult, 2, (LPXLOPER) &xReference,
        (LPXLOPER) &xNum);

    return &xResult;
}

This function can be registered with a type_text argument of "R!". The exclamation point means that the function is volatile and needs to be recalculated whenever the sheet changes. To work around this, set recalculation to manual. Next, enter the following formulas on a worksheet.

Microsoft Excel schedules the cells to be calculated in descending order. That is, first B1 will be calculated, then B2, then B3, and so on. What happens when we insert the GetB5 call on line B6?

Initially, the value is correct. But Microsoft Excel has no way of knowing that the function GetB5 depends on the value of Cell B5. So if you change B1 . . .

. . . and then recalculate by pressing F9, Microsoft Excel schedules B6 to be calculated first. Now when GetB5 tries to look at the value of B5, it finds that the cell has not been calculated yet. The xlCoerce function call returns xlretUncalced. However, Microsoft Excel remembers that it moved to that uncalculated cell and schedules B5 to be recalculated again later. Because the GetB5 function returns a wrong value, Microsoft Excel recalculates B2 through B5, and finally, calls GetB5 once again. So the final values are correct:

Finally, Microsoft Excel now knows that B6 is dependent on B5, and rightly schedules it to be calculated last in the future. GetB5 will not see xlretUncalced again.

Experienced XLM macro language users will recognize that this is not the behavior of the Microsoft Excel macro language. If GetB5 had been written in the XLM macro language, the first recalculation would simply have given the wrong value. Microsoft Excel would still reschedule B6, so the next recalculation would be correct. If, for some reason, you require this macro language behavior, you can specify # in the type_text argument to the REGISTER function.