Microsoft KB Archive/76357

{| = BREAK Command May Not Interrupt a Macro Loop in Excel =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q76357

SUMMARY
The BREAK command is designed to interrupt a FOR-NEXT, FOR.CELL-NEXT, or a WHILE-NEXT loop. However, BREAK does not interrupt a macro loop if it is placed within a block IF statement.

Excel 3.0
There are two forms of the IF function (Form 1 and Form 2). Form 2 is a block structure and can only be used on macro sheets. This form allows you to execute more than one statement based on the outcome of the IF statement's logical test.

If a BREAK statement is encountered within a loop, that loop is terminated and the macro proceeds to the statement following the NEXT statement. If the BREAK command is encountered within a Form 2 IF function, the BREAK command will be ignored and the macro will enter an infinite loop. The BREAK command executes properly if encountered in a Form 1 (single-line) IF statement.

Workaround
Use a Form 1 IF command in place of the Form 2 IF command.

Steps to Reproduce Behavior
  Open a worksheet and enter the following: A1: A2: 10 A3: 100   Open a macro sheet and enter the following: A1:    =WHILE(ACTIVE.CELL<>100) A2:    =IF(ACTIVE.CELL<>&quot;&quot;) A3:    =BREAK A4:    =ELSE A5:    =SELECT(&quot;r[1]c&quot;) A6:    =END.IF A7:    =NEXT A8:    =RETURN

Define the macro by selecting cell A1 and choosing Define Name from the Formula menu. Type &quot;Test&quot; (without the quotation marks) in the Name box, select Command and choose OK.  Select cell A1 on the worksheet as the active cell and run the macro by choosing Run from the Macro menu and selecting Test.

The macro enters an infinite loop and can only be halted by pressing the ESC key on the keyboard.