CODEWITHSUNDEEP
excel-dna
Sylvia Graham
Sylvia Graham

Reputation: 31

How do I detect a reference to an empty cell using Excel-DNA?

     //when the following line encounters a reference to
     //an empty cell, the method returns #VALUE

'''' double val = dataRange[0]; return val; ''''

[ExcelFunction(IsMacroType = true)]
public static object getVal([ExcelArgument(AllowReference = true)] double[] dataRange)

Upvotes: 0

Views: 875

Answers (1)

C. Augusto Proiete
C. Augusto Proiete

Reputation: 27828

By declaring the arguments of the function as double (or double[]) you're only allowed to have valid double values.

If you need to check for special values such as ExcelEmpty you need to declare the arguments of your function as object (or object[]).

e.g.

[ExcelFunction(IsMacroType = true)]
public static object getVal([ExcelArgument(AllowReference = true)] object[] dataRange)
{
    if (dataRange[0] == ExcelEmpty.Value)
    {
        // ...
    }

    // ...
}

If you prefer to get the values via an ExcelReference then declare your input argument as object and check if it's an ExcelReference.

e.g.

[ExcelFunction(IsMacroType = true)]
public static object getVal([ExcelArgument(AllowReference = true)] object input)
{
    if (input is ExcelReference reference)
    {
        // ...

        var value = reference.GetValue();

        // ...
    }

    // ...
}

Upvotes: 1

Related Questions