what the difference between...

Worksheetfunction.Vlookup()

and...

Application.Vlookup()

???


What the benefit of using one over the other???
Are there special circumstance or situation where you might wanna use one of
them, but in other cases it is preferable to use the other?


TIA,

One of the differences is the way they behave:

Application.vlookup returns an error that you can check:

dim Res as variant 'could return an error
res = application.vlookup(....)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

Application.worksheetfunction.vlookup raises a trappable error that you have to
catch:

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number < 0 then
msgbox "no match"
else
msgbox res
end if
on error goto 0

(application.match() and application.worksheetfunction.match() behave the same
way.)

Personally, I find using the application.vlookup() syntax easier to read. But
it's personal preference.

Charlotte E wrote:

what the difference between...

Worksheetfunction.Vlookup()

and...

Application.Vlookup()

???

What the benefit of using one over the other???
Are there special circumstance or situation where you might wanna use one of
them, but in other cases it is preferable to use the other?

TIA,

--

Dave Peterson

They are the same when you are in excel. If you opened an excel workbook
from another office product you would need the application or if you used
GetObject to get the workbook.


If you are in VBA it is more efficient to use FIND

set c = Columns("A").Find(what:="abc",lookin:=xlvalues,loo kat:=xlwhole)
if Not c is nothing then

end if

Find is better becaue you don't get an error when the item doesn't exist.
You get Nothing which you can test for.

"Charlotte E" wrote:

what the difference between...

Worksheetfunction.Vlookup()

and...

Application.Vlookup()

???


What the benefit of using one over the other???
Are there special circumstance or situation where you might wanna use one of
them, but in other cases it is preferable to use the other?


TIA,