WorksheetFunction

[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

[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

"Stuart" <kff...@yytd.gft> wrote in message
news:boubv8$kec$1...@news6.svr.pol.co.uk...

[Don Guillett]

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

--
Don Guillett
SalesAid Software
don...@281.com

"Stuart" <kff...@yytd.gft> wrote in message
news:boubv8$kec$1...@news6.svr.pol.co.uk...

[Stuart]

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

stuart

Chip Pearson <ch...@cpearson.com> wrote in message
news:OHo#t0WqDH...@TK2MSFTNGP10.phx.gbl...