"Application-defined or object-defined error" no line or indication of where though

[MrTeeny]

I occasionally have a box coming up with the message "Application-defined or object-defined error" but there's no indication of where the error is occurring, I don't get a debug link or any line highlighted. If I click the OK the sheet continues as normal. Is there anyway I can try and debug to catch this error so I can add some trapping to avoid it.

The sheet's quite large with lots of routines so no point posting it or parts as there's no indication where the error is coming from. Any pointers to what the error message is related to would be hopefully be some help.

Thanks

 

[ACommandLineKindaGuy]

Far better to fix the problem than error checking to avoid it. But if you're set on that, then just add an On Error Resume Next to the start of each routine.

Assuming you really want to fix the issue, I'm assuming you compiled the code.

Without more information, all I can suggest is cycle through the most likely routines by placing a break at the start of the code and then either execute code line by line, or run the code to cursor and try to sneak up on it that way. I hate this kind of debugging

 

[Fluff]

But if you're set on that, then just add an On Error Resume Next to the start of each routine.

I would STRONGLY recommend against doing that, it's about the worst thing you can ever possibly do.

 

[ACommandLineKindaGuy]

I would STRONGLY recommend against doing that, it's about the worst thing you can ever possibly do.

Agreed, and I said as much, if not as emphatically as you!

 

[RoryA]

I don't get a debug link or any line highlighted

Do you typically use error handling in your routines that reports the error message?

 

[rlv01]

One way to debug something like that is to set breakpoints in your code then execute from the debugger. If the code executes to the first breakpoint then you set another a bit further along. This should help you narrow down the problem code. A similar method is to sprinke Debug.Print statements in your code. So for example, the first one would be Debug.Print "1" and the next Debug.Print "2" and so on. That way you will be able to tell approximately where the code crashes and start the narrowing-down process.

 

[MrTeeny]

Do you typically use error handling in your routines that reports the error message?

No but usually when I get an error in my excel code it jumps to the offending line and highlights it in yellow, no such luck with this.

One way to debug something like that is to set breakpoints in your code then execute from the debugger. If the code executes to the first breakpoint then you set another a bit further along. This should help you narrow down the problem code. A similar method is to sprinke Debug.Print statements in your code. So for example, the first one would be Debug.Print "1" and the next Debug.Print "2" and so on. That way you will be able to tell approximately where the code crashes and start the narrowing-down process.

Annoyingly there's hundreds of lines of code code and sub routines and the "Application-defined or object-defined error" doesn't give any indication of what's gone wrong. On top of that it doesn't occur all the time. It's a program that receives regular data from elsewhere to update the sheet via excels COM and the code then kicks off from a Worksheet change event. Sadly I think I may have to litter the code with debug.prints to try and find where it's failing if there's no easy way to pinpoint where the code fails.

 

[rlv01]

Annoyingly there's hundreds of lines of code code and sub routines and the "Application-defined or object-defined error" doesn't give any indication of what's gone wrong. On top of that it doesn't occur all the time. It's a program that receives regular data from elsewhere to update the sheet via excels COM and the code then kicks off from a Worksheet change event. Sadly I think I may have to litter the code with debug.prints to try and find where it's failing if there's no easy way to pinpoint where the code fails.

That there are hundreds of lines of code does not matter. I have been in your shoes with larger projects than that. Using the bisection method you can quickly find the offending line(s) of code. Let's say your project has 1000 lines of code. The first debug.print (or breakpoint) goes near line 500. Then you run your code. If it crashes before reaching the line 500 Debug.Print, then the next Debug.Print statement you add goes near line 250. On the other hand, if it makes it to the line 500 Debug.Print statement without crashing, then your next Debug.Print statementgoes near line 750. You just keep cutting the range in half in that manner followed by a new test run and you will quickly narrow things down.