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
.OnAction = "PostToLog"
I put
.OnAction "PostToLog"
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
Sub test()
Dim cb As CommandBarPopup
With cb
With .Controls.Add(msoControlPopup)
With .Controls.Add(msoControlButton)
.OnAction "wrong"
End With
End With
End With
End Sub
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.
I use PED as well (what a wonderful and helpful resource). If you are using PED as I am, then when the CentralErrorHandler returns from presenting the error message, if it is in debugmode then the next statement is Stop followed by Resume. My explanation is that the Stop statement can not be executed because the project is locked, so it moves to the next statement which is resume – and hence the infinite loop begins. At least that is what I think is going on …
–Charlie
Brilliant Charlie. I’m sure that’s the reason.
Wow! An excellent solution. I also use PED (phenomenal book) and have experienced the same infinite loop sequence with the exact same conditions. Now I can look into coding a solution for it.
Thanks!
I added a Cancel button to the error message just for this purpose – so when it gets in the infinite loop I cancel and it processes like debugmode was not enabled
I used something similar to PED error-handling. I don’t like turning everything into functions, so instead created something that accomplishes the same “trickle-up” effect but allows subroutines.
I’m wondering how you all handle errors in class modules, specifically with the VBE set to break in them. Do you just treat all procedures of any type in class modules as top-level (that’s what I’m doing) and if not, how do you get errors to trickle back up? The PED says something like treat all events in class modules (except for initialize/terminate) as top-level, but doesn’t really address functions and properties.
PED actually contains two versions of the error handler, because Rob and I had a philosophical difference of opinion. He wrote the chapter so described the ‘everything-as-a-function’ approach, but I wrote the implementaion examples so put in the ‘re-raise the error’ alternative that works in subroutines, class modules and everything.
Doug: How do you do the trickle up? ByRef arguments?
In my classes, everything is an entry point, so no trickling goodness there. (Not everything, only son-insignificant properties and methods). I don’t like it, but I dislike it the least of everything else I’ve considered.
What’s the big deal about making every non-entry point routine a function? Just drop Call in front of the function name when you want to ignore the return value. But ignore return values at your peril.
Clearly anything which could be used as a udf, so called from worksheet formulas, is an entry point routine, so this sort of error handling is inapplicable. Also, anything which could be run from the Macro dialog is an entry point routine. What’s left are routines which aren’t meant to be exposed in Excel. Hard to see practical reasons these shouldn’t be functions returning success/failure.
If the concern is functions which should be returning large data objects, either adapt the PED approach to return a variant containing an array — returnvalue(1) being the success/failure status, and returnvalue(2) being the desired data object — or pass the function a ByRef argument which will hold the desired data object.
As for the PED error handler itself, as already mentioned, the problem is the bald Resume statement after the Stop statement. You need to add code to test whether you can access the VBProject property, and if so whether its Protection property is none. Only in that case do you run Resume; otherwise, you run Resume ErrorExit. Another alternative is using a registry key (harder) or environment variable (simpler) to store whether you’re in DEBUG mode. Have the error handler check it when deciding whether to call Resume ErrorExit or Resume.
[This is stuff C programmers have known/used for 30+ years. FORTRAN programmers may have been doing this even longer. Is it really true VB programmers only read VB programming texts?]
Dick, ByRef would be logical, but I really just like my Subs to be Subs.
So I’ve resorted to a Global gboolErrorState variable that’s set True in the main error handler when an initial error is passed to it. To further bend good modular practice, the main error handler has two static variables that set and then hold the Err.Number and Description passed from the original erroring routine.
In my error-handler code in each routine I specify whether it’s a top-level routine. That line looks like:
Dim boolTopLevelProcedure As Boolean: boolTopLevelProcedure = True (or False).
When the error handler is called I pass boolTopLevelProcedure and if it’s true, the error info from the static variable are included in the error message and logfile. Then the global and static variables are cleared.
That’s the main idea. It works well, but I did bump up against an error in a class module recently, and have taken the same approach as you, by setting boolTopLevelProcedure to true in all class routines.
Oh yeah, and once gboolErrorState is true and I’ve cycled to the main error handler and back to the calling procedure, I have to raise an error 9999 to kick it up to the next routine. With a class routine I was actually showing Error 9999 in user mode, which isn’t supposed to happen.
Does that all make sense? I’ve certainly learned a lot from tinkering with it over the last few last few years.
Thanks for that Stephen. It’s been a while since I re-read that chapter and I’d forgotten all about the rethrow method.
Stephen, I never noticed there was an alternative view in there. I’ll have to hit the book this weekend!
… Eight weekends later. Stephen, or anybody who can answer, two questions about your alternative Sub-friendly error-handling system:
1. Am I correct that I need to set the bEntryPoint argument to TRUE in all class procedures and properties to have it work with Break in Class Modules?
2. When gbDebugMode is TRUE, am I correct that it still keeps cycling through the error handler, rather than returning to On Error Got0 0 type behavior? In my system, which is similar, when the global debugging variable is set, error-handling is basically ignored. This has pluses and minuses, but I do like being dropped back at the error. On the other hand, maybe I broke something in my copying and pasting of your code.
Hi,
I have posted a question to the following blog entry about the central error handler approach used by the PED book. May be this is a better post entry instead.
http://www.dailydoseofexcel.com/archives/2007/05/04/error-handling-template/
My main question is: how do you use the Global Error Handler Approach with Re-Thrown method in an Excel UDF function?
If I set in the Excel UDF EntryPoint = true, if an error happens the Global error handler will show me a msgbox, which for an excel function is something to be avoided.
If I set the Excel UDF, EntryPoint = false, is and error happens an error will be re-thrwon (with debugmdoe = false), but nobody else will be able to capture it.
In addition if a function is called let’s say 200,000 time, the error handler will log 200,000 line in the log file too.
What shall I do?
Many thanks
Pierpaolo
Pierpaolo,
There is lots that you can do, but you will have to tweak the central error code a bit. You could store the error message in a global variable (rather than use msgbox) and then return that error message as part of the UDF. Or you could save the error message to an error log (however as you mentioned you could get 200000 messages). Or you could detect when the error occurred the first time, save the message or expose it to the user via msgbox, and then skip all remaining invocations of your UDF until something is cleared.
Hi Charlie,
Thanks for tips. I thinks I am going to skip using the central error handler when it comes to user UDFs.
It will make my life easier.
Hi Charlie,
Thanks for tips. I think I am going to skip using the central error handler when it comes to user UDFs.
It will make my life easier.