How to tell blanks from 0's in passed excel range

63 views
Skip to first unread message

MB

unread,
Jun 22, 2021, 11:08:40 AM6/22/21
to Excel-DNA
Hi.  I am new to Excel DNA and have the following question.  I am passing a range of 20 rows of numbers through to Excel DNA.    not all of the rows will have numbers and will be blanks in excel.  When I pass the range through to DNA it returns 0's for the blank cells.  I need to be able to distinguish between true blanks and actual 0's.  is there a way to do this?  thank you


public static object dnaTest(double[] DummyRange)


Govert van Drimmelen

unread,
Jun 22, 2021, 11:29:10 AM6/22/21
to exce...@googlegroups.com
If you change the parameter to be of type object[] or object[,] instead of double[] then you will get empty strings instead of the 0s.

-Govert


On Tue, 22 Jun 2021, 17:08 MB, <steward...@gmail.com> wrote:
Hi.  I am new to Excel DNA and have the following question.  I am passing a range of 20 rows of numbers through to Excel DNA.    not all of the rows will have numbers and will be blanks in excel.  When I pass the range through to DNA it returns 0's for the blank cells.  I need to be able to distinguish between true blanks and actual 0's.  is there a way to do this?  thank you


public static object dnaTest(double[] DummyRange)


--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4beb1a3e-25ff-484e-bb7c-15878b69c7c7n%40googlegroups.com.

MB

unread,
Jun 22, 2021, 11:54:10 AM6/22/21
to Excel-DNA
Thank you Govert.  So this would be a range of 20 rows and 1 column being passed through.  Ultimately i want to find the first non-blank cell, and then do a calculation on that value,  but for this example I just want to check if the first value is Blank. This doesnt work.  What I am missing.  thank you for your assistance.

public static object dnaTest(object[] DummyRange)
 {
   string Blank;
   Blank="No";
   if (DummyRange[0]=null)
     Blank="Yes";
    return Blank;

Govert van Drimmelen

unread,
Jun 22, 2021, 6:17:46 PM6/22/21
to Excel-DNA
Sorry - I should have explained a bit more.
The array that you get for an object[] or object[,] argument will contain objects of different types.
Each entry in the array will be one of the following:
  • Double
  • String
  • Boolean
  • ExcelDna.Integration.ExcelError
  • ExcelDna.Integration.ExcelMissing
  • ExcelDna.Integration.ExcelEmpty
You will never get a null in the array.
An empty cell will have type ExcelEmpty.

So your function might look like this:

public static object dnaTest(object[] DummyRange)
 {
   string Blank;
   Blank="No";
   if (DummyRange[0] is ExcelEmpty)
     Blank="Yes";
    return Blank;
}

Of course you might have other values like a string with a space in as the entry.
So you might check whether the value is a double to detect the first number.

-Govert

MB

unread,
Jun 22, 2021, 9:49:01 PM6/22/21
to Excel-DNA
Perfect. Thank you!

Can Excel DNA tell if a particular excel cell in a range has a specified color or other format?

Reply all
Reply to author
Forward
0 new messages