180

From what I can see on the web, this is a fairly common complaint, but answers seem to be rarer. The problem is this:

We have a number of Excel VBA apps which work perfectly on a number of users' machines. However on one machine they stop on certain lines of code. It is always the same lines, but those lines seem to have nothing in common with one another.

If you press F5 (run) after the halt, the app continues, so it's almost like a break point has been added. We've tried selecting 'remove all breaks' from the menu and even adding a break and removing it again.

We've had this issue with single apps before and we've 'bodged' it by cutting code out of modules, compiling and then pasting it back in etc.

The problem now seems to relate to Excel itself rather than a single .xls, so we're a little unsure how to manage this.

jordanz
  • 367
  • 4
  • 12
Phil Whittington
  • 2,144
  • 2
  • 16
  • 20
  • 2
    Is user pressing escape in the middle of any processing? Or any other app that is sending escape key out? – shahkalpesh Jan 28 '10 at 13:17
  • 1
    After selecting *Remove all Breakpoints* did you also recompile and save the VBA project? I have seen a related problem within Word VBA where previously removed breakpoints are still causing a break. – Dirk Vollmar Jan 28 '10 at 13:19
  • Nothing is being processed during the execution, shahkalpesh. Divo - thanks, I may not have tried them precisely in that order. Will do so now... – Phil Whittington Jan 28 '10 at 13:21
  • In addition to divo's comment, it can be useful to comment out the relevant lines and any lines that would stop the debug, they can then be uncommented and the code recompiled (again). – Fionnuala Jan 28 '10 at 13:37
  • And I forgot to mention: In order to actually trigger a rebuild, it is normally necessary to modify a line, and then move the cursor to the next line to flag the source code as modified (e.g. make a change, move cursor down, move cursor up and undo the change manually) – Dirk Vollmar Jan 28 '10 at 13:54

13 Answers13

479

I have found a 2nd solution.

  1. Press "Debug" button in the popup.
  2. Press Ctrl+Pause|Break twice.
  3. Hit the play button to continue.
  4. Save the file after completion.
Halo
  • 1,730
  • 1
  • 8
  • 31
