Excel macro execution jumps to VBE window as if a breakpoint has been set.
I periodically have different users call up and say that they have the VBE window appear while running a macro.
When I look at their monitor, the syntax line it stopped at is highlighted in yellow, as if a breakpoint had been set there.
They will either get no message at all or 'excecution interrupted' dialog box. They do not get the 'End' 'Debug' dialog box that normally appears when an error occurs.
They have not pressed the 'Esc' or 'Ctrl-Break' keys either to pause the macro.
If the F5 (Run Sub/Userform) key is pressed, excecution continues and finishes normally.
If Excel is restarted and the macro is re-run, the code breaks at the same point in the code.
If the PC is rebooted, the macro then runs normally for a number of days or weeks depending on how frequently they run the macro.
This happens to different macros on different PC's, and usually having the user reboot weekly prevents the issue from reoccuring.
Recently one user continues to have the problem even after rebooting. So I am looking for advice.
As a point of reference most of the PC's are running Excel 2003 (no 2007) on Windows XP SP3; 'Tools-Options' is set to 'Break on Unhandled Errors'
When I look at their monitor, the syntax line it stopped at is highlighted in yellow, as if a breakpoint had been set there.
They will either get no message at all or 'excecution interrupted' dialog box. They do not get the 'End' 'Debug' dialog box that normally appears when an error occurs.
They have not pressed the 'Esc' or 'Ctrl-Break' keys either to pause the macro.
If the F5 (Run Sub/Userform) key is pressed, excecution continues and finishes normally.
If Excel is restarted and the macro is re-run, the code breaks at the same point in the code.
If the PC is rebooted, the macro then runs normally for a number of days or weeks depending on how frequently they run the macro.
This happens to different macros on different PC's, and usually having the user reboot weekly prevents the issue from reoccuring.
Recently one user continues to have the problem even after rebooting. So I am looking for advice.
As a point of reference most of the PC's are running Excel 2003 (no 2007) on Windows XP SP3; 'Tools-Options' is set to 'Break on Unhandled Errors'
ASKER
In your experience, when the macro starting acting up, was it acting up on all for all the other users too?
And after rebuilding/reimporting all the code, did it cause the same problem after using it a while again?
My current problem macro (residing on the network) was stopping on one user, but another user right beside them was not having any problems using the same macro file.
And after rebuilding/reimporting all the code, did it cause the same problem after using it a while again?
My current problem macro (residing on the network) was stopping on one user, but another user right beside them was not having any problems using the same macro file.
This issue appears to be very random. I'm not sure exactly why it behaves the way it does. Sometimes exiting Excel and coming back in fixes the issue for a single user. Sometimes rebooting solves the issue. And the issue always appears to affect people differently.
Microsoft used to have a knowledge base article about this, but I haven't been able to locate it again.
Chris
Microsoft used to have a knowledge base article about this, but I haven't been able to locate it again.
Chris
ASKER
'Very random' is a good description of the problem. In reference to my previous post, once your rebuilt the macro, have you had this problem reoccur again later on with the same macro?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your comments and perspective. You mentioned that it occurred for you mainly during the development stage. Most of my occurances have been after the macro has been in production for a while. I think I have stumbled across a solution for my last occurance that has been working for about two weeks now.
I first shut down the PC, waited a few minutes and started it again. Since this macro had its file properties as 'Read-Only', I unprotected it opened it in Excel and ran the macro. When the macro stopped and displayed the VBE editor, I halted the macro and clicked the 'Debug' menu- 'Clear All Breakpoints' (even though the highlighted statement was NOT marked as a breakpoint). I then saved the macro and closed Excel and marked the file as 'Read-Only'.
So far it has run normally. Anyway, it is one more thing to try if anyone else runs across this problem.
I first shut down the PC, waited a few minutes and started it again. Since this macro had its file properties as 'Read-Only', I unprotected it opened it in Excel and ran the macro. When the macro stopped and displayed the VBE editor, I halted the macro and clicked the 'Debug' menu- 'Clear All Breakpoints' (even though the highlighted statement was NOT marked as a breakpoint). I then saved the macro and closed Excel and marked the file as 'Read-Only'.
So far it has run normally. Anyway, it is one more thing to try if anyone else runs across this problem.
ASKER
Appreciated sharing your experience with this, as it has been difficult to find any communications on this subject.
Glad I could help.
You could try the following line at the beginning of your code. It's supposed to disable the ability to cancel execution... Application.EnableCancelKe