Worksheetfunction

[Fred Smith]

What's the difference between Application.WorksheetFunction.Vlookup and
Application.Vlookup?

As far as I can tell they both work. However, one difference I've found is
that when the Vlookup fails, Application.WorksheetFunction.Vlookup returns
nothing, but Application.Vlookup returns N/A.

Is there any need to use WorksheetFunction? It would save a lot of typing if
I didn't have to use it.

 

[Tushar Mehta]

If I recall correctly, when MS introduced Worksheetfunction the
accompanying documentation indicated that the other (direct) way would
be made obsolete at some point in the future.

As far as "a lot of typing" goes, if you use VBE's intellisense
capability it is three or four keystrokes -- a bargain if MS actually
delivers on its promise and makes use of Worksheetfunction mandatory.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

 

[Vasant Nanavati]

1. "Application" will not give you function names in the Intellisense
dropdown, nor will it give you an argument list for the function you type in
manually.

2. "WorksheetFunction" will do both.

2. It is not necessary to preface "WorksheetFunction" with "Application."

I hope I've saved you a few keystrokes!

 

[Chip Pearson]

Fred,

It depends on how you want error handling to occur, and by
extension, how you have declared the result variable.

If you omit the Worksheet function portion of the syntax, and
VLOOKUP fails, an error subtype variant is returned from the
function. If the result variable is not defined as a Variant,
you'll receive an error 13 - Type Mismatch (error trapping code
should be in place to handle this). If the return variable is in
fact declared as a Variant, it will contain an error value, and
you can test for this with IsError.

If you include the Worksheet function portion of the syntax, and
VLOOKUP fails, an error (1004) is immediately raised, and you'll
need error trapping code to handle this error.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

What's the difference between

Application.WorksheetFunction.Vlookup and

Application.Vlookup?

As far as I can tell they both work. However, one difference I've found is
that when the Vlookup fails,

Application.WorksheetFunction.Vlookup returns

 

[Fred Smith]

Thanks for the tip on the unnecessary "Application".

As for Intellisense, as a touch typist, I find it very distracting. It
certainly doesn't save me any time (I can type faster than the time it takes
to figure out what delimiter or cursor action will get VBE to select the
choice). However, it is useful in seeing a list of valid choices, except
there are too many exceptions to its lists.

 

[Fred Smith]

Thanks, Chip. Exactly the information I needed.