Microsoft KB Archive/115941

= Microsoft Knowledge Base =

Excel: Explanation of Recursive Calculation and Evaluation
Last reviewed: March 27, 1997

Article ID: Q115941

2.x 3.x 4.x 5.00 5.00c 7.00 | 2.x 3.00 4.00 5.00 5.00a

WINDOWS                    | MACINTOSH

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0
 * Microsoft Excel for OS/2, version 2.2, 3.0
 * Microsoft Excel for the Macintosh, version 2.x, 3.0, 4.0

SUMMARY
In Microsoft Excel, the sequence of evaluation can cause unexpected results when a macro is called as part of a formula. If a single formula invokes a macro and subsequently references a defined name or cell modified by that macro, the defined name or cell will be evaluated differently than it would have been prior to the calculation of the formula.

MORE INFORMATION
Microsoft Excel evaluates formulas from left to right. For example, when Microsoft Excel calculates the following formula

=FORMULA(5,B1)+B1 the cell B1 is assigned the value of 5 prior to adding the cell B1. This formula will return a value of 6 (TRUE+5) regardless of the value in B1 prior to evaluation.

If you want to add the existing value in B1 prior to modifying B1, then you should reverse the sequence of the expressions, as in the following example:

=B1+FORMULA(5,B1) This behavior can cause unexpected results when a formula calls a macro that modifies a defined name or cell referenced in the formula after the macro is invoked. In the previous example, B1 was explicitly modified within the formula. When a macro is invoked as part of the formula, the defined names and cells modified by the macro are not as obvious.

The following function macro attempts to reverse the order of the characters in the text string passed to it. It performs this recursively by passing the string minus the first character back to itself until the length of the string is evaluated as one. Then, as each instance of the function terminates, the calling instance concatenates the initial first character onto the end of the string.

A1: Reverse A2: =ARGUMENT("MyText") A3: =IF(LEN(MyText)=1,RETURN(MyText)) A4: =RETURN(Reverse(MID(MyText,2,255))&LEFT(MyText,1)) In this example, the value passed from the first instance to the second instance would be the text "MyText" minus the first character. Each subsequent instance of the Reverse function will redefine the name MyText as the text of MyText less one additional character, until the final instance which then defines MyText as just one character.

When the last instance terminates and returns the single character, the calling instance will then concatenate the remaining single character with the left-most character of MyText, which is the same single character. The name MyText, when it was first evaluated in the MID function contained a two-character string. After invoking the last instance of "Reverse" and then returning to complete the evaluation of A4, the name MyText contains a one character string. The result is that in each instance, as it evaluates LEFT(MyText,1), MyText will contain the single character passed to the last instance of "Reverse," since that was the last time the name MyText was defined. The value of the name MyText has changed during each evaluation of A4.

The result of "Reverse" will always be a string with the same number of characters as the original argument, but each character is the last character of the original string. The result of =REVERSE("Sample") would be "eeeeee."

The following corrected version of "Reverse" illustrates the problem and how to avoid it. This example starts at the end of the string, instead of starting at the beginning of the string, and extracts the last character to concatenate to the remaining string prior to passing the remaining string to the next instance.

A1: Reverse A2: =ARGUMENT("MyText") A3: =IF(LEN(MyText)=1,RETURN(MyText)) A4: =RETURN(RIGHT(MyText,1)&reverse(LEFT(MyText,LEN(MyText)-1))) In this example, the defined name MyText will change during the evaluation of A4, but the function RIGHT(MyText,1) will be evaluated before the subsequent instances of "Reverse" redefine MyText. By invoking the next instance of "Reverse" after evaluating RIGHT, this version of MyText is correctly used.

For more information about recursive functions in Microsoft Excel, query on the following words in the Microsoft Knowledge Base:

routines and function and recursive and macro