SpreadsheetGear SetArray of double when double.NaN is present

Question

I have a simplified test scenario created where I have a spreadsheet with two cells (C2/C3) having an array formula:

{=NaNTest()}

My simplified CustomFunction is as follows:

public class NaNTest : CustomFunctions.Function
{
    public NaNTest() : this( "NaNTest" ) { }
    public NaNTest( string name ) : base( name, CustomFunctions.Volatility.Invariant, CustomFunctions.ValueType.Variant ) { }

    public override void Evaluate( CustomFunctions.IArguments a, CustomFunctions.IValue r )
    {
        var result = new double[ 1, 2 ];
        result[ 0, 0 ] = double.NaN;
        result[ 0, 1 ] = 0d;
        r.SetArray( result );
    }
}

This sets both C2 and C3 to #NUM! when I'd expect only C2 to be. Is there a way to make it correctly* assign C3 to 0?

Thanks in advance.

* I say correctly because we have to implement an Excel add-in that our clients use to author spreadsheets and it provides same 'functionality' that we provide on 'our servers' when we open/process the spreadsheet in our 'SpreadsheetGear calculations' (i.e. the NaNTest() function above). The libraries we use to create the add-in only assign C2 to #NUM! and having the two implementations (client side add-in vs server side SpreadsheetGear) behaving differently makes maintenance/debugging difficult.

Answer 1

This behavior is by design. Note the comment in the documentation for the IValue.SetArray(...) method:

If any of the numbers in the array are not valid numbers, the result of the custom function will be ValueError.Num.

Since NaN isn't a valid number the entire array will resolve to #NUM! instead. Actually, if you try to set a cell value on its out (outside of a custom function), such as...

worksheet.Cells["A1"].Value = double.NaN;

...you should find that cell evaluates to #NUM! as well. If such cases can occur in your custom function, you'll likely just need to write a check for this condition and respond in whatever manner is required by your application.