Edit (also in the comments section) –
Quite recently, I ran into this error again, and couldn’t quite seem to fix it with the EnableCancelKey thing.. I had mistyped ‘False’ for ‘xlDisabled’. But thanks to that, I found a new way (lot easier and quicker) to fix this thing –
Just press Ctrl + Break twice, and the Break mode is disengaged! Isn’t it something??
*************************************** Original Post ***************************************
Yet another bug! I’ve been quite heavily into VBA lately, and that’s one reason for such error posts! This has been quite a pain in the neck for VBA programmers, esp. if you haven’t quite hit upon this before.
This is how the story goes…
You’re heavily into the coding of your product, and suddenly, you figure out that you’ve entered an infinite loop. What do your instincts say?? Ofcourse! Ctrl+Break! Now, that was eass, wasn’t it… But what follows isn’t!
So, going forward, you actually go on making more tweaks in your code, and as you run it, you suddenly find an error (the title name). You try running it manually then; to your surprise, it runs quite fine. Again, when you run the whole code, the error pops up. To your frustration, this happens quite often within one piece of code! No Logical or Autorun Errors, and still… You finally turn to the one last resort – Google!
And the story ends thereafter ~
Here’s the reason why this happens. This is a so called poorly documented piece of information by Microsoft, but we users have taken care of that.
Once you press Crtl+Break, it trips off a mode that makes the compiler go to the break mode at random intervals. Nothing you do can stop this, unless you restart your machine. This is specially the case when you’ve got custom formulas, that run devoid of any programming.
Otherwise, you could easily put a fix to this in your program by using the following line at the start of your procedure:
Application.EnableCancelKey = xlDisabled
This helps reset the errorhanding at the end of the execution of your code. Which implies, you don’t need to worry about xlEnabled. But as I said, sometimes, it’s only a restart that can help you! (Don’t forget to save before you restart) Quite a pain in the ***! Especially if you are in the middle of something.
Info on EnableCancelKey
This property has three modes as listed below:
xlDisabled -> which ignores pressed keys and solves the problem
xlInterrupt -> which sets the error handling back to Excel’s control
xlErrorHandler -> wich makes error go to your error handling section
Thus when the code you are running is done, this property is set back to its default. Hence no need to worry about that.
A nice piece of information if ever you get caught in this.
"EXCEL" Ytics | Every day in the life of an Analyst 