Announcement

Collapse
No announcement yet.

On Error Goto Statement not working consistently

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • On Error Goto Statement not working consistently



    Hi,

    I've got the following code that's not working like I want it to.

    Code:
    Sub TestProcedure()
        Dim MyNumber As Integer
        On Error GoTo 1 'Redisplay InputBox
    1:
        MyNumber = 0 'Initialize variable
        MyNumber = InputBox("Enter an Integer between 1 and 20")
        MsgBox MyNumber
    End Sub
    What I'd like is to redisplay the InputBox in case the user inputs a non-numeric value. This works fine on the first instance of non-numeric input - the input box is redisplayed. However, the second time a faulty value is entered, a run-time error appears:

    "Run-time error '13': Type mismatch"

    Any help would be much appreciated.

    m

  • #2
    Re: On Error Goto Statement not working consistently

    mhabib,

    Check out the following example:


    Sub Do_While_Show()
    Dim vaInput As Variant


    Do
    vaInput = Application.InputBox( _
    Prompt:="Please enter a value:", _
    Title:="Numeric value", _
    Type:=2)
    Loop While vaInput = "" Or IsNumeric(vaInput) = False

    End Sub
    Last edited by XL-Dennis; April 11th, 2005, 03:27.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

    Comment


    • #3
      Re: On Error Goto Statement not working consistently

      hello mhabib

      I'm not sure why the error handler doesn't work on the second repitition, and no doubt someone else will be able to tell you. Meanwhile, consider this code which works:

      Code:
      Sub TestProcedure()
          Dim MyNumber As Variant
      1:
          MyNumber = 0 'Initialize variable
          MyNumber = InputBox("Enter an Integer between 1 and 20")
          
          If IsNumeric(MyNumber) = False Then
          GoTo 1
          End If
          
          MsgBox MyNumber
          
          
      End Sub
      Note that I just used a simple IF statement, rather than an error loop.

      best regards,
      hal

      Comment


      • #4
        Re: On Error Goto Statement not working consistently

        Thank you XL-Dennis and hal. I was trying to shortcut the system. But I guess a validation loop is the more structured of the two approaches.

        Regards,

        m

        Comment


        • #5
          Re: On Error Goto Statement not working consistently

          Hi m,

          The reason that your routine fails on the second error is that Excel is still in its error state from the first error. It needs to be reset using some form of Resume statement.

          For example:
          Sub TestProcedure()
          Dim MyNumber As Integer

          On Error GoTo errorHandler 'Redisplay InputBox
          1:
          MyNumber = 0 'Initialize variable
          MyNumber = InputBox("Enter an Integer between 1 and 20")
          MsgBox MyNumber

          Exit Sub 'Don't execute errorhandler at end of routine
          errorHandler:
          Resume 1
          End Sub
          Hopefully that explains why the code was crashing and how you can avoid it happening in the future.

          However, having explained that I would stick with the approache suggested by Dennis (The Type=2 argument ensures that only numbers can be entered).

          HTH
          Cross-poster? Read this: Cross-posters
          Struggling to use tags (including Code tags)? : Forum tags

          Comment


          • #6


            Re: On Error Goto Statement not working consistently [Resolved]

            Thanks for the explanation, Richie(UK)

            It all makes sense now.

            m

            Comment

            Working...
            X