BREAK Command May Not Interrupt a Macro Loop in Excel

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.

MORE INFORMATION

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

1. Open a worksheet and enter the following:

         A1:
         A2:     10
         A3:     100
   
   

2. Open a macro sheet and enter the following:

         A1:     =WHILE(ACTIVE.CELL()<>100)
         A2:     =IF(ACTIVE.CELL()<>"")
         A3:     =BREAK()
         A4:     =ELSE()
         A5:     =SELECT("r[1]c")
         A6:     =END.IF()
         A7:     =NEXT()
         A8:     =RETURN()
   
      Define the macro by selecting cell A1 and choosing Define Name from
      the Formula menu. Type "Test" (without the quotation marks) in the
      Name box, select Command and choose OK.
   
   

3. Select cell A1 on the worksheet as the active cell and run the macro by choosing Run from the Macro menu and selecting Test.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 22, 125-127