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

  • Thread starter Thread starter Barb Reinhardt
  • Start date Start date
B

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
 
I already tried "=NA()" and I get

=NA()

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

Any other suggestions?
 
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
 
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
Click to expand...

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
 
Back
Top