Microsoft KB Archive/67465

Excel: Macro Error at NEXT Command with Valid FOR PSS ID Number: Q67465 Article last modified on 02-08-1993 PSS database name: M_eXceL

3.00

MACINTOSH

Summary:

If there is a macro error when the macro reaches a NEXT function, and the FOR statement associated with that NEXT is valid, check the IF statements in the macro. All IF statements must close with an END.IF if the IF statements are contained within the FOR/NEXT loop.

More Information:

Microsoft Excel version 3.00 allows for the use of structured IF statements. Given the following macro:

A1: =FOR(“x”,1,10) A2: =IF(x=5) A3: =Alert(“X Now Equals 5”) A4: =GOTO(A8) A5: =ELSE A6: =ALERT(“X does not equal 5”) A7: =END.IF A8: =NEXT A9: =RETURN

There will be a macro error when x equals five because, when x equals five, Microsoft Excel is directed to go to cell A8. This causes Microsoft Excel to jump past the END.IF statement in cell A7. Because Microsoft Excel thinks it is still in the IF structure, it will be expecting the NEXT statement to be associated with a FOR statement within that IF structure. Because there isn’t a FOR statement within the IF structure, the macro halts. By removing the GOTO command from cell A4, the macro will perform properly.

Because it is rare for real-world macros to have as straightforward a solution, there is a rule of thumb for the macro programmer to use. Be sure that for every IF statement used, there is an associated END.IF.

Note: Form 1 IF statements, with the following structure

IF(logical,value_if_true,value_if_false)

do not require an END.IF.

For more information on structured IF statements, see pages 126-127 of the “Microsoft Excel Function Reference” version 3.00 manual.

Additional reference words: 3.0 3.00

Copyright Microsoft Corporation 1993.