Difference between worksheetfunction.vlookup and app.wf.vloopup

upobDaPlaya used Ask the Experts™
Within VBA what is the difference between the 3 below statements ?  I am told 1 difference is relative to error handling

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
WorksheetFunction.VLookup and Application.WorksheetFunction.VLookup will both cause a runtime error if VLOOKUP cannot find a match. If you use that approach, you must therefore turn error handling off before the lookup and then back on again afterwards.

In contrast, Application.VLookup will return an error value to a Variant, so you can test for the error value after it has been returned. I like the much more compact construction, and am willing to put up with the loss of IntelliSense for the parameters.

BTW, it's the same story with MATCH. So I also prefer to use Application.Match instead of WorksheetFunction.Match.

Dim v As Variant
On Error Resume Next
v = WorksheetFunction.VLookup("ABC Corp", Range("B2:D1000"), 3, False)
On Error GoTo 0
If IsEmpty(v) Then v = "Not found"

v = Application.VLookup("ABC Corp", Range("B2:D1000"), 3, False)
If IsError(v) Then v = "Not found"

Open in new window


Awesome reply !  An answer with a well articulated explanation and a bonus on the information provided on the Match function.
Roy CoxGroup Finance Manager

You can also use the VBA .Find method instead of a Lookup formula or .FindNext which allows multiple results.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial