C# Excel Dependent Picklist with Validation & Indirect

Question

Below is a generic attempt at creating dependent picklists dynamically from C#. When value 'A' is selected from pick1, pick2 is supposed to display the values from the SecondaryRangeA.

This code almost works, but instead of displaying the SecondaryRangeA it displays the literal value 'A'.

pick2.Validation.Add(XlDVType.xlValidateList, 
                     XlDVAlertStyle.xlValidAlertStop, 
                     XlFormatConditionOperator.xlBetween, 
                     "=INDIRECT(\"A5\")");

When I open excel after it exports and modify the data validation it shows the formula.

=INDIRECT("A5")

If I modify the formula manually in Excel to exclude the quotes it works as expected.

=INDIRECT(A5)

When I modify the code to the following I get an exception. Any ideas?

pick2.Validation.Add(XlDVType.xlValidateList, 
                     XlDVAlertStyle.xlValidAlertStop, 
                     XlFormatConditionOperator.xlBetween, 
                     "=INDIRECT(A5)");

Exception:

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  Message=Exception from HRESULT: 0x800A03EC
Source=""


StackTrace:
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2)
   at TestExcelValidation.Program.Main(String[] args) in C:\TFS\ExcelInterop\TestExcelValidation\Program.cs:line 44
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Full Example:

using System.Collections.Generic;
using System.IO;
using System.Linq;
using Microsoft.Office.Interop.Excel;

namespace TestExcelValidation
{
    class Program
    {
        static void Main(string[] args)
        {
            string temporaryPath = Path.GetTempPath();
            string temporaryFile = Path.GetTempFileName();
            Application appl = new Application();
            appl.Visible = true;
            Workbook workbook = appl.Workbooks.Open(temporaryFile, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Worksheet worksheet = (Worksheet)workbook.Worksheets.Add();



            List<string> primaryList = new List<string>();
            primaryList.Add("A");
            primaryList.Add("B");

            List<string> secondaryListA = new List<string>();
            secondaryListA.Add("A1");
            secondaryListA.Add("A2");
            secondaryListA.Add("A3");

            List<string> secondaryListB = new List<string>();
            secondaryListB.Add("B1");
            secondaryListB.Add("B2");
            secondaryListB.Add("B3");

            Range primaryRange = AddToExcelNamedRange(worksheet, primaryList, 'A', 1, "PrimaryRange");
            Range secondaryRangeA = AddToExcelNamedRange(worksheet, secondaryListA, 'B', 1, "A");
            Range secondaryRangeB = AddToExcelNamedRange(worksheet, secondaryListB, 'C', 1, "B");

            Range pick1 = worksheet.Range["A5"];
            pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange");
            Range pick2 = worksheet.Range["A6"];
            pick2.Validation.Delete();
            pick2.NumberFormat = "Text";
            pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(\"A5\")");
            pick2.Validation.InCellDropdown = true;
            pick2.Validation.IgnoreBlank = true;
        }

        private static Range AddToExcelNamedRange(Worksheet worksheet, List<string> primaryList, char col, int row, string rangeName)
        {
            Range range = worksheet.Range[col.ToString() + row.ToString(), col.ToString() + primaryList.Count().ToString()];
            range.Name = rangeName;
            foreach (string item in primaryList)
            {
                worksheet.Cells[row, col - 64] = item;
                row++;
            }
            return range;
        }
    }
}

Answer 1

I have a workaround, but I want to know why this doesn't work. I'm sure I will run across this again.

_{Here is an answer in English, god only knows what the other two are saying.}

Problem

Adding a cascading drop down list using a validation in Excel via C# (or VBA) fails with a COMException 0x800A03EC.

Cause

The reason it doesn't work is because the source is actually empty.

Let me show you how I worked this out. I injected a Macro in Excel and ran it:

Range pick1 = worksheet.Range["A5"];
pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange");
Range pick2 = worksheet.Range["A6"];

StringBuilder sb = new StringBuilder();
sb.Append("Sub InsertCascadingDropDown()" + Environment.NewLine);
sb.Append("    Range(\"A6\").Select" + Environment.NewLine);
sb.Append("    With Selection.Validation" + Environment.NewLine);
sb.Append("        .Delete" + Environment.NewLine);
sb.Append("        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=\"=INDIRECT(A5)\"" + Environment.NewLine);
sb.Append("        .IgnoreBlank = True" + Environment.NewLine);
sb.Append("        .InCellDropdown = True" + Environment.NewLine);
sb.Append("        .ShowInput = True" + Environment.NewLine);
sb.Append("        .ShowError = True" + Environment.NewLine);
sb.Append("    End With" + Environment.NewLine);
sb.Append("End Sub" + Environment.NewLine);

//You need to add a COM reference to Microsoft Visual Basic for Applications Extensibility for this to work
var xlmodule = workbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); 

xlmodule.CodeModule.AddFromString(sb.ToString()); 
appl.Run("InsertCascadingDropDown");

This causes a run-time error '1004' when the Macro executes the line to add the Validation:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(A5)"

So this led me to be believe the selection object was not defined (or more to the point it was empty, depending on how you interpret the word Selection).

Solution

I played around with this and eventually halted the code control and added the validation manually when I discovered this:

The source currently evaluates to an error.

That was a smoking gun that the selection object was not null, it was the A5 drop down lists' selection/value was infact empty!

Adding the cascading drop down list needs its parent to have a value!

So this is all you need to do:

pick1.Value2 = "A";  //< set the parent to have a value
pick2.Validation.Delete();  //<- this is not really needed, unless you run this again
pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A5)");

Answer 2

At least with Excel 2007 and 2010, =Indirect(A5) without the quotes evaluates to #REF when used in a cell. I'm thinking how this code is passed from C# has something to do with the exception (since it would evaluate to an error). Also, using the same function when manually creating the Validation also gives the message it evaluates to an error in excel. Edit: Though I stand corrected with some points here with more research.

Input expected from Indirect() expects a string value in A1 or R1C1 format instead of an actual cell reference. Unless, the target range is a cell reference ex: A5 was A1.

According to MSDN, Indirect() would only calculate when the file is open and in memory MSDN 151323. Opening the workbook and changing the validation list and seeing it evaluate correctly doesn't mean the error doesn't exist when the code in C# is ran.

Answer 3

If for example the value in A5 is B then =INDIRECT(A5) is the same as =INDIRECT("B") which is the same as =B which is not valid formula or cell reference.

=INDIRECT("A5") is the same as =A5