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:
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.
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"
Server stack trace:
Exception rethrown at :
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
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):
My problem solved. Maybe this note might help someone else.
- Marked as answer by Tim Li Thursday, January 15, 2009 5:33 AM