How do I get a UDF to return #N/A, as if from =NA()

[Barb Reinhardt]

I have a user defined function and at times I want it to return =NA() in the
cell. How do I do this? I can get it to return a text string, but not a
formula.

Thanks,
Barb Reinhardt

 

[Jacob Skaria]

Try

returnvalue = "=#N/A"

If this post helps click Yes

 

[Jacob Skaria]

Oops..

=NA() only works in worksheet .

If this post helps click Yes

 

[Barb Reinhardt]

I already tried "=NA()" and I get

=NA()

in the cell. It doesn't behave like the formula =NA().

Any other suggestions?

 

[Jacob Skaria]

Try

returnvalue = CVErr(xlErrNA)

If this post helps click Yes

 

[Peter T]

Function foo(v)
' try v = 0 and v = 1
On Error GoTo errH
foo = 1 / v

Err.Raise 12345
Exit Function
errH:
If Err.Number = 11 Then
foo = CVErr(xlErrDiv0)
ElseIf Err.Number = 12345 Then
foo = CVErr(xlErrNA)
Else
foo = CVErr(xlErrValue)
End If
End Function

here are the XlCVError constants

xlErrDiv0
xlErrNA
xlErrName
xlErrNull
xlErrNum
xlErrRef
xlErrValue


Regards,
Peter T

 

[Ron Rosenfeld]

I have a user defined function and at times I want it to return =NA() in the
cell. How do I do this? I can get it to return a text string, but not a
formula.

Thanks,
Barb Reinhardt

A function can only return a value. If you want to set a formula into the
cell, you'll need to use a different method (e.g. Sub).

However, if having your formula return the #NA error (not the string; the
error) is acceptable, then you could set your result equal to CVErr(xlErrNA).

The only potential caveat is that you'd need to use a variant for your result.

e.g:

======================
Function FooBar() As Variant
FooBar = CVErr(xlErrNA)
End Function
====================
--ron