Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error handling 1st go

Status
Not open for further replies.
Westicle

Westicle

Programmer
Joined
Aug 22, 2002
Messages
29
Location
GB
Hi

I'm having a go at handling errors for the first time and having trouble. What I am doing is looking up a staff number (entered on a userform) on a spreadsheet. If the staff number doesn't exist then this works fine, however if the staff number does exist it still picks up an error even though there isn't one. Any clues?

Set FlexSource = Worksheets("Data").Range("A8:IV60000")

On Error GoTo ErrorHandler

NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)
On Error GoTo 0

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 1004 ' the correct error?
MsgBox "That staff number does not exist on the Flex database"
Exit Sub
Case Else
MsgBox "some other error"
End Select
Resume
 
Sort by date Sort by votes
Westicle,

Som comments...

1) your code falls thru the ErrorHandler -- use Exit Sub of some other control structure

2) clear the err object
Code: 
    On Error GoTo ErrorHandler

        NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)
    On Error GoTo 0
  
 
    Exit Sub
    
ErrorHandler:    ' Error-handling routine.
    Select Case Err.Number    ' Evaluate error number.
        Case 1004    ' the correct error?
            MsgBox "That staff number does not exist on the Flex database"
        Exit Sub
        Case Else
            MsgBox "some other error"
    End Select

    Err.Clear


    Resume


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks Skip. But now it doesn't generate an error but doesn't continue with the code afterwards either?
 
Upvote 0 Downvote
First, You have Exit Sub in your error routine.

Second, try using On Error Resume Next
Code: 
    On Error Resume Next

        NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)
        
        If Err.Number <> 0 Then GoSub ErrorHandler
        
    On Error GoTo 0
  
 
    Exit Sub
    
ErrorHandler:    ' Error-handling routine.
    Select Case Err.Number    ' Evaluate error number.
        Case 1004    ' the correct error?
            MsgBox "That staff number does not exist on the Flex database"
        Case Else
            MsgBox "some other error"
    End Select

    Err.Clear

    Return

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
It still does the same. It seems as though even if there is no error it runs the second case and stops the code there.
 
Upvote 0 Downvote
Westicle,

Works for me!

Post your current code.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Sub Lookup_Click()

Set FlexSource = Worksheets("Data").Range("A8:IV60000")

StaffNumber = StaffNumberTB.Value


On Error Resume Next

NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)

If Err.Number <> 0 Then GoSub ErrorHandler

On Error GoTo 0

Exit Sub

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 1004 ' the correct error?
MsgBox "That staff number does not exist on the Flex database"
Case Else
MsgBox "some other error"
End Select

Err.Clear

Return


'Lookup employee personal details (for identification) and put on to form
FirstName = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 4, False)
Surname = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 5, False)
NameTB.Text = Surname & ", " & FirstName

blah blah blah

End sub
 
Upvote 0 Downvote
you NEVER execute the code AFTER the error routine
[tt]
'Lookup employee personal details (for identification) and put on to form
FirstName = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 4, False)
Surname = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 5, False)
NameTB.Text = Surname & ", " & FirstName

'blah blah blah
[/tt]
EVERYTHING after the On Error Resume Next executed JUST FINE????????

I still do not understand your problem!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote


"you NEVER execute the code AFTER the error routine" is that a question or a statement - are you saying there should not be any code after the error handler? Like I said i'm an Error newbie.

I tried replacing this - 'Lookup employee personal details (for identification) and put on to form
FirstName = Application.WorksheetFunction.VLookup etc.

with this - MsgBox "Ok"

I don't get the message box.

Let me confirm this - this line of code is throwing up the error "NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)"

and this is because the number does exist on FlexSource. So I put On Error in the line preceding the code I think will throw up the error?

 
Upvote 0 Downvote
you NEVER execute the code AFTER the error routine"

It is a statement of fact! It can NEVER happen!

I ran you code without a problem!

WHAT IS THE PROBLEM??? Please post your code!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Option Explicit

Dim FlexSource As Range

Dim NumberCheck As Variant

Dim StaffNumberRow As Long

'Variables relating to Personal Info
Dim StaffNumber As Long
Dim FirstName As String
Dim Surname As String
Dim Grade As Integer
Dim DOB As Date
Dim FullOrPartTime As String

'Variables relating to Flex choices
Dim PrivateMedical As String

Sub Lookup_Click()

Set FlexSource = Worksheets("Data").Range("A8:IV60000")

StaffNumber = StaffNumberTB.Value

On Error Resume Next

NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)

If Err.Number <> 0 Then GoSub ErrorHandler

On Error GoTo 0

