none
Excel VSTO Com Exception on adding Validation to a range.

    Question

  • I'm working on an Add-in for Excel (2003) that inserts data into a sheet. I'm trying to add validation to some cells, but one of the Validation.Add calls is throwing an exception. So far I've been unable to figure out why. I'm trying to build a dependent validation where the dropdown list in one cell is dependent upon the selection in another cell.

    Here's a snippet:
    myRange = currentSheet.get_Range("B2", missing);  
    myRange.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "=LN", missing);  
     
    Excel.Range myRange2 = currentSheet.get_Range("C2", missing);  
    try 
    {  
       myRange2.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "=INDIRECT(B2)", missing);  
    }  
    catch (Exception ex)  
    {  
       MessageBox.Show(ex.StackTrace);  

    The first validation add executes without a problem, the second one throws an exception. When I substitute another formula, like "=LN" as in the previous validation, it adds correctly. Is there something illegal about "=INDIRECT()" here? The desired validation works correctly when inserted manually or using VBA. 

    • The formula "LN" refers to a named range, one of three, that my code has successfully set prior to this point.
    • I tried adding the second range object, "myRange2" in case there was a problem with reusing the first range object. That didn't help either.
    The plan is to extend this to many rows, but if I can't get this simple code or some workaround to work it may be fatal to my project. I might have to resort to VBA. I'd be very disappointed if VSTO can't do what I need, especially after I've been advocating it for this project.

    What am I overlooking?

    Here's the Exception that gets caught by the try/catch:
     

    System.Runtime.InteropServices.COMException was caught

    Message="Exception from HRESULT: 0x800A03EC"

    Source=""

    ErrorCode=-2146827284

    StackTrace:

    Server stack trace:

    Exception rethrown at [0]:

    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

    at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)

    at ExcelAddinTest.ThisAddIn.createSheet_Click(CommandBarButton ctrl, Boolean& CancelDefault) in C:\projects\Experiment Projects\ExcelAddinTest\ExcelAddinTest\ThisAddIn.cs:line 127

    Thursday, January 08, 2009 5:12 PM

Answers

  •  Ok, I've discovered the solution on my own.

    The problem is that in my sample code, while the "Indirect()" is being inserted as the formula for the validation, it evaluates to error unless the target cell (B2 in this case) contains a valid value. This apparently isn't a problem when entering a validation manually in Excel or when VBA is inserting it, but VSTO throws an exception in response.

    The solution, or at least a workaround, is to set the value of the target cell (B2), to a valid value. In this case, that's a name of one of my named ranges that the indirect will point to. After Validation.Add() successfully executes, the target cell can be reset to blank if desired. It only has to contain a valid value while the validation is being added.

    Example (with the try/catch removed and now covering several rows):
    myRange = currentSheet.get_Range("B2""B16");     
    myRange.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "=LN", missing);     
    myRange.Value2 = "MMXU";  //a valid value   
                     
    Excel.Range myRange2 = currentSheet.get_Range("C2""C16");                 
    myRange2.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "=indirect(B2)", missing);     
        
    myRange.Value2 = "";    
     

    My problem solved. Maybe this note might help someone else.



    • Marked as answer by Tim Li Thursday, January 15, 2009 5:33 AM
    Thursday, January 08, 2009 8:00 PM