PDA

View Full Version : [SOLVED] On Error ...



MWE
03-17-2005, 03:15 PM
I have recently encountered an On Error problem in an Excel2000 application. In this appl, data is entered several times within a looping structure. The data should be numeric, but could be a mix of alpha and numeric (user input error). I have an On Error GoTo ErrorHandler line that directs execution to the ErrorHandler section where the problem is digested, an error message presented, execution directed to the line where input occurs (a few lines above where the problem happened) for new input. The code works fine for the first error. But if a second error occurs, the On Error does not trigger.

I wrote a simple test routine. It spits out an error message for the first "bad input", but fails if bad input occurs again.


Sub TestOnError()
Dim X As Single
Dim strBuffer As String
GetNum:
strBuffer = InputBox("enter value for X")
If strBuffer = "end" Then Exit Sub
On Error GoTo ErrorHandler
X = strBuffer
MsgBox Str(X)
GoTo GetNum
ErrorHandler:
MsgBox "Error: input contains non-numerics", vbCritical
GoTo GetNum
' Resume Next
End Sub

If I use the Resume Next line in the ErrorHandler section instead of the GoTo GetNum, I get the old value for X (understandably) and the proc marches on.

I have read through the On Error material in VBA Help and in a few VB manuals, but can find nothing about not triggering the 2nd time.

What is happening here?

Thanks

Andy Pope
03-17-2005, 04:34 PM
Hi MWE,

This works for me.


Sub TestOnError()
Dim X As Single
Dim strBuffer As String
GetNum:
strBuffer = InputBox("enter value for X")
If strBuffer = "end" Then Exit Sub
On Error GoTo ErrorHandler
X = strBuffer
MsgBox Str(X)
GoTo GetNum
ErrorHandler:
MsgBox "Error: input contains non-numerics", vbCritical
Resume GetNum
End Sub

Norie
03-17-2005, 06:23 PM
What about using the IsNumeric function in VBA to check that a number has been entered in the input box?

mdmackillop
03-17-2005, 06:34 PM
How about


Sub TestOnError()
Dim X As Single
Dim strBuffer As String
GetNum:
Do
strBuffer = InputBox("Enter value for X" & vbCr & "Leave blank to exit")
If strBuffer = "" Then Exit Sub
If IsNumeric(strBuffer) Then Exit Do
MsgBox "Error: input contains non-numerics", vbCritical
Loop
X = strBuffer
MsgBox Str(X)
GoTo GetNum
End Sub

acw
03-17-2005, 06:37 PM
Hi

how about using the application.inputbox facility. This will allow you to specify a type to ensure the input is numeric

strBuffer = application.InputBox("enter value for X",,,,,,,1)

Tony

MWE
03-17-2005, 08:06 PM
Thanks for all the suggestions. I was thinking about explicitly stating that I did not seek an alternative approach to the numeric conversion issue; rather I want to understand WHY the On Error GoTo statement does not work a 2nd time. It is now even more bothersome because of the claim that "Resume GetNum" works, but "GoTo GetNum" does not.

The initial problem and screwy behavior of On Error encouraged me to rethink the basic approach and I had already tried the "IsNumeric" check before I posted to this forum. That partially solved the problem, but further investigation led to other issues. I will probably end up writing my own version of IsNumeric to handle the numeric cases like 7/8 that the standard version can not. But that does not answer the fundamental question.

johnske
03-17-2005, 09:11 PM
Hi MWE,

You must use the resume statement to end error handling in a procedure, otherwise the error's still 'active'.

Another way you can do this is to exit the Sub to clear the error (write a small Sub that just gives your error message and returns to the main Sub) but that's the long way round the problem .... E.G.


Sub TestOnError()
Dim X As Single
Dim strBuffer As String
GetNum:
strBuffer = InputBox("enter value for X")
If strBuffer = "end" Then Exit Sub
On Error GoTo ErrorHandler
X = strBuffer
MsgBox Str(X)
GoTo GetNum
ErrorHandler:
ErrorSub
End Sub

