Excel Worksheet Functions Error Handling - Request for opinion

I just learned something today on this post about the dirrerence between using Excel functions qualified by Application and those qualified by Application.WorksheetFunction. In the first case, the function will return with values that include NA (if you declare the receiving vairbale Variant), and in the second case, a VBA error is thrown. Error handling is different is each case. I was wondering what the opinion of others on this topic was. Which style of error handling should be used?

My opinion is to use Application.WorksheetFunction as the error handling is consistent with other VBA object error handling. I would use Application only if for some reason I wanted to allow an NA value to be processed. and subsequently stored in a cell.

Using the example of a MATCH - I would use Application, Variant and test based on IsNumeric

Opinions vary, and I don't know how many other functions it applies to other than Match. The purer approach would be to use consistent error handling, but I confess that returning an error to the assigned variable is a lot more convenient, and I use it frequently. I have never seen it documented other than anecdotally, so I reckon the behavior could be changed at will.

See Chip's page here, and MSKB here.

Chip's link was informative, though I'd expect lookups to have the same behaviors as Match. I didn't get any illumination for the MSKB article, unless I missed something.

At http://www.officekb.com/Uwe/Forum.as...-Documentation, Tushar Mehta says,

Also, and AFAIK this applies only to Match, MS for some strange reason (might even be a bug or an oversight) implemented the 'no match found' handling of Application.Match and Application.Worksheetfunction.Match differently. Don't ask me why. It did. And, any number of people have written code that exploits the difference.

Actually, this was the quote I was looking for, from http://www.dailydoseofexcel.com/arch...ction-method/:

Tushar Mehta says:
March 18, 2005 at 11:23 pm
There’s a certain paradox between this topic and the rant in ‘How to Spread Bad Habits’ since that is exactly what recommending people drop use of the worksheetfunction property does.

When Worksheetfunction was introduced (97?) the documentation indicated it would be the future way to access all XL functions made available to VBA. The old method would be supported for existing functions.

Because of MS’s lack of improvements to XL over the last few versions and now its commitment to dumping VB(A) I doubt there will be any new XL functions made available to VBA. So, luckily, there will be no negative consequence to propagating what should be categorized as a very bad practice.

And, yes, I have been aware of the ‘benefit’ of not using WorksheetFunction but, personally, I find the arguments very dubious. It isn’t like people have a rational aversion to the use of On Error. So, why do people find a ‘benefit’ to not using it in the context of Worksheetfunction.Match?

You: "I have never seen it documented other than anecdotally, so I reckon the behavior could be changed at will"

MSKB article: "You can use most built-in worksheet functions in a macro by calling the function as a method of the Application object or the WorksheetFunction object"



Or did I misunderstand what you meant?

And for once, I disagree with Tushar.

I dropped a note on the MSKB page suggesting that they explain the difference in error handling there. Looks like they are loosing focus on this topic since they may be dumping VBA (?)

MSKB article: "You can use most built-in worksheet functions in a macro by calling the function as a method of the Application object or the WorksheetFunction object"

Sorry, my point was that it gives no hint that the behavior is different one way than the other, and I've never seen any MS documentation that does (which isn't to say it isn't out there).

And for once, I disagree with Tushar.

I don't disagree with his point, it's just one of those things that's too convenient to pass up.

Originally Posted by blane245

since they may be dumping VBA (?)

that won't be happening any time soon.

I don't agree with the implication that not believing MS documentation is bad practice.

Originally Posted by shg

Sorry, my point was that it gives no hint that the behavior is different one way than the other, and I've never seen any MS documentation that does (which isn't to say it isn't out there).

As best I recall, the last time I saw them use it in a code sample in the KB, they got it wrong (used IsError with Worksheetfunction).

I don't agree with the implication that not believing MS documentation is bad practice.

There's something we agree on. Like this example for the Find method from Help:

Please Login or Register  to view this content.

That line needs some error handling.

Yeah, that one still annoys me.

Originally Posted by shg

That line needs some error handling.

Is that because of the compund If and not knowing which test will be checked first? Ada has a nice construct - And Then - to prevent such problems.

They both always get checked in VBA.

Since the code inside the loop is not modifying the contents of the found cell, the value will always be found, and the Not Is Nothing is unnecessary.

If the code were, e.g., clearing the found cell in the loop, the value would eventually not be found. Not is Nothing would be happy, but c.Address would raise an error, since Nothing has no address.

Those two clauses can never be conjoined like that.

gotcha - but we have wander off into water cooler land. I'd like to get back on topic.

Are there any more opinions about the different syles of error handling for worksheet functions? Seems like folks prefer the error handling style of Application over Application.WorksheetFunction.