The difference between the two approaches is how errors are handled.
For example, if you include the WorksheetFunction reference and an
error occurs, VBA will raise a runtime error that you must trap and
deal with using standard error handling techniques:
Dim V As Variant
On Error Resume Next
V = Application.WorksheetFunction.VLookup(1, Range("A1:B10"), 2,
False)
If Err.Number <> 0 Then
Debug.Print "not found"
Else
Debug.Print "found: " & V
End If
If you omit the WorksheetFunction reference, the function returns a
Variant of subtype Error that you test with IsError. No runtime error
is raised. E.g.,
Dim V As Variant
V = Application.VLookup(1, Range("A1:B10"), 2, False)
If IsError(V) = True Then
Debug.Print "not found"
Else
Debug.Print "found"
End If
In the first approach, you can declare the variable V to be the type
that should be returned by the VLOOKUP (e.g., a Long or a String). In
the second approach, V must be declared as a Variant.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Sun, 23 Aug 2009 14:24:47 +0200, "JP Ronse" <(E-Mail Removed)>
wrote:
>Hi All,
>
>I see that most of the MVP's are using:
>
>x = application.worksheetfunction.<function(arguments)>
>
>while
>
>x = application.<function(arguments)>
>
>is also working.
>
>Is there a reason to use worksheetfunction, except that the syntax of the
>function is given?
>
>Wkr,
>
>
>JP
>
>
Similar Threads
