Returning #N/A and other errors from UDF

Oct 28, 2011 at 5:07 PM

Hello

I would like to return errors from a Excel-DNA UDF contained in a CSharp DLL.  I have googled quite a bit and it looks as though you can do this by returning an ErrorWrapper.  However, I always get a #VALUE come out in Excel 2010 rather than the #N/A or other error I want.  Test code snippet below.

Any ideas?

Kind Regards
Jon

[ExcelFunction(Description = "Gets Error", Category = "Testing")]
public static object GetError()
{
     return new ErrorWrapper((int)XLCVError.xlErrNA);
}

 

Coordinator
Oct 28, 2011 at 7:50 PM
Edited Oct 28, 2011 at 7:51 PM

Hi Jon,

The ErrorWrapper stuff is useful in the RTD server context (and I think for functions in Automation Add-Ins).  

But for normal functions in Excel-DNA, you'd use the ExcelError enum type like this:

 

	public static object GetValueError()
	{	
		return ExcelError.ExcelErrorValue;
	}

	public static object GetReferenceError()
	{
		return ExcelError.ExcelErrorRef;
	}

 

... and similar for the other Excel error types.

Any unhandled exception in your function will also be caught, and Excel-DNA will return #VALUE to Excel.

Regards,

Govert

Oct 28, 2011 at 8:00 PM

Hi Govert

That works like a charm.

Many Thanks
Jon

Feb 9, 2014 at 6:20 AM
if your function returns an int, how would you do this?
return (int)ExcelError.ExcelErrorRef;
seems to just return the enum value.
Coordinator
Feb 9, 2014 at 1:51 PM
If your function returns an int, you can't return anything but an int. You'll have to change the return type to object if you want to return other types of things, like the error values shown in this discussion.

-Govert