Word will place a red dot in the left margin in front of that line and will stop execution when it reaches the line. This is done by placing the cursor on that line and hitting the F9 key (or using the Debug menu). Insert a breakpoint on an existing line of executable code. Include a Stop statement in the code, which causes Word to enter break mode. Sub another_code_that_runs_5_seconds()Īpplication.Hit Ctrl-Break and choose Debug from the resulting dialog box.
#Excel vba break code code
For the portions of the code over which you ( the developer) want to exert control, you can have it set to xlErrorHandler and for the other pieces you can let the user retain it by setting it to xlInterrupt later down the line.
'Do something to make your impatient user happyĪnother interesting thing to note is that you can have more than one Application.EnableCancelKey instructions in a piece of code.
Sub code_that_runs_5_seconds()Īpplication.EnableCancelKey = xlErrorHandler This error is then tapped by the error handler ( On Error GoTo MyErrorHandler) and error handing code, after checking for the exact error code ( error code 18 in this case), lets the code execution resume from where it left off. If the user tries to stop the code prematurely, the xlErrorHandler kicks in and let the application raise an error. Here is a code that is supposed to run for 5 seconds. The xlErrorHandler is the one that lets the developer instruct the application generate an error ( code 18) and then to tap into that error by using error handling. The xlInterrupt is the normal course of action where the user can terminate the code and is the value that the application resets to after the code has run its course. Essentially there can be three values for Application.EnableCancelKey : xlDisabled, xlErrorHandler and xlInterrupt.īy setting Application.EnableCancelKey = xlDisabled, we are essentially telling the application to stop responding to the ctrl + break command from the user. The statement instructs Excel to not show the “Code execution has been interrupted” message and provides a way for the developer to tap into the ctrl + break action by the user. So the trick to prevent VBA code execution by pressing ctrl + break is to insert this magic statement in the VBA code: Application.EnableCancelKey = xlErrorHandler (now I know that opening the workbook again is always an option but hey that wouldn’t be half the fun would it 8-) ) If the user stops the code execution in between, they are left with a pretty ugly spreadsheet. It is important that the user let it run for that duration without stopping code execution since there are a lot of intermediate sheets, rows and columns that the code generates and subsequently deletes before exiting. Take for example some VBA code that runs for a few seconds. And for those who believe in killing poor little VBA code(s) with a ctrl + break, I just got a neat little trick up my sleeve. (People for Ethical Treatment of Algorithms) and believe that any code, no matter how long it takes ( or in my case how badly written it is), must be allowed the dignity to complete. You see, I am a very strong supporter of P.E.T.A. (Including header)Īnytime I write VBA code that runs for more than a split second, one of my worries remains that someone will ctrl + break it. This article has been read times this year. This article has been read times this month.
0 kommentar(er)
