XL97: Cannot Stop Macro After Switching to Other Program (163497)

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To work around this problem, wait for the macro to finish running. After the macro is finished, Microsoft Excel 97 updates the screen. If you wait for a long time and the macro does not finish running, it may be necessary to end the instance of Microsoft Excel 97 by pressing CTRL+ALT+DEL.

Although it is not possible to prevent this problem from ever occurring, you can lessen its affects by adding MsgBox commands to the Visual Basic macro. For example, assume you are running the following Visual Basic macro:

   Sub Test()
       For X = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 1).Value = X
       Next X
       For Y = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 2).Value = Y
       Next Y
       For Z = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 3).Value = Z
       Next Z
   End Sub
				

When you run this macro, switch to another program, and then switch back to Microsoft Excel 97, you may not be able to halt the macro by pressing ESC. Also, the screen may not be updated properly until the macro is finished. However, you can add message boxes that allow the screen to be updated. For example, type the following subroutine:

   Sub Test()
       For X = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 1).Value = X
       Next X
       MsgBox "Stage one complete!"     'new line of code
       For Y = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 2).Value = Y
       Next Y
       MsgBox "Stage two complete!"     'new line of code
       For Z = 1 To 4000
           ThisWorkbook.Worksheets(1).Cells(1, 3).Value = Z
       Next Z
       MsgBox "Stage three complete!"   'new line of code
   End Sub
				

When each loop is completed, the macro displays a message box. When you click OK, the screen is updated properly, and you can halt the macro by pressing ESC.

Application.EnableCancelKey = xlInterrupt
				

Application.EnableCancelKey = xlErrorHandler