VBA error handling not working when function being called generates error

Question

I am iterating through rows,and looking up the first column of each row(name) using a different function for finding his marks.

For each "name" there is a particular entry in a different table("marks") which can also be blank or "-"

Sub main()
On error goto errorhandler
Dim name as string
Dim marks as double
Dim source as range
Dim runs as integer

runs = 1
Set source = Sheets("input").Range("$A$2")
i=1

Do until source.offset(i,0) = ""        'iterate through rows 

    name = source.offset(i,0)
    marks = find(name)

    do until runs * marks > 100
        runs = runs + 1 'since marks is not defined;runs overflows 
    Loop
    'a lot of code which relies on marks
errorhandler:
    i = i + 1
Loop

End Sub

Function find(name as string) as double
    find = application.vlookup(name,Sheets("values").Range("$A$2,$C$5"),2,0)
End function

now as i said the value in column 2 of that table can also be blank or "-" and thus results in error Runtime error 13 "Type mismatch"

i even tried putting on error statement inside the loop VBA should normally search for error handling in the calling function i.e "main" but its not doing so

@Siddharth sorry, I miss read it, it was an edit by the OP, removing my comment — chris neilsen
– chris neilsen, Commented Mar 6, 2012 at 10:43

Siddharth Rout · Accepted Answer · 2012-03-06 11:14:43Z

2

TRIED AND TESTED

Sub main()
    On Error GoTo errorhandler

    Dim name As String
    Dim marks As Double
    Dim source As Range

    Set source = Sheets("input").Range("$A$2")
    i = 1

    Do Until source.Offset(i, 0) = ""       'iterate through rows
        name = source.Offset(i, 0)
        marks = find(name)
        Debug.Print marks
        i = i + 1
    Loop

    Exit Sub
errorhandler:
    MsgBox Err.Description
End Sub

Function find(name As String) As Double
    find = Application.WorksheetFunction.VLookup(name, Sheets("values").Range("$A$2:$C$5"), 2, False)
End Function

EDIT: Kartik, Sorry, I didn't see that you have already accepted the answer.

FOLLOWUP

actually i dont want to print any error message instead straightaway skip to the next iteration – Kartik Anand 14 secs ago

In that case you are handling error in the wrong section ;)

Try this

Sub main()
    Dim name As String
    Dim marks As Double
    Dim source As Range

    Set source = Sheets("input").Range("$A$2")
    i = 1

    Do Until source.Offset(i, 0) = ""       'iterate through rows
        name = source.Offset(i, 0)
        marks = find(name)
        Debug.Print marks
        i = i + 1
    Loop
End Sub

Function find(name As String) As Double
    On Error GoTo earlyexit

    find = Application.WorksheetFunction.VLookup(name, Sheets("values").Range("$A$2:$C$5"), 2, False)

    Exit Function
earlyexit:
    find = 0
End Function

edited Mar 6, 2012 at 11:14

answered Mar 6, 2012 at 10:43

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Kartik Anand Over a year ago

actually i dont want to print any error message instead straightaway skip to the next iteration

Kartik Anand Over a year ago

but vba says if it encounters an error in a function it will check for error handling in the calling sub.so shoudnt it pass to main???and moreover putting it in the function wont allow me to skip a certain percentage of my code which will produce an error in main

Kartik Anand Over a year ago

your code isnt working as i said,it doesnt skip "the lines of code it should have" and thus produces an overflow error(which vba doesnt handle)

Siddharth Rout Over a year ago

@Kartik Anand: I have updated the code now. Please check if you still get the errors.

Kartik Anand Over a year ago

sorry but i cant upload anything from my office computer!!i'll surely reply with the snapshots when i get back home;

|

chris neilsen · Accepted Answer · 2012-03-06 10:34:26Z

0

Add an Err.Clear after errorhandler:

Also, see the Excel help on Err.Clear which reccomends On Error Resume Next
together with If Err.Number <> 0 Then

This will produce much clearer code

Something like

Sub main()
    On Error Resume Next
    Dim name As String
    Dim marks As Double
    Dim source As Range

    Set source = Sheets("input").Range("$A$2")
    i = 1

    Do Until source.Offset(i, 0) = ""       'iterate through rows
        name = source.Offset(i, 0)
        marks = Find(name)
        If Err.Number <> 0 Then
            Err.Clear
        Else
            ' Your other code for non-error case here

        End If
        i = i + 1
    Loop
End Sub

edited Mar 6, 2012 at 10:34

answered Mar 6, 2012 at 10:28

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

14 Comments

Kartik Anand Over a year ago

Actually i dont want to use resume next because there is a lot of code between errorhandler and find function and will produce overflow if allowed to run.That is why i straight away want to skip to the next iteration.

Kartik Anand Over a year ago

Thank you very much your recommendations,worked like a charm;and just for curiosity why didnt it work with "on error goto errorhandler" ??

chris neilsen Over a year ago

Because you did not have a Err.Clear to reset the error handling

Siddharth Rout Over a year ago

@Chis neilsen: if you put OERN right at the top then it will suppress all errors. For example if you don't have a Sheet("Input") then OERN will simply suppress the error instead of highlighting it. If I go by your method then I will use OERN inside the loop just before marks = Find(name) :)

Siddharth Rout Over a year ago

@chris neilsen: I disagree with the point that "imbedding a error handler inside a loop is a poor design" i.e if you are referring to OERN. There are many scenarios where you have to use OERN inside a loop.

|

Collectives™ on Stack Overflow

VBA error handling not working when function being called generates error

2 Answers 2

10 Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related