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.

score 2 · Accepted Answer · answered Apr 27 '15 at 13:58

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.

Thanks. We'll take precautions in our custom functions to try and protect from that and in addition make our 'other component' behave the same way. (FYI I tried Googling your site to see if I could find anything in SetArray but nothing came up...) — Terry, Apr 27 '15 at 14:09

SpreadsheetGear SetArray of double when double.NaN is present

1 Answers1