the WorksheetFunction object wasn't introduced until xl97. Obviously, since
VBA was introduced in xl5, you still had the means to use worksheet
functions in VBA. This was through the Application Object. This capability
has been preserved for compatibility.

Any function that can return an error could be handled with the iserror
check using Application as the qualifier. For example, taking the average
of a blank range causes an error

? application.Average(Range("A1:A10"))
Error 2007
? iserror(application.Average(Range("A1:A10")))
True

worksheetFunction.Average(Range("A1:A10"))

raises a trappable error, the 1004 error. This error is produced by the
worksheetfunction object.

the bug Myrna talked about was that Match and the Lookup functions didn't
always seem to work in xl97 using worksheetfunction as a qualifier. the
difference in error handling is by design and is not a bug. Different
object qualifiers, different code execution path, different behavior.

--
Regards,
Tom Ogilvy

"Dave Unger" wrote in message
oups.com...
Thanks Dave,

Allow me to bounce this around a bit more, just to see if I've "got it"
- again, correct me where I'm wrong.

The full syntax for a worksheet function (such as MATCH) is
Application.WorksheetFunction.Match

VBA allows abbreviations such as WorksheetFunction.Match and
Application.Match, so these are 3 equivalent ways of calling the same
worksheet function. Am I correct up to this point?

Now, even though I'm calling the exact same function in all 3 cases, I
should be aware of a "bug", which causes WorksheetFunction.Match to
handles errors differently than Application.Match - which makes it seem
like you're calling 2 different functions, but you're not.

Is it possible other worksheet functions may also exhibit differences,
depending on the method used to call them?

thanks,

Dave Unger