Error management in a function

[virginie]

Hi,

I'm working on a C# Quant library since a long time now and I use
Excel DNA to use it in Excel. My problem is now I need to start a
Error management in my library as it's starting to be very complex.

My problem is if I use a function which return a double, impossible to
return a string. For example "Rho Error".

If the function return an object, impossible to change the format in
Excel.

Is there a way with Excel DNA to avoid this problem?

Tks,

Julien

[Govert van Drimmelen]

Hi Julien,

I'm not sure what the problem is with returning a detailed error
string for errors. I'm not sure what you mean by 'impossible to change
the format in Excel'. If you return a double it doesn't matter if the
return type of the function is 'double' or 'object'.

You can also consider this:
* Keep your return type as double, and throw an exception if things
are wrong. This will return as #VALUE, which is consistent with how
Excel works. =IFERROR etc. work correctly.
* Register an UnhandledExceptionHandler in your AutoOpen and display
or log the errors somewhere else - maybe in the LogDisplay or a new
form you make.

The .dna file below shows how this could work.

Regards,
Govert


<DnaLibrary Name="ErrorHandler Sample" Language="CS">
<![CDATA[
using System;
using ExcelDna.Integration;
using ExcelDna.Logging;

public class MyAddIn : IExcelAddIn
{

public void AutoOpen()
{
Integration.RegisterUnhandledExceptionHandler( ErrorHandler );
}

private object ErrorHandler(object exceptionObject)
{
ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);

// Calling reftext here requires all functions to be marked
IsMacroType=true, which is undesirable.
// A better plan would be to build the reference text oneself, using
the RowFirst / ColumnFirst info
// Not sure where to find the SheetName then....
string callingName = (string)XlCall.Excel(XlCall.xlfReftext, caller,
true);

LogDisplay.WriteLine(callingName + " Error: " +
exceptionObject.ToString());

// return #VALUE into the cell anyway.
return ExcelError.ExcelErrorValue;
}

public void AutoClose()
{
}

[ExcelFunction(IsMacroType=true)]
public static double DoGood()
{
return 7;
}

[ExcelFunction(IsMacroType=true)]
public static double DoBad()
{
throw new InvalidOperationException("Don't be evil!");
}
}
]]>
</DnaLibrary>

[ajwillshire]

Hi,

My usual way to get error messages out of the functions is just to use
error codes - e.g., err.number in VB.net.

For example one function I wrote returns a double(,) as a value.

At various stages in the code I have a variable which I update as the
algorithm progresses. For example, if the variable is called "Stage"
then after each block of code I set "stage" to a new number so I can
work out where it falls down.

If there's an error then the first element in the output (,) refers to
the stage it failed and the second element is the error number, or if
it is in a loop I might return the value of the counter.

This might be a bit of a half-baked solution but I find it helpful
sometimes.

Andrew

> > Julien- Hide quoted text -
>
> - Show quoted text -

[virginie]

thank you both for your answer.

When I've go a function which return an object which is a number for
example, I can't change the format of this number in Excel.

Let say my function return 2,87647864 and in excel, I want to change
the format to 2,8 I can't as if excel was considering the number as a
text instead of a number.

I think I will just throw an exception to avoid the problem and see
later how can I display the error in a logdisplay.

tks,

julien

[Govert van Drimmelen]

Hi Julien,

If you return the double 2.876567 from a function that has return type
'object', Excel-Dna will return a value of type double to Excel, and
Excel will be able to format the cell with no problem. The only
problem would be if you explicitly return the value as a string, for
example by saying return myDouble.ToString("F6"). Then Excel will
treat it as a string, preventing reformatting.

So the following two functions behave differently w.r.t Excel's
formatting, even though both function have the same declared return
type.

Function GetDoubleDouble() As Object
Return 2.876567
End Function

Function GetDoubleString() As Object
Return (2.876567).ToString("F6")
End Function

As a result, you can safely declare your function to have return type
object, which allows you to return doubles which _can_ be reformatted,
and also error strings which display detailed errors.

Hope this makes sense, and gives you some more option to consider.

Regards,
Govert

[virginie]

Ok I understand now how it works. I will see how I can play with that
to set up a proper error managment.

Julien