Stan
  • 5,106
  • 1
  • 17
  • 7
  • 18
    +1: Wow, this post saved me from a world of pain. A very unexpected solution for a bizarre problem. After pressing [Ctrl+Break] twice and continuing, I saved the file. After that, the problem no longer appeared when I closed and re-opened the file. – dbenham Oct 01 '12 at 16:59
  • Thank you so much, I was going nuts trying to run macros and getting that error on random lines. – David__ Nov 21 '12 at 03:27
  • 1
    This has caused me no end of problems in the past, I've used radical code designs thinking it was how I'd written the code. Just had it come up again, tried the Ctrl-Break(or PAUSE on some keyboards) x2 and hey-presto, back to normal. THANK YOU!!! – aSystemOverload Jan 10 '13 at 12:16
  • 1
    Skills! Worked here on 2010 - Good old Microsoft ay! – Ralpharoo Feb 19 '14 at 07:21
  • 2
    Wow I have had this problem popping up every now and then forever. I tried pressing "debug" then ctrl + break (twice) and it didnt work. then I pressed "debug" ctrl + break (x 3), then press f8 to step to next line then f5 and it kept on running to the end. So something to this. the answer above by Stan is the most valuable hidden secret answer i have ever found on here. thanks stan. – IcemanBerlin May 20 '14 at 09:22
  • 2
    Thank you, that is an excellent answer: I have never seen an effective fix for 'phantom breakpoints' anywhere else, ever, not since I first encountered it in 1998. Microsoft have never acknowledged the problem and it is clear that they will never fix it. – Nigel Heffernan Jul 10 '15 at 11:04
  • 32
    Does anyone know why this works? Or where it comes from? Is this magic? – ZX9 Aug 13 '15 at 18:00
  • 2
    There should be some kind of top excel bugs description + work around, describing similar issues. Something like important TODOs before receiving heart attack! You just saved my day man! – Vityata May 03 '16 at 13:23
  • I don't know why it works, but I do know that my code was auto-breaking at *exactly* the same points where previously it would have refused to break when stepping through code with F8. Specifically, at any event where a workbook was opened or saved. It would just fly off and do the rest of the code and I'd have to set a hard break point with F9 on the next line, before I dared to F8 over that line. I wonder if there's some kind of cycling with Ctrl+Pause|Break that goes on? – baldmosher May 05 '16 at 17:21
  • 4
    If this helps anyone, what triggered the bug was my using the (otherwise never touched) Pause|Break key in Command Prompt - apparently it's cross system. Also, to add to the comments above, you're a SO superhero! – Josh Friedlander Jun 13 '17 at 07:29
  • 2
    This is some black magic voodoo. Thank you for sharing your freakish wisdom. 30 minutes from demo to upper management, this has saved me from imminent heart failure. – Ryan B. Jan 30 '18 at 18:31
  • 1
    What are all that people at Microsoft doing there? For what are they getting paid if bugs in their products are staying unfixed for 3+years? – Anatoly Alekseev Nov 30 '18 at 20:20
  • Microsoft employee if you are looking at this please consider prioritising your backlog - look at the vote count. And that's just the people who found this page! – Colm Bhandal Jan 23 '19 at 13:40
  • 1
    This BUG in Excel is over 20 years old. Luckily this ridiculous fix works. – Hannover Fist May 10 '19 at 23:18
  • @ZX9 I can't say why it works exactly, but from my experience it's not adding breaks randomly; It seems to add these hidden breaks when you save the file with breakpoints. It's almost like the breakpoints are being saved with the file, but not displayed when you open the file again (because they shouldn't be remembered). – Profex Oct 07 '19 at 12:47
  • I was having this issue with a userform that was opened as 'vbModeless' and had lost then regained focus during execution. Every time I would send focus to another Windows app unrelated to my code, then clicked back onto the userform I would experience this (could be anywhere in code execution). Although I was completely skeptical that this would work, I went ahead and performed this trick, lost then regained focus and to my surprise it actually worked! Too bad this user hadn't logged in since 2012 else I'd open up a bounty. – K.Dᴀᴠɪs Aug 10 '20 at 19:50
  • 1
    Thank you: I found the answer elsewhere, in 2015, but I'm commenting here and upvoting it to ensure that this answer remains prominent. Also: there is another point to note: **'ghost break points' are a useful example of Microsoft's refusal to acknowledge a known bug**, and of their refusal to engage with your questions if there's any risk of admitting that there's an error or omission that they're never going to fix. The internal politics behind that are toxic and inscrutable: but whatever is actually going on, it goes on **a lot** and experienced developers need to recognise the pattern – Nigel Heffernan Jun 02 '21 at 11:39
  • I'm writing VBA macros since 1997, and always struck this behaviour from time to time along these years. Today It happened because I put some Application.OnKey statement in my code; I started thinking this is more a VBA feature than an issue... – Max1234-ITA Mar 08 '22 at 13:44
  • I can confirm that this issue still occurs in Excel 2016, and thankfully the cure mentioned here still works! I know in my case, it has to do with hitting ctrl-Break more than once in a debug session. Hitting it twice in a row must reset some internal condition that causes this behavior. Adding my thanks and upvotes to the list. – Tom the Toolman May 01 '22 at 20:02
  • Nice one. Debugging some legacy VBA reports and this cropped up! – TilleyTech Dec 08 '22 at 17:15
  • crazy that a question from 13 1/12 years ago helped me. With mine, it wasn't saving breakpoints. but what I had been doing was ctr+break to break me out of a misbehaving loop. This must have reset that. – Chasester Jul 09 '23 at 18:37
17

This problem comes from a strange quirk within Office/Windows.

After developing the same piece of VBA code and running it hundreds of times (literally) over the last couple days I ran into this problem just now. The only thing that has been different is that just prior to experiencing this perplexing problem I accidentally ended the execution of the VBA code with an unorthodox method.

I cleaned out all temp files, rebooted, etc... When I ran the code again after all of this I still got the issue - before I entered the first loop. It makes sense that "press "Debug" button in the popup, then press twice [Ctrl+Break] and after this can continue without stops" because something in the combination of Office/Windows has not released the execution. It is stuck.

The redundant Ctrl+Break action probably resolves the lingering execution.

fthiella
  • 48,073
  • 15
  • 90
  • 106
user2052260
  • 179
  • 1
  • 2
17

One solution is here:

The solution for this problem is to add the line of code “Application.EnableCancelKey = xlDisabled” in the first line of your macro.. This will fix the problem and you will be able to execute the macro successfully without getting the error message “Code execution has been interrupted”.

