Thanks for pointing me to chip's web page - great info there.
I'm still pretty green at VBA programming, and this discussion
group is fantastic!

Dave


"JMB" wrote:

chip has some discussion on his site
http://www.cpearson.com/excel/Callin...onsFromVBA.htm

but I've not yet found an explanation for exactly why that happens. it
seems to behave like that for match, hlookup, lookup, and ceiling. I guess
it is something to be cognizant of if the function can return an error (such
as #NA or #NUM).


"Dave" wrote:

When I use the WorksheetFunction.Find, the module always aborts
with "run-time error#1004 cannot get the Find property of the
worksheet function class". Using Application.Find works fine.

Is this true for all of the WorksheetFunction properties?

Dave

"JMB" wrote:

My experience has been when invoking Vlookup in VBA with
WorksheetFunction.VLookup
I always get a run-time error "cannot get the vlookup property of the
worksheetfunction class" any time vlookup evaluates to NA.

But when I use Application.Vlookup, no run time errors.



"Michael Saathoff" wrote:

I am trying to create a Vlookup function with an additional argument to
return if the vlookup function evaluates to #N/A (Below is the code).

It works fine unless the function evaluates to #N/A.

I would greatly appreciate any help,
Michael

Function VLOOKUP_NA(VAL1, Range, OFFSET, TF, NAVALUE)

If WorksheetFunction.IsNA(WorksheetFunction.VLookup(V AL1, Range, OFFSET,
TF)) < False Then
VLOOKUP_NA = NAVALUE
ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(V AL1, Range, OFFSET,
TF)) = False Then
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF)
End If

End Function