On Error ...

[MWE]

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]

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]

What about using the IsNumeric function in VBA to check that a number has been entered in the input box?

[mdmackillop]

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]

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]

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]

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]

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