Error handling

[TheMadTree]

Good afternoon,

I'm looking for a way to define an 'On error' statement that would just work for the line underneath the statement.
With the statement below, I'm looking for a (week)number from 1 to 53.

Weeknumber = InputBox("What week are you after")

If the user would enter anything else I would like it to refer to a section that I called 'NotValidInput'. Unfortunately the statement 'On error goto NotValidInput' seems to work for the whole macro not just for that one line.

Can anyone help me?

Thank you!

 

[Joe4]

You could have two "error-type" situations here:

- An invalid entry resulting in a VBA error (i.e. trying to enter a text entry into a numeric field). This is what you would typically use VBA error handling for. The key is to declare your variable as the correct data type (i.e. a numeric field like "byte"), so it knows what kind of data to exepect.

- An entry that is valid in VBA's eyes, but is not within your allowable range (i.e. numeric entries less than 1 or greater than 53). You can handle this with some simple data validation, but using an IF statement.

Here is some sample code which shows you how to structure all of this, and what your code may look like. I included lots of documentation to show you what each section of code is doing.

Sub MyMacro()

'   Declare your variable to the smallest numeric data type that is sufficient for your data
    Dim Weeknumber As Byte
    
'   Turn on error handling
    On Error GoTo err_handler
    
'   Prompt for entry
    Weeknumber = InputBox("What week are you after")
    
'   Check to see if numeric entry is within desired range
    If (Weeknumber < 1) Or (Weeknumber > 53) Then
        MsgBox "Please try again!  You must enter a number between 1 and 53!", vbOKOnly, "ENTRY ERROR!"
    End If
    
'   Turn off error handling
    On Error GoTo 0
    
'   Put the rest of your code here
'   ...
    
'   Need this line here so if there are no errors, you exit the macro before hitting
'   the error handling code
    Exit Sub
    
'   put the error handling code at the end of your macro
err_handler:
'   Handle the error code specifically given when a non-valid entry is made
    If Err.Number = 13 Then
        MsgBox "Please try again!  You must enter a number between 1 and 53!", vbOKOnly, "ENTRY ERROR!"
'   Return VBA error number and description for any other type of error
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[brucef2112]

Good afternoon,
I'm looking for a way to define an 'On error' statement that would just work for the line underneath the statement.
With the statement below, I'm looking for a (week)number from 1 to 53.
Weeknumber = InputBox("What week are you after")
If the user would enter anything else I would like it to refer to a section that I called 'NotValidInput'. Unfortunately the statement 'On error goto NotValidInput' seems to work for the whole macro not just for that one line.
Can anyone help me?
Thank you!

You may be able to avoid the GoTo NotValidInput with one of these two styles of coding.

You may not need all the features these two samples offer but they are flexible to handle the following issues;
*User Entered Nothing and clicked OK button.
*User Clicked CANCEL button.
*User Entered a valid number.

Look at the two of them and give em a try.
One uses a Do...Loop which I prefer and suggest you use because is a bit shorter and cleaner to use.
The other uses a GoTo which works the same but is kinda 'Old School'.(these days GoTo is usually reserved to handle System Errors or Exit a sub.

The Do...Loop

Sub InputBoxExampleLOOP()
    Dim CancelTest As Variant

    Do
        CancelTest = Application.InputBox("Enter the week number(1-52) you are looking for" & vbCrLf & vbTab & "     OR click CANCEL to quit.", "Valid Week number:")
        
        If CancelTest = False Then
            MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
            ' Put your code here if user clicked cancel, or even better, use this
            ' value after exiting Do...Loop to run your code there.
            
        ElseIf CancelTest = "" Then
            ' Put code here to do something if user enters nothing but most likely you just
            ' want to LOOP so they are asked again for input or to click cancel.
                    
        Else
            If Not IsNumeric(CancelTest) Then CancelTest = -1  'What they entered isn't a number, force CancelTest to neg# so we loop again.
            If (CancelTest >= 1 And CancelTest <= 52) Then
                MsgBox "You entered " & CancelTest & ".", 64
                'You have a valid value in 'CancelTest' to use, so it will exit the LOOP>
                
            End If
        End If
    Loop Until CancelTest = 0 Or (CancelTest >= 1 And CancelTest <= 52)
    


    'Its better if you wait to do stuff with the answer down here.
    If CancelTest = 0 Then
        ' Do stuff here if needed because the user clicked cancel.
        MsgBox "Task was canceled by user."
    Else
        ' You have a valid value in CancelTest to use so put your code here to work with it.
        MsgBox "You now have week number '" & CancelTest & "' to work with."
    End If
End Sub

The GoTo

Sub InputBoxExampleGoTo()
Dim CancelTest As Variant
showInputBox:
    CancelTest = Application.InputBox("Enter the week number(1-52) you are looking for" & vbCrLf & vbTab & "     OR click CANCEL to quit.", "Valid Week number:")
    
    If CancelTest = False Then
        MsgBox "You clicked the Cancel button, Input Box will close.", 64, "Cancel was clicked."
        ' Put your code here if user clicked cancel, or even better, use this
        ' value after exiting this IF...End to run your code there.
        
    ElseIf CancelTest = "" Then
        ' Put code here to do something if user enters nothing but most likely you just
        ' want to do the GOTO so they are asked again for input or to click cancel.
        GoTo showInputBox
        
    Else
        If Not IsNumeric(CancelTest) Then GoTo showInputBox 'they didn't enter a number, ask again.
        If (CancelTest >= 1 And CancelTest <= 52) Then
            MsgBox "You entered " & CancelTest & ".", 64
            'You have a valid value in CancelTest to use so put your code here to work with it or
            ' run code after exiting this nested IF...END IF. ie CancelTest equals zero cause user clicked Cancel or
            ' CancelTest is a number between 1 and 52.
        Else
            GoTo showInputBox
        End If
    End If
    
    'Its better if you wait to do stuff with the answer down here.
    If CancelTest = 0 Then
        ' Do stuff here if needed because the user clicked cancel.
        MsgBox "Task was canceled by user."
    Else
        ' You have a valid value in CancelTest to use so put your code here to work with it.
        MsgBox "You now have week number '" & CancelTest & "' to work with."
    End If
End Sub

 

[TheMadTree]

Thank you both for your spot on and detailed replies!
These explanations solved my issue!