How can I avoid error in VBA procedure trying to use Excel function VLOOKUP
if lookup value is missing?

wrap th ecall in a function

so

sub MyMainSProc()
....
....
x = application.worksheetsfunction.vlookup(x,source,n, false)
....
End Sub

becomes

sub MyMainSProc()
....
....
x = SafeLookup(x,source,n,false)
....
End Sub

Function SafeLookup(what,where,which).
on error resume next
SafeLookup = application.worksheetsfunction.vlookup(what,where, which,false)
on error goto 0
End Sub


Note: you can of course use the ON ERROR RESUME NEXT in your main
procedurre, but this may bugger up your error handling. Writing a "safe"
function to trap a function type error is much neater & saves a lot of
problems.

Patrick Molloy
Microsoft Excel MVP

"hobbyist" wrote:

How can I avoid error in VBA procedure trying to use Excel function VLOOKUP
if lookup value is missing?

don't use WorksheetFunction.Vlookup or
Application.WorksheetFunction.Vlookup

use Application.Vlookup

then you can check not found with iserror

Dim res as Variant

res = Application.Vlookup( args)
if iserror(res) then
msgbox "No match"
else
msgbox "Value returned is " & res
End if

--
Regards,
Tom Ogilvy

"hobbyist" wrote in message
...
How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?

Just reading the post, thanks for this tip! Had not considered wrapping
these problematic functions in my own user functions as easier way of dealing
with error conditions in the built-in functions, but looks like it has many
advantages over trying to handle the error in the main code.

"Patrick Molloy" wrote:

wrap th ecall in a function

so

sub MyMainSProc()
...
...
x = application.worksheetsfunction.vlookup(x,source,n, false)
...
End Sub

becomes

sub MyMainSProc()
...
...
x = SafeLookup(x,source,n,false)
...
End Sub

Function SafeLookup(what,where,which).
on error resume next
SafeLookup = application.worksheetsfunction.vlookup(what,where, which,false)
on error goto 0
End Sub


Note: you can of course use the ON ERROR RESUME NEXT in your main
procedurre, but this may bugger up your error handling. Writing a "safe"
function to trap a function type error is much neater & saves a lot of
problems.

Patrick Molloy
Microsoft Excel MVP

"hobbyist" wrote:

How can I avoid error in VBA procedure trying to use Excel function VLOOKUP
if lookup value is missing?

Here is an example way

On Error Resume Next
ans = Application.VLookup("Val", Range("A1:H10"), 2, False)
On Error GoTo 0
If IsError(ans) Then
MsgBox "error"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hobbyist" wrote in message
...
How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?

Unless you have some other reason for having it,
You don't need On Error Resume Next as this statement does not raise a
trappable error.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Here is an example way

On Error Resume Next
ans = Application.VLookup("Val", Range("A1:H10"), 2, False)
On Error GoTo 0
If IsError(ans) Then
MsgBox "error"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hobbyist" wrote in message
...
How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?

If your going to ignore the error, then why bother with error trapping.
Just omit worksheetfunction and use iserror. Or reinvent the wheel if you
prefer. Users choice I guess.

--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
Just reading the post, thanks for this tip! Had not considered wrapping
these problematic functions in my own user functions as easier way of
dealing
with error conditions in the built-in functions, but looks like it has
many
advantages over trying to handle the error in the main code.

"Patrick Molloy" wrote:

wrap th ecall in a function

so

sub MyMainSProc()
...
...
x = application.worksheetsfunction.vlookup(x,source,n, false)
...
End Sub

becomes

sub MyMainSProc()
...
...
x = SafeLookup(x,source,n,false)
...
End Sub

Function SafeLookup(what,where,which).
on error resume next
SafeLookup =
application.worksheetsfunction.vlookup(what,where, which,false)
on error goto 0
End Sub


Note: you can of course use the ON ERROR RESUME NEXT in your main
procedurre, but this may bugger up your error handling. Writing a "safe"
function to trap a function type error is much neater & saves a lot of
problems.

Patrick Molloy
Microsoft Excel MVP

"hobbyist" wrote:

How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?

not wanting to cause a long discussion but sveral functions sunch as
Application.WorksheetFunction.VLookup()
Application.WorksheetFunction.HLookup()
Application.WorksheetFunction.Match()
DO indeed raise trappable errors.

If one has a long procedure with error handling, then I like to wrap these
in my own functions since I know th error will be a "not found" ... otherwise
we need to alter the error trapping in the proc that uses the function.



Patrick



"Tom Ogilvy" wrote:

Unless you have some other reason for having it,
You don't need On Error Resume Next as this statement does not raise a
trappable error.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Here is an example way

On Error Resume Next
ans = Application.VLookup("Val", Range("A1:H10"), 2, False)
On Error GoTo 0
If IsError(ans) Then
MsgBox "error"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hobbyist" wrote in message
...
How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?

What you say is true but has little to do with what I posted.

Application.VLookup()
Application.HLookup()
Application.Match()

Don't raise trappable errors and don't need to be put into separate
functions as the results can be tested inline.
Note he (Bob) wrote:

ans = Application.VLookup("Val", Range("A1:H10"), 2, False)

THIS DOES NOT RAISE A TRAPPABLE ERROR WHEN THE VALUE IS NOT FOUND!!!! unless
ans is dimensioned as other than variant which would be stupid indeed.

I don't want to raise a long discussion either, but in xl97 and xl2000 at
least, the functions are sometimes Flaky when used with Worksheetfunction as
a qualifier besides the fact that they raise a trappable error when the item
is not found when it is used.

--
Regards,
Tom Ogilvy


"Patrick Molloy" wrote in message
...
not wanting to cause a long discussion but sveral functions sunch as
Application.WorksheetFunction.VLookup()
Application.WorksheetFunction.HLookup()
Application.WorksheetFunction.Match()
DO indeed raise trappable errors.

If one has a long procedure with error handling, then I like to wrap these
in my own functions since I know th error will be a "not found" ...
otherwise
we need to alter the error trapping in the proc that uses the function.



Patrick



"Tom Ogilvy" wrote:

Unless you have some other reason for having it,
You don't need On Error Resume Next as this statement does not raise a
trappable error.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
Here is an example way

On Error Resume Next
ans = Application.VLookup("Val", Range("A1:H10"), 2, False)
On Error GoTo 0
If IsError(ans) Then
MsgBox "error"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"hobbyist" wrote in message
...
How can I avoid error in VBA procedure trying to use Excel function
VLOOKUP
if lookup value is missing?