Simple Excel VBA Question

[Guest]

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(VAL1, Range, OFFSET,
TF)) <> False Then
VLOOKUP_NA = NAVALUE
ElseIf WorksheetFunction.IsNA(WorksheetFunction.VLookup(VAL1, Range, OFFSET,
TF)) = False Then
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF)
End If

End Function

 

[JE McGimpsey]

You might consider using a wrapper function for VLOOKUP instead - that
way it can be flexible enough to use with HLOOKUP, or MATCH, too:

For instance:

=IfNA(VLOOKUP(A1,J:K,2,FALSE),"NA VALUE")

or just

=IFNA(MATCH(A1,J:J,FALSE))


Here's one implementation:

Public Function IfNA(ByRef vTest As Variant, _
Optional ByRef vDefault As Variant = vbNullString) As Variant
If vTest = CVErr(xlErrNA) Then
IfNA = vDefault
Else
IfNA = vTest
End If
End Function

 

[Bernd P]

Hello,

In case of an error your function does not get further within the
isna() call, I think.

I suggest:
Function VLOOKUP_NA(VAL1, Range, OFFSET, TF, NAVALUE)

On Error Resume Next
VLOOKUP_NA = WorksheetFunction.VLookup(VAL1, Range, OFFSET, TF)
If Err.Number <> 0 Then VLOOKUP_NA = NAVALUE

End Function

Regards,
Bernd

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

chip has some discussion on his site
http://www.cpearson.com/excel/CallingWorksheetFunctionsFromVBA.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).

 

[Guest]

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

 

[Guest]

Some additional links that you may find have helpful information (and/or some
nifty downloads)

http://www.decisionmodels.com/
http://erlandsendata.no/english/
http://www.contextures.com/
http://j-walk.com/
http://vb.mvps.org/
http://mcgimpsey.com/
http://xcell05.free.fr/
http://xldynamic.com/
http://mvps.org (look for the excel link on the right).