Sub ErrorSub()
MsgBox "Error: input contains non-numerics", vbCritical
TestOnError
End Sub

The Resume method Andy gave is the simplest

HTH,
John

MWE
03-17-2005, 10:48 PM
Thanks again for the comments and suggestions. I now understand why my original code acted as it did. This has been a useful experience as it forced me to rethink the conversiion process. I ended up with a fairly simple, recursive routine that works quite well. It handles "bad input" and the several special cases I want to support, e.g., simple fractions. The extension to all basic math operators was pretty simple. I may end up with an On Error in there somewhere, but so far I have not been able to break it.


Sub Test_MWE_Str2Num()
Dim IRC As Single
Dim X As Single
Dim strBuffer As String
GetNum:
strBuffer = InputBox("enter value for X (enter end to quit)")
If LCase(strBuffer) = "end" Then Exit Sub
Call MWE_Str2Num(strBuffer, X, IRC)
MsgBox "original text: " + strBuffer + Chr(10) + _
"return code: " + Str(IRC) + Chr(10) + _
"numerical value: " + Str(X), vbInformation
GoTo GetNum
End Sub

Sub MWE_Str2Num(strValue As String, X, IRC)
' Title MWE_Str2Num
' Target Application: any supporting VB / VBA
' Function decodes a text string into its numeric equivalent; able to
' decode basic numerics, e.g., 123 , 123.456 , 1.25E3 , 1.25E-2, etc
' plus any combination of basic numbers and simple math operators,
' for example:
' 7/8 1.2/3.4
' 1+7/8 1.25 + 3/4
' 2+3
' 1/2 + 3/4
' 4.21*5.17
'
' Parentheses are allowed but are NOT interpreted as mathematical
' constructions, rather, they are simply ignored
' Limitations: Practically limited to simple expressions. Math operator
' sequence is +, -, *, /, \ left to right
' Processing is recursive, so any combination of numbers and
' operators can be accomodated, but results may be incorrect
' because of the math operation sequence.
' Passed Values
' strValue [input, string] text string
' X [returned, single] numeric value
' IRC [returned, integer] return code (0 = bad; 1 = OK)
' Public/Private Variables used: None
' MATools/MWETools Subroutines Called:
' MWE_Str2Num (recursively)
' VBA procedures called:
' IsNumeric
' Len
' Mid
' Replace
' External Files Accessed: NONE
' Orig Date 17-Mar-2005
' Orig Author MErdrich
' HISTORY
Dim I As Integer, IRC1 As Integer, IRC2 As Integer, Locn As Integer
Dim X1 As Single, X2 As Single
Dim MathOp(5) As String
MathOp(1) = "+"
MathOp(2) = "-"
MathOp(3) = "*"
MathOp(4) = "/"
MathOp(5) = "\"
' remove parentheses
strValue = Replace(strValue, ")", "")
strValue = Replace(strValue, "(", "")
' start with simpliest case of normal numeric
If IsNumeric(strValue) = True Then
X = strValue
IRC = 1
Exit Sub
End If
' not simple numeric; recursively process For I = 1 To 5
Locn = InStr(1, strValue, MathOp(I))
If Locn > 0 Then
Call MWE_Str2Num(Mid(strValue, 1, Locn - 1), X1, IRC1)
Call MWE_Str2Num(Mid(strValue, Locn + 1, Len(strValue) - Locn), X2, IRC2)
If IRC1 * IRC2 <> 1 Then GoTo NotNumeric
Select Case MathOp(I)
Case "+"
X = X1 + X2
Case "-"
X = X1 - X2
Case "*"
X = X1 * X2
Case "/"
X = X1 / X2
Case "\"
X = X1 \ X2
End Select
IRC = 1
Exit Sub
End If
Next I
' not numeric !
NotNumeric:
IRC = 0
End Sub