application.worksheetfunction

  • Thread starter Thread starter JP Ronse
  • Start date Start date
J

JP Ronse

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
 
The type of error returned is different.

"Application" returns a Variant containing an error which allows the use of IsError...
If IsError(Application.Match...)

"WorksheetFunction" returns a VBA error which requires an error handler...
On Error Resume Next
Application.WorksheetFunction.Match...
If Err.Number <> 0 then
--
Jim Cone
Portland, Oregon USA



"JP Ronse" <[email protected]>
wrote in message
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
 
Hi Jim,

Tnx for the clarification. Can I conclude that both syntaxes are equal,
except of course from the returned error, but decision depends who the
programmer will handle errors?

Any idea which is the fastest way? AFAIK, calling the application or
accessing objects is slow compared e.g. accessing a memory variable. If you
have a lot of errors to process, the VBA way can be much faster then the
application way or do I see this wrong?


Wkr,

JP
 
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)
 
Application.Worksheetfunction seems to be faster for the few Functions I
have tested... but may not be faster for all.

regards
Charles Williams
Decision Models
 
Hi Chip,

Thank you very much.


Wkr,

JP


Chip Pearson said:
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)
Click to expand...
 
Tnx Charles.

Wkr,

JP


Charles Williams said:
Application.Worksheetfunction seems to be faster for the few Functions I
have tested... but may not be faster for all.

regards
Charles Williams
Decision Models
Click to expand...
 
The choice would usually be determined by the type of error trapping you wanted to do.
Application.WorksheetFunction.Match takes about 1/2 the time of Application.Match,
however if you are only calling the function a few times then you couldn't measure
the time difference.
--
Jim Cone
Portland, Oregon USA




"JP Ronse" <[email protected]>
wrote in message
Hi Jim,
Tnx for the clarification. Can I conclude that both syntaxes are equal,
except of course from the returned error, but decision depends who the
programmer will handle errors?

Any idea which is the fastest way? AFAIK, calling the application or
accessing objects is slow compared e.g. accessing a memory variable. If you
have a lot of errors to process, the VBA way can be much faster then the
application way or do I see this wrong?
Wkr,
JP
 
Hi Jim,

I understand.

If you know that I have written some code to generate a monthly report based
on a CSV dump of about 40MB, over 20.000 rows and x columns and only about
1000 rows are valuable input, each gain in runtime is most appreciated.

Tnx!

Wkr,
JP
 
Back
Top