A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
In my opinion, these are the two worst Excel errors ever:
A casual Google search will show these errors are as common as they are arcane. Below I’m going to detail my experiences with these errors and how I’ve fixed them. I won’t pretend my experiences are exhaustive, but hopefully they’ll contribute to our understanding of these errors.
When you run a macro, execution is constantly interrupted with a dialog box that says “Code execution has been interrupted” and you’re given a choice to continue, end, debug, or get help.
If you press continue, the dialog will keep reappearing. You’ve looked through your code but found no errors—and yet, the dialog will not stop appearing.
I’ve found this error often appears in VBA code using loops. Let’s say you realized you’ve accidentally written an infinite loop, so you use CTRL + Pause Break and then tell Excel to end execution. Or, alternatively, a runtime error results in your loop and you tell Excel to end execution rather than fixing the code and continuing.
I’m not an expert on how Excel works in the background, but it appears that when you break execution, that breakpoint is written somewhere in memory. If you do multiple breaks, you may end up writing that breakpoint multiple times. For whatever reason, these breakpoint instructions aren’t cleared when you stop your code.
My guess is, when you run your code again, Excel attempt to use an address in memory that still has a breakpoint instruction in it.
The most immediate fix is to save and then restart your computer. This will wipe out any data stored in your RAM.
In addition, Dave left the following comment in response to this article:
When the “Code execution has been interrupted” dialogue box appears hit debug and then hit ctrl break again (sometimes it you have to hit ctrl break more than once). This seems to reset what ever setting within excel was set after the first ctrl break.
Try your best not to break execution while in a loop if you don’t need to. I realize however, that’s not realistic advice. So here’s what I do: I plan my loops accordingly. Before running a loop, I include a comment above about my loop’s terminating condition. If I must press CTRL+BREAK during the loop, I never instruct Excel to END execution. Instead, I press Debug and in the line following the place execution has stopped, I write some code to help the loop terminate safely. For do/loop and while/wend loops, often this mean setting a Boolean condition to true or false. For for/next loops, this means setting the iterator to its max value. You can also do this for runtime errors.
A few forums have suggested you add the following line to your code:
Application.EnableCancelKey = xlDisabled
This code will disable execution breaks in your code. That means, if you get caught in an endless loop, you won’t be able to use CTRL+BREAK stop it. Therefore, I strongly advice against use this approach. It doesn’t really solve the problem, it ignores it. And, you lose the ability halt all code execution. It’s not a good idea.
When I first encountered this error, I though maybe there was something wrong with the file. So I copied all the code into another file. I also used the various code cleaner products out there written by Excel MVPS. It’s my belief – and I could be wrong about this – these fixes have only appeared to work when I’ve also reset my computer concurrently. I don’t advise against your trying them, but I can’t say for sure if they really solved the problem.
In my experience, properly handling breaks in loops is the best prevention.
You open an Excel file and the first thing you see is a message box that says “There was a problem sending the command to the program.” Then the file does not open.
This error appeared for me when I had some code that opened another spreadsheet. The code tested for certain items in the other spreadsheet. If a division by zero error occurred, I used the ON ERROR handler to close the other file and then close the current file running the code. Afterward, when I opened the current file again, I saw this popup.
Again, I’m not an expert on what happens behind the scenes in Excel. However, when you open another file with Excel, Microsoft appears to take advantage of something called the Dynamic Data Exchange, which is what Office applications use to communicate to one another. While handling my error, I never actually cleared the error out, I just told Excel to save and quit. Because of this, some error instruction was written into this file’s memory – and when I open the file again, the error persisted.
The most immediate fix is to do a clean start of Excel, and then to deselect Ignore other applications that use Dynamic Data Exchange (DDE) from Excel’s options menu. If you have Excel 2010 or 2013, you can find this item by clicking File > Options > Advanced tab > General section. For Excel 2007, click the Office Button > Excel Options > Advanced tab > General section.
If you are handling errors, make sure to use
Err.Clear in your error handler. Don’t let Excel close without handling and clearing all runtime errors. In fact, don’t do what I did: don’t use
ON ERROR to handle errors you can anticipate. Test for those instead. Use
ON ERROR for errors you can’t anticipate.
If you encounter this problem in your spreadsheet, make sure you understand what’s causing the error, even if it doesn’t happen very often. The fix above will allow you to once again open the file. But make sure to investigate the root cause, and try your best to make sure all potential errors are handled. I’ve seen Excel add-ins that instruct users who’ve had to perform a forced-shutdown of Excel that the method above is the “fix” to the problem. I suppose not all errors are foreseeable, but preventing forced shutdowns should be a primary goal of any product.
In this case, restarting my computer didn’t seem to provide any relief.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide