Microsoft Knowledge Base

WINDOWS                     | MACINTOSH

• 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

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)

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))

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)))

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