Problem with worksheet functions and errors

[NDBC]

I am putting some error handling in my code. I have it working after doing
some searching on here but for my own understanding can someone explain why
this code works when there is an error in the vlookup result

Dim Name As Variant

'Tag scanned into column 1
If Target.Column = 1 Then
Application.ScreenUpdating = False

Name = Application.VLookup(Target.Value, Sheets("ID
List").Range("A3:B65536"), 2, False)
If IsError(Name) Then
Sheets("Running Sheet").Cells(Target.Row, 2) = ""
Else
Sheets("Running Sheet").Cells(Target.Row, 2) = Name
End If


And this code doesn't

Dim Name As Variant

'Tag scanned into column 1
If Target.Column = 1 Then
Application.ScreenUpdating = False

Name = Worksheetfunction.VLookup(Target.Value, Sheets("ID
List").Range("A3:B65536"), 2, False)
If IsError(Name) Then
Sheets("Running Sheet").Cells(Target.Row, 2) = ""
Else
Sheets("Running Sheet").Cells(Target.Row, 2) = Name
End If

They both work fine if there is no error but the second with the
worksheetfunction doesn't handle errors.

Thanks

 

[OssieMac]

Have a look at the following URL; particularly Chip Pearsons answer.

http://www.microsoft.com/communitie...&p=1&tid=809cdfcf-a513-4963-b39a-be27ae5e8c7b

 

[Chip Pearson]

If you use the WorksheetFunction class, errors are handled by throwing
an exception that your code must trap and deal with. E.g.,

Dim V As Long
On Error Resume Next
V = Application.WorksheetFunction.Match(11, Range("A1:A10"), 0)
If Err.Number <> 0 Then
Debug.Print "ERROR", Err.Number, Err.Description
Else
Debug.Print "OK: Result: " & CStr(V)
End If

Without the On Error Resume Next handler, the code will stop running
and enter debug mode if Match fails to find a match.

But if you omit the WorksheetFunction class and an error occurs, the
function will return a Variant of subtype vbError, which you can test
with IsError. The code will continue to execute even if Match throws
an error.

Dim V As Variant
V = Application.Match(111, Range("A1:A10"), 0)
If IsError(V) Then
Debug.Print "error"
Else
Debug.Print "no error"
End If

No On Error handler is necessary. If you do use the WorksheetFunction
class, you can declare the variable as the type (Long, String, etc)
that you expect to return from the successful completion of the
function; in the first example, V is declared as a Long.

If you omit the WorksheetFunction class, you must declare the result
variable as a Variant, as shown in the second example. Otherwise,
you'll get a Type Mismatch (13) error when the Variant vbError subtype
is assigned to an incompatible data type. The only compatible data
type for a vbError is a Variant..

Dim V As Long
V = Application.Match(111, Range("A1:A10"), 0)
If IsError(V) Then
Debug.Print "error"
Else
Debug.Print "no error"
End If

Here, Application.Match throws an error result of type vbError, and
the code attempts to assign that error value to the Long variable V. A
Long cannot accept a vbError type, so you get first the primary error
originating with Match and then a secondary error when assigning the
error result of Match to the Long variable.

Beyond the differences between error handling, I can't think of a
reason to use one approach over the other, but you do need to consider
and code for the different types of error handling, whichever method
you decide to use. Choose one approach and then be consistent with
it.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)