But, after I inserted this line of code, I was not able to use Ctrl+Break any more. So it works but not greatly.

Stan
  • 5,106
  • 1
  • 17
  • 7
  • Thanks, Stan - that's certainly a solution if you're under time pressure and just want it to run! Very useful. – Phil Whittington May 02 '11 at 08:08
  • 1
    You can re-enable in the same execution it by setting it to `xlInterrupt`. (It also automatically re-enables when code execution finishes.) Your other answer worked for me, but worst case this could be used as a temporary fix. [More information on Application.EnableCancelKey](https://msdn.microsoft.com/en-us/library/office/ff834623.aspx) – Kodithic Apr 13 '15 at 23:53
  • 3
    The correct answer - and the only correct answer - is above. Inserting *any* line of code, including “Application.EnableCancelKey = xlDisabled” merely shifts the problem to another line. Often, the new 'breakpoint' is exactly the line you inserted. The only reason I am not downvoting your answer is that I have resorted to using it myself, repeatedly, over the last decade. – Nigel Heffernan Jul 10 '15 at 11:02
  • This one worked for me whilst the CTRL + Break wasn't – Damien Apr 25 '21 at 06:15
13

I found hitting ctrl+break while the macro wasn't running fixed the problem.

James
  • 131
  • 1
  • 2
3

I have came across this issue few times during the development of one complex Excel VBA app. Sometimes Excel started to break VBA object quite randomly. And the only remedy was to reboot machine. After reboot, Excel usually started to act normally.

Soon I have found out that possible solution to this issue is to hit CTRL+Break once when macro is NOT running. Maybe this can help to you too.

bazinac
  • 668
  • 5
  • 22
3

I would try the usual remedial things: - Run Rob Bovey's VBA Code Cleaner on your VBA Code - remove all addins on the users PC, particularly COM and .NET addins - Delete all the users .EXD files (MSoft Update incompatibilities) - Run Excel Detect & Repair on the users system - check the size of the user's .xlb file (should be 20-30K) - Reboot then delete all the users Temp files

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
2

I have had this problem also using excel 2007 with a foobar.xlsm (macro enabled ) workbook which would get the "Code execution has been interrupted" by simply trying to close the workbook on the red X in the right corner with no macros running at all, or any "initialize" form, workbook, or workheet macros either. The options I got were "End" or "Continue", Debug was always greyed out. I did as a previous poster suggested Control Panel->Programs and Features-> right click "Microsoft Office Proffesional 2007" (in my case) ->change->repair.

This resolved the problem for me. I might add this happened soon after a MS update and I also found an addin in Excel called "Team Foundation" from Microsoft which I certainly didnt install voluntarily

Carlito
  • 21
  • 1
2

Thanks to everyone for their input. This problem got solved by choosing REPAIR in Control Panel. I guess this explicitly re-registers some of Office's native COM components and does stuff that REINSTALL doesn't. I expect the latter just goes through a checklist and sometimes accepts what's there if it's already installed, maybe. I then had a separate issue with registering my own .NET dll for COM interop on the user's machine (despite this also working on other machines) though I think this was my error rather than Microsoft. Thanks again, I really appreciate it.

Phil Whittington
  • 2,144
  • 2
  • 16
  • 20
1

I would like to add more details to Stan's answer #2 for below reasons:

  • I faced this issue myself more than dozen times and depending on project conditions, I chose between stan's voodoo magic answer #1 or #2. When I kept on facing it again, I become more inquistive that why it happens in first place.

  • I'd like to add answer for Mac users too.

  • There are limitations with both these possible answers:

    • if the code is protected (and you don't know password) then answer #1 won't help.
    • if the code is unprotected then answer #2 won't let you debug the code.

  1. It may happen due to any of the below reasons:

    • Operating system not allocating system resources to the Excel process. (Solution: One needs to just start the operating system - success rate is very low but has known to work many times)

    • P-code is the intermediate code that was used in Visual Basic (before .NET) and hence it is still used in the VBA. It enabled a more compact executable at the expense of slower execution. Why I am talking about p-code? Because it gets corrupted sometimes between multiple executions and large files or just due to installation of the software (Excel) went corrupt somewhere. When p-code corrupts. the code execution keeps getting interrupted. Solution: In these cases, it is assumed that your code has started to corrupt and chances in future are that your Excel workbook also get corrupt giving you messages like "excel file corrupted and cannot be opened". Hence, as a quick solution, you can rely on answer #1 or answer #2 as per your requirements. However, never ignore the signs of corruption. It's better to copy your code modules in notepad, delete the modules, save & close the workbook, close the excel. Now, re-open the workbook and start creating new modules with the code copied earlier to notepad.

  2. Mac users, try any of the below option and of them will definitely work depending on your system architecture i.e. OS and Office version

    • Ctrl + Pause
    • Ctrl + ScrLk
    • Esc + Esc (Press twice consecutively)

You will be put into break mode using the above key combinations as the macro suspends execution immediately finishing the current task. This is replacement of Step 2.

  1. Solution: To overcome the limitation of using answer #1 and answer #2, I use xlErrorHandler along with Resume statement in the Error Handler if the error code is 18. Then, the interrupt is sent to the running procedure as an error, trappable by an error handler set up with an On Error GoTo statement. The trappable error code is 18. The current procedure is interrupted, and the user can debug or end the procedure. Microsoft gives caution that do not use this if your error handler has resume statement else your error handler always returns to the same statement. That's exactly we want in unwanted meaningless interruptions of code execution.
jainashish
  • 4,702
  • 5
  • 37
  • 48
0

My current reputation does not yet allow to post this as a comment. Stans solution to enter the debug mode, press twice Ctrl+Break, play on, save did solve my problem, but I have two unexpected twists:

  1. My project struture is password protected, so in order to get into the Debug Mode I had to first enter Developer mode, click on the project structure and enter the password.

  2. My project is a template file (.xmtl). I opened the file via double click which opens it as .xml with a "1" at the end of the previous file name. I fixed the bug as by Stans instruction and saved it as that ...1.xml file. When I then opened the template again, this time as template, and wanted to apply the same bug fix to that file, the bug was gone! I did not change this file and still no bug at executing the Macro. This means to me that the bug is not actually in the file, but in a (hidden) setting in Excel.

blablubbb
  • 115
  • 1
  • 4
0

If it's a phantom breakpoint:

1 Delete the offending line of code

2 Run the code again

3 Repaste the line

I found this laughably simple solution after spending a couple days wading through all the answers here and elsewhere. I figured, if I link it to my original question it might help some other poor chap, since the question it's on is VBA break execution when there's no break key on keyboard and this is more applicable.

Link to original answer

half of a glazier
  • 1,864
  • 2
  • 15
  • 45
0

I faced the same issue today. Resolved it with these steps.

  1. Create a new module
  2. Move the procedure that is causing the issue to this new module.
  3. Save project
  4. Run macro again.

This time, the code execution will run till completion without any intermediate stops.

OfficeTricks.Com
  • 108
  • 6
0

TLDR

Open the module with the problem and press the following keystrokes:

  • ctrl+home to go to the top of the module
  • ' to add a comment in front of Option Explicit
  • down to parse all without Option Explicit and tell VBA that all needs to be recompiled
  • up
  • backspace to uncomment
  • alt+d
  • l to trigger the recompilation of the whole module

The solution described above assumes the modules in the VBA project use Option Explicit, (which they should, it is very good practice). If your modules do not use it, then, instead of commenting and uncommenting the first line, you can select all the code, cut it and paste it back.

The details

This problem is caused by something going wrong with the incremental compilation.

When a module is edited, VBA compiles only the modified functions, not the whole project. Sometimes things go wrong with the incremental compilation and two things can happen at execution time: phantom breakpoints like the one mentioned here appear or errors about undefined or wrong type variables show up.

The answers that mention modifying one line often help, because they do trigger the compilation of the function containing that line, which in turn may trigger the compilation of other functions or even the entire project. But the problem sometimes pops back up because editing one line only may not trigger the compilation of the whole project.

The answer with the highest number of upvotes may help, but it requires first to reproduce the problem, which sometimes doesn't reproduce on the development machine.

The correct solution is to force the recompilation of the whole project.

I don't think there is an explicit way to do so, but it is possible to force the recompilation of the largest module, one that is used by or uses all modules and classes. There is no command to trigger such recompilation, but by changing one global directive will do the job.

In some cases it may be necessary to repeat the steps in multiple modules.

stenci
  • 8,290
  • 14
  • 64
  • 104