使用变量时在 Excel 中通过 VBA 设置验证失败

我正在尝试使用 VBA 设置一系列单元格的数据验证。我使用此代码收到运行时错误 1004（非常有用）“应用程序定义或对象定义错误”。

 With rngRangeToCheck.Cells(lrownum, 1).Validation
    .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=choice
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With

在 Formula1 中，choice 是传递给函数的变量，类似于代码所在工作簿中的“=SomeNamedRange”。

错误发生在.Add代码部分。

如果我将 Formula1 硬编码为Formula1:="=SomeNamedRange"它工作没有问题。我真的不想对其进行硬编码，因为我正在使用许多可能的“选择”值来执行此操作，我认为这只是不太干净的代码。

几天来我一直在烧毁谷歌和大约三本不同的书，现在试图解决这个问题。

有什么建议么？感谢您帮助新手。

这可能也应该是一个评论，特别是因为这篇文章太旧了……我也遇到了同样的问题，它有时会起作用，而另一些时候则不起作用。使用动态命名范围。我找到的解决方案是暂时解锁工作表。

Private Sub FixDropdownLists()

Sheet1.Unprotect Password:="afei31afa"
Dim cellv As Range

For Each cellv In Sheet1.Range("B18:E18,B32:E32")
    With cellv.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Office_Locations"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Invalid Input"
        .InputMessage = ""
        .ErrorMessage = "Select the location only from the dropdown list."
        .ShowInput = False
        .ShowError = True
    End With
Next cellv
Sheet1.Protect Password:="afei31afa"
End Sub