referencing worksheet functions

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

Application.<function_name>
Application.WorksheetFunction.<function_name>

I've seen both syntax's offered.
Why choose one over the other?
 
David,

Actually, you can use Application, Worksheetfunction, or
Application.WorksheetFunction.

If you use Application, you don't get intellisense, with WorksheetFunction
you do.

However, I think you will find problems with some fiunctions in
WorksheetFunction. Not sure all of which, but be aware when you use it, and
then just use Application if this occurs.

Bottom line, code with WorksheetFunction, and test and run with Application
maybe?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
David,

The primary difference is how errors are handled. For example,
the Match function causes and error if the lookup value is not
found. If you use the syntax:

Dim Result As Variant
Result = Application.Match(123,Range("A1:A10"),0)

and 123 is not found, Result will contain an error value that you
can test with the IsError function. No special error handling
with On Error is required.

If you use the syntax,

Dim Result As Variant
Result =
Application.WorksheetFunction.Match(123,Range("A1:A10"),0)

and 123 is not found, you'll get a run time error that has to be
handled with an On Error handler.


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