application.worksheetfunction
 

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

application.worksheetfunction
 

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"
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

application.worksheetfunction
 

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


"Jim Cone" wrote in message
...
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"
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

application.worksheetfunction
 

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"
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

application.worksheetfunction
 

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

"JP Ronse" 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


"Jim Cone" wrote in message
...
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"
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

application.worksheetfunction
 

Hi Chip,

Thank you very much.


Wkr,

JP


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

application.worksheetfunction
 

Tnx Charles.

Wkr,

JP


"Charles Williams" wrote in message
...
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

"JP Ronse" 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


"Jim Cone" wrote in message
...
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"
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

application.worksheetfunction
 

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"
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


"Jim Cone" wrote in message
...
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"
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

application.worksheetfunction
 

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


"Jim Cone" wrote in message
...
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"
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


"Jim Cone" wrote in message
...
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"
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