VBA Macro - "Code execution has been interrupted" error 1

[cgraeter]

We have a number of "automations" built into Excel documents using VBA that are used frequently in our business. Recently, a couple of our users have been getting a message box popping up on them when attempting to run the macros. It states "Code execution has been interrupted" as if someone had pressed ctrl+break. The options available are Continue, End, and Debug. Debug points to random lines of code each time. When continue is pressed, it moves right along without issue and gets the message again on a subsequent random line later in the code. Continuing to press continue allows the macro to finish.

Out of more than 40 users, only 2 have experienced this. I have googled this issue to death and found no indication of what causes it. Does anyone know why this might be happening and how to make it stop?

 

[cgraeter]

One additional note....

Rebooting makes the issue go away for a while at least. At some point, it eventually returns though.

 

[Cyntalan]

I've actually had this same thing happen to me on occasion, especially on long-running macros. Eventually, something happens in which it interrupts the macro as if I were to hit Ctrl+Break, even down to letting me continue. No actual error occurs, and the break happens at any given line of the macro (one recent break happened on a line only containing "Else"). My assumption is there's SOME sort of memory leak, which is why a reboot will fix things up for a time. As far as a permanent fix, I really wish I knew. Anyone have any ideas?

 

[musclemac]

I don't know if you've found a solution to this problem, i.e. the macro code interrupting for no apparent reason, but i've used the following code and it seems to help some of the time. I close out the last three tasks and start my project, in Excel, and it runs fine.

Private Sub Workbook_Open()

On Error Resume Next
Application.WindowState = xlMinimized
Dim m, app_visible As Integer
For Each myTask In Tasks
myTask.Activate
myTask.Close
If m = 3 Then Exit For
m = m + 1
Next myTask
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
app_visible = 1
End If
Next w
If app_visible = 0 Then
Application.Quit
ActiveWorkbook.Close savechanges:=False
Else:
ActiveWorkbook.Close savechanges:=False
End If
End Sub

 

[cgraeter]

Thanks for responding musclemac. I tried the code you provided, but it doesn't seem to be working. The code does not iterate through the tasks. I commented out the on error statement and I'm getting a type mismatch error associated with the myTask variable. How is this variable declared?

 

[Bong]

If your problem is, in fact, a memory leak issue, you might want to see if you're doing any of the suspicious actions mentioned here:

http://www.decisionmodels.com/memlimitsd.htm

_________________
Bob Rashkin

 

[cgraeter]

Thanks for the info, Bong. The only thing I can see that might be an area of concern for my code is destroying objects after their use. I've checked and all the objects I'm using are being destroyed explicitly.

If I had to guess, I would assume my issue is not related to a memory leak. Wouldn't more of my users be experiencing this problem if the code were causing the leak? Or am I not thinking about that correctly?

 

[musclemac]

You may have to reference the Word Object Library if you're using Excel.

 

[combo]

The programme does not break in random line if it is not a memory problem. You could try 'locals' window (in break mode to see what is going on.
It can be a problem with the design of your code. If, for instance, you set a global variable in a workbook and save it, the variable can be reset/cleared. To clear this, the code has to be separated from the working spreasheet. In tools>options, 'general' tab try to tick 'notify before state loss' to trap this case.

combo

 

[xlbo]

....or maybe your 2 out of 40 users have a habit of accidently pressaing {ESC} whilst the macro is running...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

Good point Geoff, Ockham's razor...

combo

 

[sommersm]

I've experienced this problem before on many an occasion. Sometimes a reboot works but sometimes it doesn't. I've run "Detect and Repair" in excel but this too didnt fix the issue. A complete re-installation of my excel by my company's IT area also failed to fix it. Then it disappeared...until this morning!

Every now and then I've seen it occur on someone else's machine when running some code I've written but it's rare.

So annoying. Any further thoughts apart from those suggested in the messages above?

 

[TonyJollans]

I have had this happen several times - usually, but not always, after some heavy testing. I have also seen reports of it in many places but never any suggestion as to what the root cause is or any possible fix.

When it starts happening, it will happen in all apps, so the problem is something in the VBE but beyond that, all I can say is what's been said before: closing all Office apps sometimes resolves it; rebooting always does but it will most likely return.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[cgraeter]

We continue to experience these errors off and on. If anyone comes across any explanation or solutions, please share. Thanks everyone!

 

[pelmore]

I had this issue pop up a few weeks back and it has continued after multiple restarts. I just recently remembered I changed my preferences in tools-options-general-recently used file list:
from 3 files to 9 files shown. (Thanks combo for jogging my memory) Once I changed this back to 3 the error has gone away.
My 2 cents.

 

[pelmore]

Ok, nevermind. It just started up again after my post. (sigh)