Best practice in qualifying Excel worksheet function invocation

[paul.domaskis]

So I finally got VBA working with VLookup, Worksheet names, and a
lookup table referred to by list name. I had to do the proper
prefixes:

temp = WorksheetFunction.VLookup( ...)
temp = Application.WorksheetFunction.VLookup( ...)

Which of the two are best practice, and why?

Thanks!

 

[Dave Peterson]

There are some worksheet functions where it doesn't really matter (maybe speed
is slightly affected???) if you use:

Application.somefunction()
or
application.worksheetfunction.somefunction()
or
worksheetfunction.somefunction()

But one of the nice things is that if you use worksheetfunction (with or without
the application object), you'll get a hint of what comes next with the VBE's
intellisense and autocomplete.

If you type:
application.worksheetfunction.
(with the dot)
you'll see all the worksheet functions that you can use (autocomplete)

If you type:
application.worksheetfunction.vlookup(
you'll see (not too informative) 4 arguments (arg1, arg2, arg3, arg4)

You won't see that if you use application.vlookup.

But (you didn't ask about this), there is a difference with a few functions on
how errors are treated between:

Application.vlookup() (and application.match())
and
application.vlookup() (and application.worksheetfunction.match())

If you don't use the .worksheetfunction portion, then you can test the returned
results for an error:

Dim res as variant 'could be an error
res = application.vlookup("someval", somerangehere, 2, false)
if iserror(res) then
msgbox "no match"
else
msgbox res
end if

===========
But when you use worksheetfunction.vlookup(), then you'll have to program
against a run time error.

dim res as long 'or string or even variant
on error resume next
res = application.vlookup("someval", somerangehere, 2, false)
if err.number <> 0 then
err.clear
msgbox "no match"
else
msgbox res
end if
on error goto 0

============
Personally, I like using the
if iserror(res) then
version
And I hardly ever (almost never??) use worksheetfunction.

If you continue to hang out in the excel newsgroups, I bet you'll find that the
people who use application.vlookup() are the ones who have been using excel
longer (application.worksheetfunction.vlookup() was added in xl97 (IIRC))--or
they've picked up this nasty <vbg> habit from the longer time users.

 

[paul.domaskis]

My brain was fried yesterday, but I looked at it today. It makes
sense (your preference of being able to test for errors rather than
trap for them).

Thank you kindly for sharing your insightful expertise.

 

[paul.domaskis]

Dave, I tried the iserror function on the variable in which I stuck
the VLookup return value. IsError never becomes true when VLookup
fails to find something. I resorted to using the Err object as
described at http://www.cpearson.com/excel/CallingWorksheetFunctionsInVBA.aspx.

It turns out that if there is an error, the left-hand-side does not
actually get a result.

I suuppose I could always try putting the VLookup expression inside
IsError as an argument, but then I'd have to repeat that expression to
assign to the destination variable when IsError evaluates to False.

Anyway, I'm sure this is motherhood to most, but it feels like I'm
finally starting to "operate" in the VBA environment.

Thanks again for the leads.

 

[Dave Peterson]

I'd guess that you did something wrong if iserror() didn't return an error when
there was no match. But since you didn't share any details, I don't have a
guess.

And using ...worksheetfunction.vlookup() won't return a value if there's not a
match. That was the second example in my earlier post.