Efficiently check if a COM property is available without try/catch

Question

I am using VSTO and would like to check if a certain property is available in a COM object. When it is available, it will return a result, when it isn't available, it will return an exception.

Catching an com exception is extremely slow and I would like to know if there is a more efficient way of checking whether or not that property will throw an exception.

Note that this question is similar but does not have a solution: How to check if a COM property or method exists without generating an exception?

Note that there is another way of checking if a range has validation or not by using Range.SpecialCells, but I am looking for the more general solution of detecting whether or not a property in a COM object is available or not since this same performance problem occurs for other Excel COM objects.

For an example. If a range has Data Validation, Range.Validation.Type property will return a result, if it doesn't have data validation, then it will throw an exception. The code below tests for data validation 10 times on cells that either have it or don't. On my computer testing on a cell that has data validation outputted a time of 0 milliseconds, whereas one that didn't have data validation took over 500 milliseconds.

    public static bool HasDataValidation(Excel.Range range)
    {
        try
        {
            var validationType = range.Validation.Type;
            return true;
        }
        catch (Exception)
        {
            return false;
        }
    }

    public static void RunTest()
    {
        var excelApp = Globals.ThisAddIn.Application;
        var worksheet = (Excel.Worksheet) excelApp.ActiveSheet;
        var rangeWithoutDataValidation = worksheet.Range["A1"];
        var rangeWithDataValidation = worksheet.Range["A2"];

        rangeWithoutDataValidation.Validation.Delete();

        rangeWithDataValidation.Validation.Delete();
        rangeWithDataValidation.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop,
            Excel.XlFormatConditionOperator.xlBetween, "A,B,C");

        var watch = Stopwatch.StartNew();
        for (int i = 0; i < 10; i++)
        {
            bool hasValidation = HasDataValidation(rangeWithoutDataValidation);
        }
        watch.Stop();
        long timeNoValidation = watch.ElapsedMilliseconds;

        watch = Stopwatch.StartNew();
        for (int i = 0; i < 10; i++)
        {
            bool hasValidation = HasDataValidation(rangeWithDataValidation);
        }
        watch.Stop();
        long timeWithValidation = watch.ElapsedMilliseconds;
        MessageBox.Show(String.Format("Time without validation was {0} milliseconds.\nTime with validation was {1} milliseconds.", timeNoValidation,timeWithValidation));
    }

Have you tried Hans' answer here: http://stackoverflow.com/questions/8068449/calling-a-member-of-idispatch-com-interface-from-c-sharp — Simon Mourier, Jul 22 '15 at 16:41
yes I tried it, but it got the ID of the Type property (for the Range.Validation object) both when it would throw the exception and when it wouldn't throw the exception. The property is always there — tjsmith, Jul 22 '15 at 16:48
So it's not a problem of checking if the "property is available", but a problem of calling the property getter. You can try to use the IDispatch Invoke now (with DISPATCH_PROPERTYGET). This is described here: http://www.productiverage.com/idispatch-iwastedtimeonthis-but-ilearntlots — Simon Mourier, Jul 22 '15 at 16:49

score 0 · Answer 1 · answered Jul 21 '15 at 17:17

0

How about something like this:

public static bool HasDataValidation(Excel.Range range)
{
    try
    {
        var validation = range.Validation;
        return (validation != null);
    }
    catch (Exception)
    {
        return false;
    }
}

answered Jul 21 '15 at 17:17

citywall

235
2
11

that will always return true. Every Range has a Validation object as far as I know. It is Validation.Type that throws the COM exception if there is no validation on the range. – tjsmith Jul 21 '15 at 18:25

Efficiently check if a COM property is available without try/catch

1 Answers1