通过VBA添加依赖下拉列表时的数据验证报错问题

问题背景 #

目标是通过VBA添加依赖下拉列表，但执行.Add语句时触发报错，原代码如下：

Sub TaskNoValidation(InputTable As ListObject)
    
    'Data Validation application
    With InputTable.ListColumns("Task No").DataBodyRange.Validation
        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, _
        Operator:=xlBetween, Formula1:= _
        "=INDIRECT(""Task_List[""&LEFT(C14&"" "",FIND("" "",C14&"" "")-1)&""]"")"
        '=INDIRECT("Task_List["&LEFT(C14&" ",FIND(" ",C14&" ")-1)&"]")
        
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Task Not Found"
        .InputMessage = ""
        .ErrorMessage = _
        "Note:  The task number entered does not match any tasks numbers assigned to the project in the project database.  Please email Bridge Department Admin Assistant with the information to be added."
        .ShowInput = True
        .ShowError = True
    End With

End Sub

报错核心原因：当关联单元格C14为空时，数据验证公式会因FIND函数无法解析有效内容导致计算失败；即使修改公式为添加空格兜底的版本，添加数据验证时仍会因C14为空触发解析报错，但C14有值时添加正常，后续清空C14后下拉列表可正常显示。

临时解决方案 #

通过临时填充关联单元格为有效项目值，完成数据验证添加后再恢复原值：

Sub TaskNoValidation(InputTable As ListObject)
    
Dim InitialValue As Variant
Dim loPNL As ListObject
    
    Set loPNL = ThisWorkbook.Worksheets("Project Numbers").ListObjects("Project_Number_List")
    
    InitialValue = InputTable.DataBodyRange(1, 1)
    InputTable.DataBodyRange(1, 1) = loPNL.DataBodyRange(1, 1)
        
    With InputTable.ListColumns("Task No").DataBodyRange.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, _
        Operator:=xlBetween, Formula1:= _
        "=INDIRECT(""Task_List[""&LEFT(C14&"" "",FIND("" "",C14&"" "")-1)&""]"")"
        '=INDIRECT("Task_List["&LEFT(C14&" ",FIND(" ",C14&" ")-1)&"]")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Task Not Found"
        .InputMessage = ""
        .ErrorMessage = _
        "Note:  The task number entered does not match any tasks numbers assigned to the project in the project database.  Please email Bridge Department Admin Assistant with the information to be added."
        .ShowInput = True
        .ShowError = True
    End With

    InputTable.DataBodyRange(1, 1) = InitialValue
    
End Sub

替代解决方案 #

除临时填充法外，可通过以下两种方式直接规避报错：

方法1：用IFERROR包裹公式，兜底空值场景 #

修改数据验证的Formula1参数，确保C14为空时公式仍能解析到有效列表：

"=INDIRECT(IFERROR(""Task_List[""&LEFT(C14&"" "",FIND("" "",C14&"" "")-1)&""]"", ""Task_List[默认项目]"")"

将"Task_List[默认项目]"替换为你的任务列表中存在的任意项目名称即可。

方法2：使用结构化引用替代固定单元格地址 #

针对ListObject表格，用结构化引用代替固定的C14地址，同时结合IFERROR处理空值，让公式更通用：

"=INDIRECT(IFERROR(""Task_List[""&LEFT([@[Project Name]]&"" "",FIND("" "",[@[Project Name]]&"" "")-1)&""]"", ""Task_List[默认项目]"")"

（需将[@[Project Name]]替换为表格中关联列的实际表头名称）

以上两种方法无需临时修改单元格内容，无论关联单元格是否为空，添加数据验证时公式都能正常解析。

内容的提问来源于stack exchange，提问作者Forward Ed