The two WORST Excel Errors EVER

In my opinion, these are the two worst Excel errors ever:

  • Code execution has been interrupted, and
  • There was a problem sending the command to the program

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.

Code execution has been interrupted

The problem

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.

codex

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.

What I think is happening

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.

How to fix 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.

How to prevent it

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.

What NOT to do

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.

What hasn’t really worked for me

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.

There was a problem sending the command to the program

The problem

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.

image

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.

What I think is happening

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.

How to fix it

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.

How to prevent it

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. 

What NOT to do

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.

What hasn’t really worked for me

In this case, restarting my computer didn’t seem to provide any relief.

Advertisements

6 thoughts on “The two WORST Excel Errors EVER

  1. Dave

    With the code execution has been interupted issue this is what I do to fix it :

    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.

    Reply
  2. Baz

    “Code execution has been interrupted” is the bane of my life. The ‘Ctrl Break’ technique does nothing for me. Setting objects to nothing at the end seems to help.
    If it gives anybody a clue what is happening, I have a file with code run from a commandbutton. After the first use of the button, I reliably got the dreaded dalogue box on every subsequent click – it debugged on the click event line. I tried a few increasingly random code changes; the final one was to select a cell on the sheet with the commandbutton on it. It has worked perfectly since then.

    Reply
    1. Jordan G Post author

      Well, the trick may be just to avoid it in the future by not doing ctrl breaks in a loop if you can help it. Restarting your comp should clear out the error.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s