How to report an error from an Excel UDF with a comment?

[Jiri Pik]

I can return an error using ExcelDna.Integration.ExcelError.ExcelErrorNA.

 

However, I would like to return an error with a message, e.g. saying that “The parameter has to be a positive number”. How to do it? I can certainly return a text “#NA Parameter has to be a positive number”, but Excel’s function IsError would not recognize it.

 

Can anybody point me in the right direction and possibly provide a code snippet?

 

Thanks.

 

 

[Naju Mancheril]

No easy solution to this. Excel thinks of errors as codes and not as exception-style objects with a message component.

You're only option may be to return "#NA some message" and create an IsErrorEx UDF for your users.

We once wrote an IsErrorEx function to detect the different error messages that the Bloomberg addin returns. It worked well once we told everyone about it.

[Patrick O'Beirne]

Another approach is to return a two-element array where the second element is the status.
But if someone enters this as a normal UDF and not as an array then they will never see the second element.
Another is as already said to return a text string with the  #error message!  and hope they see that.

On 04/02/2013 13:09, Malek wrote:

Hi,

I'd the same issue and the best solution I've found was to return an Error, for instance #NA,  and add the error description as  a comment to a cell.

You could have a look to the enclosed sample (which could be loaded in any Excel version) and look into  Add function for more details (Indeed, if you try to call Add function from Excel by missing an argument you will get #GETTING_DATA as error with an error description as comment).

Best,
Malek

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jiri Pik]

Thank you all for all your help!

 

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Malek
Sent: 04 February 2013 14:10
To: exce...@googlegroups.com
Cc: n...@alumni.cmu.edu
Subject: Re: [ExcelDna] How to report an error from an Excel UDF with a comment?

 

Hi,

I'd the same issue and the best solution I've found was to return an Error, for instance #NA,  and add the error description as  a comment to a cell.

You could have a look to the enclosed sample (which could be loaded in any Excel version) and look into  Add function for more details (Indeed, if you try to call Add function from Excel by missing an argument you will get #GETTING_DATA as error with an error description as comment).

Best,
Malek

Le dimanche 3 février 2013 14:54:36 UTC+1, ngm a écrit :

--

[Benoît PATRA]

Hi,

I do have the same problem. I'd like to investigate more the solution from Malek however the download link's dead.

Do you know which Add function Malek is referring to in his post? Would it be compatible with a non macro-type UDF?

[ngm]

Please see attached.

[Benoît PATRA]

Thank you very much.

On Tuesday, February 18, 2014 6:27:29 PM UTC+1, ngm wrote:

Please see attached.