WorksheetFunction

[Chip Pearson]

Stuart,

The primary difference between using or not using WorksheetFunction is how
errors are handled. When you use WorksheetFunction and an error occurs, a
trappable error is raised by VBA. For example,

Dim V As Variant
V = Application.WorksheetFunction.Sum(1, "A", 3)

will raise an error 1004, and this can be trapped by an On Error statement.

When you do not use WorksheetFunction, the result of the function is a error
typed variant , but code does not stop executing and no trappable error is
raised. For example,

Dim V As Variant
V = Application.Sum(1, "A", 3)
Debug.Print V

(Note that if V is declared as a numeric type, you'll get an error 13, type
mismatch, because VBA can't put an error typed value in a numeric variable.)

Whether you do or do not use WorksheetFunction is a matter of personal
coding style. I tend to use it because I rarely use the Variant variable
type, and I like the intellisense support when typing.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Don Guillett]

AFAIK
application by itself works fine for all of the later versions

 

[Stuart]

I have noticed in the advice given on this grouop that when a
worksheetfunction is used the syntax is to include
"Application.Worksheetfunction" before the relative function. For along time
now I have utilised worksheet functions by simply adding "application"
before the function. What are the benifits in using the longer statement?


Stuart

 

[Stuart]

Many thanks for your detailed answer, definately another string added to my
bow!

stuart