IS functions

[David Unger]

Hello,

Sorry to raise this issue again, but I'm still having trouble using the IS
functions, here's the latest manifestation (running Excel 97)

This formula works perfectly fine in my spreadsheet:

=IF( ISNA(MATCH(0,A1:H1,0)),"Zero NOT found","Zero found")

However, a similar statement in VBA code generates an error message if '0'
is not present (e.g., N/A), but works if '0' is present.

Set rng = Range("A1:H1")
If WorksheetFunction.IsNA(WorksheetFunction.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

Here's the error message:

Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class.

Am I doing something wrong - can't seem to spot it? Appreciate any
suggestions.

Thanks,

Dave

 

[Myrna Larson]

This is a bug. Change Worksheetfunction.Match to just Match.

Also, you don't need to use the worksheet function ISNA function here. You can
write it as

If IsError(Match(0, Rng, 0)) Then

 

[Dave Unger]

Hi Myrna,

Using the IsError function doesn't seem to change anything.

If I change Worksheetfunction.Match to just Match I get an error
message: "Compile Error - Sub or Function not defined."

Can't seem to win!

Dave

 

[Bob Phillips]

I think Myrna means change Worksheetfuunction to Application

Set rng = Range("A1:H1")
If IsError(Application.Match(0, rng, 0)) Then
MsgBox "Zero NOT found": Exit Sub
Else
MsgBox "Zero found"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

see response in programming.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I agree with Myrna as to using Application.Match instead of
Worksheetfunction Match, and iserror instead of IsNa, but if you want to use
worksheetfunction, you have to trap the error you complain about - this is
how worksheetfunction.match signals that a match is not found

Sub ABCD()
Dim rng As Range
Dim res As Long
Set rng = Range("A1:A10")
res = -1
On Error Resume Next
res = WorksheetFunction.Match(0, rng, 0)
On Error GoTo 0
If res = -1 Then
MsgBox "Zero was Not found"
Exit Sub
Else
MsgBox "Zero was found"
End If

End Sub

 

[Dave Unger]

Tom,

I think you've solved it for me - if I use Application.Match it works
as I intended! I guess what confused me is using Match without the
Application in front of it generates an error message, for some reason
my VBA does not recognize Match by itself. And for some reason, when I
type Application., Match does NOT appear on the pop-up list. Go
figure! Anyway, now that I know about this (bug?) I can carry on.
Thanks ever so much,

Dave

 

[Dave Unger]

Hi Bob,

You're right, see my reply to Tom - live and learn I guess. Thanks for
your help,

Dave

 

[Myrna Larson]

Maybe I misread your original post. I thought you had

Application.WorksheetFunction.Match

That must be changed to

Application.Match

 

[Myrna Larson]

Match without the Application in front of it generates an error message, for
some reason my VBA does not recognize Match by itself

VBA doesn't recognize ANY worksheet functions "by itself". You have to tell it
where to find the function by preceding it by Application or
Application.WorksheetFunction.

And for some reason, when I type Application., Match does NOT appear on the

pop-up list.

That's because they want you to use the Application.Worksheetfunction syntax.
The fact that MATCH doesn't always work with the new syntax is a bug that they
didn't discover and/or fix.

 

[Dave Peterson]

You're actually using a worksheet function in VBA.
(like: =match(a1,b:b,0) in a cell)

If you had typed:
application.worksheetfunction.
You'd get Match to show up in that autocomplete list.

But, as you've seen, application.worksheetfunction.match and application.match
behave differently when it comes to errors.

I'm willing to live with the lack of popup to get the behavior I like.

 

[Dave Unger]

Hi Myrna,

Let's see if I'm understanding you right, correct me if I'm wrong.

CountA is a worksheetfunction, all the following statements work:

cnt = Application.WorksheetFunction.CountA(rng)
cnt = WorksheetFunction.CountA(rng)
cnt = Application.CountA(rng)

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Dave

 

[Bob Phillips]

Because MATCH is also a worksheet function, I would assume it should
work in exactly the same way, but because of a bug in Excel 97, only
Application.MATCH syntax will work.

To state it another way, Worksheetfunction.MATCH and Application.MATCH
are really the same statement, but only the latter works.

I don't know how I would know this unless someone told me or I stumbled
onto it quite by accident.

Isn't that generally the way with bugs?

 

[Dave Peterson]

To state it another way, Worksheetfunction.MATCH and Application.MATCH

are really the same statement, but only the latter works.

Isn't quite true.

Both of them work fine. But they each handle errors (#n/a in the worksheet)
differently.

worksheetfunction.match raises a runtime error (1004) that you can check one way
and application.match returns an error that you can check another way.

I personally like the application.match() syntax. I just find it simpler.

 

[Tom Ogilvy]

In xl97 at least, worksheetFunction.Match didn't always work fine.
Sometimes it did and sometimes it didn't. Or to summarize, it usually
worked, but wasn't reliable. That was Myrna's point. On the other hand,
never had a problem with Application.Match. These are my own experiences
and Myrna apparently shares those. Your comments on error handling are, of
course, correct.

 

[Dave Peterson]

Thanks for the correction. I didn't recall this problem in xl97.

 

[Dave Unger]

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

 

[Dave Unger]

Tom,

"Experience" seems to be the key word, thanks for sharing it.

Dave

 

[Dave Peterson]

I'm gonna jump in (even with my bad memory of xl97!). Tom or Myrna can yell,
er, correct me where I'm wrong.

Both application.match and worksheetfunction.match call the same excel function.

But I don't think Myrna meant that the bug was the different ways each handles
errors. (It seems inconsistent to me--but not quite a bug.)

I think Myrna (and Tom) meant the bug was that worksheetfunction.match didn't
return the correct value all the time in xl97.

And that's a lot more serious.

application.vlookup() and worksheetfunction.vlookup() have the same error
handling difference. I don't know if xl97 had a similar bug.

As for me, I'd dump the worksheetfunction version all the time. xl2003 still
supports application.functionnamehere().

I think it makes life much simpler.

I don't think the autocomplete (arg1, arg2, arg3) helps in any way. If I need
help, I'll swap back to excel and look at help.

(Here's hoping I'm closer to correct!)

 

[Tom Ogilvy]

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.