Best way to test for empty or missing values

Dec 28, 2013 at 5:02 PM
Hello,

New to ExcelDNA, but what a great tool. What is the preferred way to test for a cell or parameter being empty or missing? I am using the following, but wonder if I am missing something already built in?
public static bool IsItemEmpty(object value)
{
    return (value == null || value.ToString().Equals("ExcelDna.Integration.ExcelEmpty", StringComparison.OrdinalIgnoreCase)) ? true : false;
}

public static bool IsItemMissing(object value)
{
    return (value == null || value.ToString().Equals("ExcelDna.Integration.ExcelMissing", StringComparison.OrdinalIgnoreCase)) ? true : false;
}
Coordinator
Dec 28, 2013 at 9:09 PM
Hi,

Your Excel-DNA function will never be called with a null. You can check for Missing or Empty like this:
using ExcelDna.Integration;
public class MyFunctions
{
    public static object TestInput(object input)
    {
        if (input is ExcelMissing)
        {
            return "!!! No value passed";
        }
        if (input is ExcelEmpty)
        {
            return "!!! Empty cell passed";
        }
        return "Something Interesting: " + input.ToString();
      
    }
}
Now if you call the function as =TestInput() the result will be the "No value passed", while =TestInput(A2) with A2 empty will give you the "Empty cell passed" result.

-Govert
Marked as answer by j2associates on 12/28/2013 at 4:51 PM
Dec 28, 2013 at 11:08 PM
Thank you for the quick and timely reply. I am actually using the two methods in an item validation method as below. I guess its just the old programmer in me that always checks an object for null.
private static object validateValue(object value, object defaultValue, ref bool usedDefaultValue)
{ 
    usedDefaultValue = value == null || IsItemEmpty(value) || IsItemMissing(value) ? true : false;  
    return usedDefaultValue ? defaultValue : value;
}