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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

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

In article ,
says...
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.

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!

--

Vasant




"Fred Smith" wrote in message
...
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

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



"Fred Smith" wrote in message
...
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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!

--

Vasant




"Fred Smith" wrote in message
...
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Thanks, Chip. Exactly the information I needed.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Chip Pearson" wrote in message
...
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



"Fred Smith" wrote in message
...
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail