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

's Computer Specifications

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

's Computer Specifications

Try

returnvalue = "=#N/A"

If this post helps click Yes
---------------
Jacob Skaria

"Barb Reinhardt" wrote:

> 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

's Computer Specifications

Oops..

=NA() only works in worksheet .

If this post helps click Yes
---------------
Jacob Skaria

"Jacob Skaria" wrote:

> Try
>
> returnvalue = "=#N/A"
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Barb Reinhardt" wrote:
>
> > 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

's Computer Specifications

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

=NA()

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

Any other suggestions?

"Jacob Skaria" wrote:

> Oops..
>
> =NA() only works in worksheet .
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Jacob Skaria" wrote:
>
> > Try
> >
> > returnvalue = "=#N/A"
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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

's Computer Specifications

Try

returnvalue = CVErr(xlErrNA)

If this post helps click Yes
---------------
Jacob Skaria

"Barb Reinhardt" wrote:

> I already tried "=NA()" and I get
>
> =NA()
>
> in the cell. It doesn't behave like the formula =NA().
>
> Any other suggestions?
>
> "Jacob Skaria" wrote:
>
> > Oops..
> >
> > =NA() only works in worksheet .
> >
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> >
> >
> > "Jacob Skaria" wrote:
> >
> > > Try
> > >
> > > returnvalue = "=#N/A"
> > >
> > > If this post helps click Yes
> > > ---------------
> > > Jacob Skaria
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > 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

's Computer Specifications

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

"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:F7624D0C-AA8E-4106-A66E-(E-Mail Removed)...
>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

's Computer Specifications

On Wed, 10 Jun 2009 04:43:01 -0700, Barb Reinhardt
<(E-Mail Removed)> wrote:

>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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Search and replace hard-line-return character, not carriage return	=?Utf-8?B?VHlsZXIgVA==?=	Microsoft Word Document Management	2	16th Aug 2006 09:11 PM
Serialize a class having property that return object as return type	surindersaini@gmail.com	Microsoft C# .NET	4	24th Oct 2005 03:23 PM
VBA to apply a Soft Return (or Hard Return) at end of wrapped line	Dennis	Microsoft Word Document Management	7	14th May 2005 07:01 AM
Userenv 1000. Windows cannot determine the user or computer name. Return value (<return error code>).	Ingmar	Microsoft Windows 2000 Networking	1	18th Nov 2003 05:08 PM
Userenv 1000. Windows cannot determine the user or computer name. Return value (<return error code>).	Ingmar	Microsoft Windows 2000 Group Policy	1	18th Nov 2003 05:08 PM