Excel VBA

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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.