PDA

View Full Version : Solved: Problem with error handling with GoTo Statement



nitt1995
07-08-2006, 02:33 PM
I have written this small code snippet that I would like to accept a decimal-precision input (via the CDec command) and give the user the ability to cancel out of the routine.

The routine is set up so that if an invalid input is entered, an error message appears and the user is prompted to try again. This works the first time, but the second time the error handling appears to be ignored (giving my a Type Mismatch Error).

Any ideas?


Sub main()
Dim n As Variant

10: On Error GoTo CatchTypeError
n = (InputBox(Prompt:="Input Value"))
If n = "" Then
Exit Sub
Else: n = CDec(n)
End If

MsgBox Prompt:="Your value is: " & n
GoTo 10
Exit Sub
CatchTypeError:
InvalidNumberError
GoTo 10
Exit Sub
End Sub
Sub InvalidNumberError()
MsgBox Prompt:="Invalid Number" & vbCrLf _
& "Please enter number >= 0 and" & vbCrLf _
& "<=79,228,162,514,264,337,593,543,950,335", _
Buttons:=vbExclamation
End Sub

malik641
07-08-2006, 03:10 PM
Hey nitt1995 and welcome :)

I'm not exactly sure what was going wrong with that error handling technique you have....:think: But here's how I managed to solve it:

Sub main()
On Error Resume Next
Dim n As Variant

10:
n = (InputBox(Prompt:="Input Value"))
If n = "" Then
Exit Sub
Else
n = CDec(n)
If Err Then GoTo CatchTypeError
End If

MsgBox Prompt:="Your value is: " & n
GoTo 10
CatchTypeError:
InvalidNumberError
GoTo 10
End Sub
Sub InvalidNumberError()
MsgBox Prompt:="Invalid Number" & vbCrLf _
& "Please enter number >= 0 and" & vbCrLf _
& "<=79,228,162,514,264,337,593,543,950,335", _
Buttons:=vbExclamation
End Sub
I hope someone else can explain what was wrong with your original coding.

HTH :thumb

Cyberdude
07-08-2006, 04:54 PM
Where you have

CatchTypeError:
InvalidNumberError
Goto 10
Change the GoTo 10 to Resume 10.
(At least that corrected it for me.)

Sid

nitt1995
07-09-2006, 06:00 AM
Thanks for your help! Your modification works great, but I needed a small change. If you don't set Err = false in the catch statement before you return to the main code, it will always trip the error (once tripped), even with a valid input.


Hey nitt1995 and welcome :)

I'm not exactly sure what was going wrong with that error handling technique you have....:think: But here's how I managed to solve it:

Sub main()
On Error Resume Next
Dim n As Variant

10:
n = (InputBox(Prompt:="Input Value"))
If n = "" Then
Exit Sub
Else
n = CDec(n)
If Err Then GoTo CatchTypeError
End If

MsgBox Prompt:="Your value is: " & n
GoTo 10
CatchTypeError:
InvalidNumberError
GoTo 10
End Sub
Sub InvalidNumberError()
MsgBox Prompt:="Invalid Number" & vbCrLf _
& "Please enter number >= 0 and" & vbCrLf _
& "<=79,228,162,514,264,337,593,543,950,335", _
Buttons:=vbExclamation
End Sub I hope someone else can explain what was wrong with your original coding.

HTH :thumb

nitt1995
07-09-2006, 06:08 AM
Thanks!


Where you have

CatchTypeError:
InvalidNumberError
Goto 10
Change the GoTo 10 to Resume 10.
(At least that corrected it for me.)

Sid

skatonni
07-10-2006, 06:33 PM
In the version from Malik641, I think the first Goto 10 should be Exit Sub. It works despite the warning here, http://www.cpearson.com/excel/ErrorHandling.htm "Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers."

Perhaps Cyberdude's solution is related to this:
"All forms of the Resume clear or reset the Err object."

johnske
07-10-2006, 07:03 PM
Hi skatonni, and welcome to the board...


In the version from Malik641, I think the first Goto 10 should be Exit Sub. It works despite the warning here, http://www.cpearson.com/excel/ErrorHandling.htm "Do not use the Goto statement to direct code execution out of an error handling block. Doing so will cause strange problems with the error handlers."Yes, it should have been Exit Sub


Perhaps Cyberdude's solution is related to this:
"All forms of the Resume clear or reset the Err object."That is correct, the error hasn't been handled properly until it's been cleared from the innards of the machine, and this can only be done with a Resume, Exit, or End statement of some kind, On Error Goto 0 doesn't do it, neither does Err.Clear... For more, have a look here (http://xlvba.3.forumer.com/index.php?showtopic=17)

malik641
07-10-2006, 07:46 PM
Hi skatonni, and also welcome


In the version from Malik641, I think the first Goto 10 should be Exit Sub. I just figured nitt wanted to repeat the process untill "" was entered in the input box :dunno

BTW, looks like some good reading. I'll have to get to that :thumb thanks guys