使用 vba 进行数据验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10510160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data validation with vba
提问by Kartik Anand
Normally it is possible to apply data validation in excel through vba but i am having a specific problem
通常可以通过 vba 在 excel 中应用数据验证，但我有一个特定的问题
I am using the indirect method for applying validation,normally when i dont use vba i get a warning by excel that "source currently evaluates to an error,do u want to continue?" (this is because cells which indirect refers to may be empty), now i can easily skip this error in excel by clicking "Yes"
我正在使用间接方法来应用验证，通常当我不使用 vba 时，我会收到 excel 的警告“源当前评估为错误，您想继续吗？” （这是因为间接引用的单元格可能为空），现在我可以通过单击“是”轻松跳过 excel 中的此错误
Here's the link http://www.contextures.com/xldataval02.html(Dependent validation lists)
这是链接http://www.contextures.com/xldataval02.html（相关验证列表）
But when i try to perform the same thing using vba i am getting a runtime error,there is no friendly prompt which allows me to continue.How can i handle this kind of error.
但是当我尝试使用 vba 执行相同的操作时，我收到了一个运行时错误，没有允许我继续的友好提示。我该如何处理这种错误。
On error resume isnt working because then vba doesnt apply validation at all.
错误恢复不起作用，因为那时 vba 根本不应用验证。
Here's the code
这是代码
Set rng = ThisWorkbook.Sheets("input").Range("AB11:AB65536")
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
cablecode is a static named range
cablecode 是一个静态命名范围
Q11 and Z11 refer to inputs in that particular row
Q11 和 Z11 指的是该特定行中的输入
depthand valuesare normal strings
深度和值是普通字符串
cablecode can have two values = "is" or "iec"
cablecode 可以有两个值 = "is" 或 "iec"
Q11 can be "xlpe" or "pvc"
Q11 可以是“xlpe”或“pvc”
Z11 can be "al" or "cu"
Z11 可以是“al”或“cu”
since cablecode is constant for whole project ive referred to it directly, Q11 and Z11 can be different for different rows so ive referred to them seperately
由于电缆代码对于我直接引用的整个项目是不变的，因此 Q11 和 Z11 对于不同的行可能不同，因此我分别引用它们
The whole string comes out to be "is_depth_al_xlpe_values" similarly different permuations,and all named ranges are already defined
整个字符串出来是“is_depth_al_xlpe_values”类似的不同排列，并且所有命名范围都已经定义
回答by shahkalpesh
I suppose the following line needs correction from
我想以下行需要更正
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=INDIRECT(cablecode&""_depth_""&$Q11&""_""&$Z11&""_values"")"
to
到
dim cellToBeReferred as string
cellToBeReferred = cablecode & "_depth_" & Range("$Q11").Value & "_" _
& Range("$Q11").Value & "_values"
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=INDIRECT(" & cellToBeReferred & ")"
Taking the string out will help you debug the issue and figure what is the value of named range, you'd want to refer to. See what value cellToBeReferredresults into and correct the concatenation using above.
取出字符串将帮助您调试问题并找出您想要参考的命名范围的值。查看cellToBeReferred结果是什么值并使用上面的方法更正连接。
EDIT after reading OPs comment
阅读 OP 评论后编辑
In that case, surround the INDIRECTwith ISERRORand give a dummy cell reference.
For e.g.
在这种情况下，将INDIRECTwithISERROR括起来并给出一个虚拟单元格引用。例如
"=IFERROR(INDIRECT(" & cellToBeReferred & "), Z1)"
Where Z1 is a cell to be referred if INDIRECTfails.
其中 Z1 是INDIRECT失败时要引用的单元格。