Exit Sub

ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 1004 ' the correct error?
MsgBox "That staff number does not exist on the Flex database"
Case Else
MsgBox "some other error"
End Select

Err.Clear

Return


'Lookup employee personal details (for identification) and put on to form
FirstName = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 4, False)
Surname = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 5, False)
NameTB.Text = Surname & ", " & FirstName

Grade = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 13, False)
GradeTB.Text = Grade

DOB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 14, False)
DOBTB.Text = DOB

'Lookup employee Flex choices and put on to form

'Private Medical
If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 50, False) <> "" Then
PrivateMedical = "No Cover"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 51, False) <> "" Then
PrivateMedical = "Network Self Only"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 52, False) <> "" Then
PrivateMedical = "Network Self & Partner"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 53, False) <> "" Then
PrivateMedical = "Network Self & Children"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 54, False) <> "" Then
PrivateMedical = "Network Family"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 55, False) <> "" Then
PrivateMedical = "Premier Self Only"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 56, False) <> "" Then
PrivateMedical = "Premier Self & Partner"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 57, False) <> "" Then
PrivateMedical = "Premier Self & Children"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 58, False) <> "" Then
PrivateMedical = "Premier Family"
Else: PrivateMedical = "Not Returned Flex"
End If

PrivateMedicalTB = PrivateMedical

'Holiday

'Check to see if Employee is full or part time, then look for holiday choices.
If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 16, False) = 37.5 Then
FullOrPartTime = "Days"
Else: FullOrPartTime = "Hours"
End If

'Look for Holiday choices
If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 38, False) <> "" Then
HolidayTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 38, False) & " " & FullOrPartTime & " Reduced Holiday"
ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 39, False) <> "" Then
HolidayTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 39, False) & " " & FullOrPartTime & " Additional Holiday"
Else: HolidayTB = "None"
End If

'Vouchers

MandSTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 41, False)
SainsburyTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 42, False)
AsdaTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 43, False)
BootsTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 44, False)
JohnLewisTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 45, False)
GAPTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 46, False)
WhitbreadTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 47, False)
HMVTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 48, False)

'Childcare
ChildcareTB = "£" & Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 40, False)

'Computer
If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 48, False) = "y" Then
ComputerTB = "Interested"
Else: ComputerTB = "Not interested"
End If


End Sub
 
Upvote 0 Downvote
Common Westicle,

Nothing, NOTHING NOTHING NOTHING after ther error handler EVER executes!!!!!

Look at your control structure!!!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
So it should look like this:

Declare variables

Sub RunCode()

Some code

Error check
Some code
End Error check

Some code

ErrorHandler

End Sub
 
Upvote 0 Downvote
You have been told MANY times so here is another one.

The way you have your code set all the lines between
'Lookup employee personal details (for identification) and put on to form
and
end sub
(both inclusive)

DO NOT EXECUTE.

What you have there is like this.

A.
Set FlexSource = Worksheets("Data").Range("A8:IV60000")

StaffNumber = StaffNumberTB.Value
if error goto B
C.
terminate sub execution
B.
Select Case Err.Number
Case 1004
MsgBox ...
Case Else
MsgBox ...
End Select
goto C
... many lines of code never executed ...
end sub

Change your code to be like follows and it will do one of the following.
1- Proceed without any errors.
2- Issue an error and exit the sub

Code: 
Option Explicit

Dim FlexSource As Range

Dim NumberCheck As Variant

Dim StaffNumberRow As Long

'Variables relating to Personal Info
Dim StaffNumber As Long
Dim FirstName As String
Dim Surname As String
Dim Grade As Integer
Dim DOB As Date
Dim FullOrPartTime As String

'Variables relating to Flex choices
Dim PrivateMedical As String

Sub Lookup_Click()
        
    Set FlexSource = Worksheets("Data").Range("A8:IV60000")
    
    StaffNumber = StaffNumberTB.Value
    
