Back to the issue at hand about my function. I believe you were both discussing additional checks aside from null and that appears to be what is needed. Debug.printing control names and values in my for loop shows that I don't actually have any nulls, what I have is "0" So, it looks like I need a null and 0 check.
That quote says that all forms of RESUME will clear the error in the procedure, it does not say that errors are not cleared on exit. It is a true statement. This is why a goto would be real bad. You would stay in the procedure and not clear the error unless you did it explicitly (err.clear).
Easy to prove
Code:
Private Sub DemoResume()
Dim x As Double
On Error GoTo DemoResume_Error
x = 1 / 0
Exit Sub
DemoResume_Error:
Call ErrMsg
End Sub
Public Sub ErrMsg()
Debug.Print Err.Number & " " & Err.Description
End Sub
Public Sub TestDemo()
DemoResume
Call ErrMsg
End Sub
I call the procedure which errors and prints the error message and the code exits. Then I print the message again.
Private Sub DemoResume()
Dim x As Double
On Error GoTo DemoResume_Error
x = 1 / 0
PrintAgain:
Call ErrMsg
Exit Sub
DemoResume_Error:
Call ErrMsg
Resume PrintAgain
End Sub
In this case you throw the error and print the message.
You resume and go back up to printagain
The error msg shows that the error was cleared without explicitly calling err.clear.
So any RESUME clears an error, and the error also clears when the procedure exits.
I would not say that at all. Mr. Pearson is making a very strong recommendation. If you stay in your module you should use resume because this clears the error. But nowhere does he say that in order to exit you must do a resume first in order to clear the error before exiting. He actually says the opposite.
What @Isaac and I were saying is there is no logic to resume only to exit. And nowhere does Chip Pearson suggest that. Again it is like jumping off a ladder and on the way down someone telling you to continue falling. It is going to happen anyways.
You will often see people on this and other forums tell you that you have to wear belts and suspenders, just in case. These same people tend not to understand or know what the "just in cases" are. I personally want to know what those "just in cases" are. Even if there were conditions that I knew of where exiting the procedure was unlikey to clear the error I would explicitly clear the error, not do the silly loop.
I would say that and I did . I think we are arguing semantics. But, hey, I like to sometimes as well. It's a Friday after all!
That quote about RESUME clearing and resetting the error object is a declarative statement of fact as you mentioned yourself. It cannot be a recommendation, it is simply a statement of truth. That being said, just because THAT statement is not a recommendation, does not mean that Chip (or you) are not recommending the use of RESUME. You most certainly are doing so. You simply are recommending it be used when needed and not just to add one more hop before exiting. Chip's article in particular gives some nice examples on how and why to use Resume when handling specific errors.
I also realize my post in #44 could have been taken to mean "I got it. Don't use RESUME." That was not my intent. But, I'll take on the responsibility for being a bit too succinct. There, semantics argued.
Not to beat this too much longer, but there are definite reasons when you may want to use this construct. But it is not when you only have a single line to exit the sub in the ExitHandler.
Code:
ExitHandler:
Exit Sub
ErrHandler:
Call ErrProcessor
Resume ExitHandler
That is the problem. People started to copy that blocking and started to think it was necessary for some reason that did not know. So here is where it makes sense
Code:
ExitHandler:
'Lots of code here that you want to happen
'Either when you get an error or do not get an error. More than just exit sub
Exit Sub
ErrHandler:
Call ErrProcessor
Resume ExitHandler
So above I want to do certain things when I get an error as well as when I do not get an error. Close a connection, log something, set a temp var, provide a message, turn off hourglass or progress meter, etc..
With the above if I do not throw an error all my regular code executes as well as the code in the passed the ExitHandler. If an error is thrown then that block of the code is also executed as desired.
Without the above construct you would end up duplicating the code in two places the main and in the error block.
Well, hopefully, they've read through this whole thread and won't do that. I know I'm changing my code. I can understand the frustration when one's blindly parrot someone else's suggestions. I parrot many people, but never blindly!
You actually just highlighted something that I rigged in the old error handling that is better covered by your "sample" code above. I had screen updating code that made more sense to leave in the proper process and finish turning back on even when there was an error.
You actually just highlighted something that I rigged in the old error handling that is better covered by your "sample" code above. I had screen updating code that made more sense to leave in the proper process and finish turning back on even when there was an error
I understand what you are stating and what Chip is stating in the portion you quoted. It would be useful if you could explain what he meant by the portion that I quoted in post #35
Are you saying that it is not necessary to do this clear or reset if you just end your error handler?
on error goto fail
'code that causes an error
...
carryon:
'''
'''
'''
exit sub
fail:
show error
goto carryon
end sub
after you catch the error you incorrectly goto the carryon point, and well carry on. If you now get another error it will crash the programme, as you are still running the error handler until you "resume" from the error handler, and the error handler code is not re-entrant. You have to "resume" from the error handler to reset it in order to trap the next error.
Now, the thing about an error handler is that you can't just blindly resume as if the error never happened. You have to understand what caused the error, and understand how you can handle it.
So if you are trying to, say, save a file that may already exist, you may want to delete the old file first, before you create a new one. If you get an error code that indicates the file is open, you can't delete the old one, and create the new one. If you get a code that says there was no file to delete, well you can carry on. You might even try to use a folder that doesn't exist. So your code has to understand the difference and react to the error that occurred.
On the other hand, you can write the program to just create a new file, automatically overwriting the existing one, but at the point you try to create the file, you might get a similar error that the file is in use, or that the folder you specified doesn't exist, so again you need to consider the error and react accordingly. Maybe the error codes are different, in slightly different circumstances.
So you end up with this sort of structure
Code:
on error goto fail
'code that causes an error
...
carryon:
'''
'''
'''
exit sub
fail:
select case error:
case x show error
tidy up
'no need to resume because you are ending the sub
exit sub
case y:
resume carryon
end select
end sub
eg - a specific example. if you open a recordset, and then while using the recordset, you get a run time error and just exit the sub, what happens to the recordset. Is it still active or not? So you need to consider how your error handler tidies up when it closes.
Back in post #21, I posted my standard error handling code which includes a Resume Exit_Handler line.
I've just caught up with this thread and see there has been a lot of discussion about that idea.
Like many developers, I try to reuse code, adapting as necessary, rather than reinvent from scratch.
In this case, I use MZ Tools to insert a standard error handling code in every procedure.
I also use VB Extensibility to get the name of the current procedure for use in any error message (not covered in post #21).
Whilst having an Exit_Handler section with nothing other than Exit Sub may appear superfluous, in many procedures I add additional code to tidy up before exit...whether or not an error occurs. That may well include setting a recordset to Nothing which I always do to ensure no possible ambiguity in whatever code may follow.
@ Genma - That's some good generic example code and scenarios. Chip's VBA Error Handling posted earlier covers similar topics. A nice post for those that may miss the link to Chip's page.
This has been a nice discussion. There is only one problem left...my function code doesn't work still! LOL. The fix is what's already been talked about early on, I need to trap Nulls, Zeros, ZLSs and Blanks. I just can't figure out exactly how I want to do that in code. I know there are code snippets that work out there. But, when I look at them I can't figure out how the programmatic logic works. Especially the ones that do a length check with Len(). If someone wants to walk me through the logic, then I'd be more comfortable copying and pasting.
That's helpful, I didn't know about this tool. I remember once I was inheriting a database with zero error handling. One of the first things I was going to do, until I was re-assigned to another project, was write some custom code to try to identify all procedures as text, edit the text to add error handling and reimport the modules. That was shaping up to be a challenging bit! I'm glad to hear MZTOOLS has it
@ Genma - That's some good generic example code and scenarios. Chip's VBA Error Handling posted earlier covers similar topics. A nice post for those that may miss the link to Chip's page.
This has been a nice discussion. There is only one problem left...my function code doesn't work still! LOL. The fix is what's already been talked about early on, I need to trap Nulls, Zeros, ZLSs and Blanks. I just can't figure out exactly how I want to do that in code. I know there are code snippets that work out there. But, when I look at them I can't figure out how the programmatic logic works. Especially the ones that do a length check with Len(). If someone wants to walk me through the logic, then I'd be more comfortable copying and pasting.
@Isaac, @MajP - Thank you for reposting your code suggestions!
As I mentioned, I don't want to use it until I understand what it's actually doing as a check. I may be further along MajP's suggestion, but I wouldn't mind an explanation of how both work.
Specifically about yours Isaac, is the string length of the number 0, 0? What about the string length of 5? This is where I get confused.
@Isaac, @MajP - Thank you for reposting your code suggestions!
As I mentioned, I don't want to use it until I understand what it's actually doing as a check. I may be further along MajP's suggestion, but I wouldn't mind an explanation of how both work.
Specifically about yours Isaac, is the string length of the number 0, 0? What about the string length of 5? This is where I get confused.
No ... you're testing the length of a string. The string is: ("" & Me.ControlName.Value)
The purpose of doing ("" & Me.ControlName.Value) is that it handles both ZLS's and nulls.
If the length of that string is 0, that means nothing is in the control, and your validation may want to catch that
Another perfectly valid way is to just write out both: If isnull(Me.ControlName) or Me.ControlName=""
No, the length of ("" & 5) (but I didn't really suggest '5', I suggested me.controlname.value, which will be a coerced to the string type by way of the concatenation), is 1
and I did 
. I think we are arguing semantics. But, hey, I like to sometimes as well. It's a Friday after all!
