This morning one of my add-ins kept giving me
Run-time error ’438′; Object doesn’t support his property or method. I would click OK, and the error would immediately pop up again. The cause of the error was pretty simple – in fact I’m surprised it compiled before I shut down last night – but a variety of other circumstances made me have to Control-Break. Here’s the thrilling story.
I created a new toolbar button in code and screwed up one of the lines. Instead of
I forgot the equal sign. This was the cause of the error. Normally, this will cause a compile error, except that I had it deeply nested in With statements referencing the Controls property. Something like this
Dim cb As CommandBarPopup
Under these circumstances, .Controls is so loosely typed that it compiles fine. But that’s not the whole story.
I use the error handling system from Professional Excel Development. If you’re unfamiliar, here’s a short explanation.. One of the aspects of this error handling system is a global variable called gbDebug_Mode. It looks for a file on my computer and behaves differently if it’s me or you (because you don’t have the file). For you, it gives an error then stops. For me, it kicks me into the code so I can see what the problem is. That’s not really a problem, except…
The project is locked for viewing and password protected, so it doesn’t throw me in the code when there’s an error. I’m not sure what it was doing in this case that caused the error to keep repeating as apposed to just stopping. So to summarize:
I coded an error
In such a way that the compiler couldn’t detect it
In a procedure with error handling
In a project that was locked
And I ran it on a machine where the error handling throws me into the code
I was able to Control+Break at the error message and End the code. Then I could unlock the project and rerun it to find the problem.