On Error GoTo ErrorHandler

        NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)
        
    'Lookup employee personal details (for identification) and put on to form
    FirstName = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 4, False)
    Surname = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 5, False)
    NameTB.Text = Surname & ", " & FirstName
    
    Grade = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 13, False)
    GradeTB.Text = Grade
    
    DOB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 14, False)
    DOBTB.Text = DOB
    
    'Lookup employee Flex choices and put on to form
    
    'Private Medical
    If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 50, False) <> "" Then
        PrivateMedical = "No Cover"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 51, False) <> "" Then
        PrivateMedical = "Network Self Only"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 52, False) <> "" Then
        PrivateMedical = "Network Self & Partner"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 53, False) <> "" Then
        PrivateMedical = "Network Self & Children"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 54, False) <> "" Then
        PrivateMedical = "Network Family"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 55, False) <> "" Then
        PrivateMedical = "Premier Self Only"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 56, False) <> "" Then
        PrivateMedical = "Premier Self & Partner"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 57, False) <> "" Then
        PrivateMedical = "Premier Self & Children"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 58, False) <> "" Then
        PrivateMedical = "Premier Family"
    Else: PrivateMedical = "Not Returned Flex"
    End If
    
    PrivateMedicalTB = PrivateMedical

    'Holiday
    
    'Check to see if Employee is full or part time, then look for holiday choices.
    If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 16, False) = 37.5 Then
        FullOrPartTime = "Days"
    Else: FullOrPartTime = "Hours"
    End If
    
    'Look for Holiday choices
    If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 38, False) <> "" Then
        HolidayTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 38, False) & " " & FullOrPartTime & " Reduced Holiday"
    ElseIf Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 39, False) <> "" Then
        HolidayTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 39, False) & " " & FullOrPartTime & " Additional Holiday"
    Else: HolidayTB = "None"
    End If
    
    'Vouchers
    
    MandSTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 41, False)
    SainsburyTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 42, False)
    AsdaTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 43, False)
    BootsTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 44, False)
    JohnLewisTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 45, False)
    GAPTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 46, False)
    WhitbreadTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 47, False)
    HMVTB = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 48, False)
    
    'Childcare
    ChildcareTB = "£" & Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 40, False)
    
    'Computer
    If Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 48, False) = "y" Then
        ComputerTB = "Interested"
    Else: ComputerTB = "Not interested"
    End If
        
    Exit Sub
    
ErrorHandler:    ' Error-handling routine.
    Select Case Err.Number    ' Evaluate error number.
        Case 1004    ' the correct error?
            MsgBox "That staff number does not exist on the Flex database"
        Case Else
            MsgBox "some other error"
    End Select

    Err.Clear
    exit sub
    
End Sub


Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
Here's a TRUNCATED example
Code: 
Sub Lookup_Click()
        
    Set FlexSource = Worksheets("Data").Range("A8:IV60000")
    
    StaffNumber = StaffNumberTB.Value
    
On Error Resume Next

        NumberCheck = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 1, False)
        
        If Err.Number <> 0 Then GoSub ErrorHandler
 
    'Lookup employee personal details (for identification) and put on to form
    FirstName = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 4, False)
        If Err.Number <> 0 Then GoSub ErrorHandler

    Surname = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 5, False)
        If Err.Number <> 0 Then GoSub ErrorHandler

    NameTB.Text = Surname & ", " & FirstName
    
    Grade = Application.WorksheetFunction.VLookup(StaffNumber, FlexSource, 13, False)
        If Err.Number <> 0 Then GoSub ErrorHandler

'......
        
    Exit Sub
    
ErrorHandler:    ' Error-handling routine.
    Select Case Err.Number    ' Evaluate error number.
        Case 1004    ' the correct error?
            MsgBox "That staff number does not exist on the Flex database"
        Case Else
            MsgBox "some other error"
    End Select

    Err.Clear

    Return
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks guys I get it!!

I didn't know what this sentance meant "your code falls thru the ErrorHandler"

If I run it using the way Frederico says then the code all runs fine but I still get the msgbox saying "some other error" - I thought this was because there WAS another error occuring but if I remove:

Case Else
MsgBox "some other error"

then it works fine and doesn't produce an error.
 
Upvote 0 Downvote
If you did copy my code without changes you should not get that error.
Notice the "exit sub" before the "ErrorHandler:" label.



Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
Works beautifully thanks.

2 more questions and then i'll leave you in peace.

Does it have to be called ErrorHandler? - I got that from VBA help in Excel.

Is the general idea that you know some errors may occur so you counter for them in the error handler and do you always do this by saying If Err.Number is blah then perform blah?
 
Upvote 0 Downvote
Error handling is always a very trick technique, and is most of the times not fully implemented by the programmers.

In your case I think it is enough to know the follwing.

With a "on error" you would normally do two things

1- on error Goto a error handler label. (the name can be anything, but you will notice that most people use that name)
This is normally at the end of the sub/function, and will also normally exit the sub/function immediately or after a few repetitions of the same error.

2- on error resume next

This will sometimes be placed on the middle of the code to trap some very specific errors, that are known to happen, and that are dealt with the code following it.


In both cases you will do a validation on the err.number.



Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question Question
multiple err handling
Replies
6
Views
962
remeng
remeng
remeng
  • Locked
  • Helpful tip Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
2K
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
morechocolate
  • Locked
  • Question Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
2K
morechocolate
morechocolate
huiettcm
  • Locked
  • Question Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
782
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top