BREAK Command May Not Interrupt a Macro Loop in Excel

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.

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.

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

REFERENCES

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


KBCategory: kbother
KBSubcategory:

Additional reference words: noupd


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.