Any Excel VBA gurus out there?

[WhiteD3]

I've got some VBA code that's been working fine but now generates a "code execution has been interrupted" error every time it runs.

The error occurs at the same line in the code.
If I step through the code the error doesn't occur, only when I run the code in full.

Had a look on the net and this appears to be a common enough problem but I haven't found an answer.

Would appreciate any help.

Cheers.

[dmdigital]

If you can post up the line of code it fails on that would help. Check the References to make sure it has all the libraries it needs.
Also check your macro security settings and try removing (by renaming just to be safe) the excel.xlb file from your profile.

[Ferret]

try adding

"Application.EnableCancelKey = xlDisabled" to your code so it executes first thing - something like this

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
End Sub

Not sure - might help

[WhiteD3]

Turned out to be one of those Bill Gate plots...

Earlier this afternoon I'd opened and tested some code for a status bar update to add to the app I'm writing, but the only way to stop it was a ctrl break. I'd closed the file but Excel still saw the ctrl break. I shutdown Excel, same thing.

I rebooted and all's well. Now I remember reading something about Excel not letting go of ctrl-break commands when I was searching the net earlier.

Thanks for the feedback guys.

[WhiteD3]

try adding

"Application.EnableCancelKey = xlDisabled" to your code so it executes first thing - something like this

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
End Sub

Not sure - might help

Ferret, thanks for that but what are the ramification in using this for the app I'm writing?

[dmdigital]

It will disable the Esc key from working (most times). Trouble with Ctrl+Break is it can go through to the kernel interrupts and I'm not sure if this command will trap that as well.

[Ferret]

Ferret, thanks for that but what are the ramification in using this for the app I'm writing?

It prevents the Esc key from interrupting code execution, problem is if you encounter an infinite loop you will need to ctrl/alt/delete instead. The implications of this for your application only you can say.

I don't know exactly why your code execution is being interrupted but if you have been doing a lot of renaming of modules, deleting modules, copying parts of code from one module to another etc etc. the kinds of thing everyone does when developing large applications then occasionally whatever manages this process within VBA gets it knickers in twist.

The complete solution is to export your modules out to text files and reimport then again so VBA rebuilds your application cleanly.

This tedious so just go here VBA Code Cleaner (http://www.appspro.com/Utilities/CodeCleaner.htm) download and install CodeCleaner. It is safe I have used it many times without problem but you know how it goes - backup first just to be sure

Maybe this will fix your problem without the 'EnableCancelKey' hack. Would be interest to know if it does.