vba 使用变量时，在excel中通过VBA设置验证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3299478/
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
Setting validation via VBA in excel fails when using variable
提问by Gradatc
I am trying to set the data validation for a range of cells using VBA. I get a run-time error 1004 (so helpful) "Application defined or object defined error" with this code.
我正在尝试使用 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
In Formula1, choice is a variable passed to the function that resembles "=SomeNamedRange" from the workbook the code is in.
在 Formula1 中，choice 是传递给函数的变量，它类似于代码所在工作簿中的“=SomeNamedRange”。
The error occurs on the .Addsection of the code.
错误发生在.Add代码部分。
If I hard-code Formula1 as Formula1:="=SomeNamedRange"it works without a problem.I'd really rather not hard-code it, because I am doing this with a lot of possible values for 'choice' and that would just be less-than-clean code, I think.
如果我对 Formula1 进行硬编码，因为Formula1:="=SomeNamedRange"它可以正常工作。我真的宁愿不对其进行硬编码，因为我正在使用许多可能的“选择”值来执行此操作，而这只是不太干净的代码， 我认为。
I have been burning up Google and about 3 different books for days now trying to sort this out.
几天来，我一直在烧掉 Google 和大约 3 本书，试图解决这个问题。
Any suggestions? Thanks for helping a newbie out.
有什么建议？感谢您帮助新手。
采纳答案by jevakallio
Are you sure your choicevariable's value is what you think it is? Maybe you should set a breakpoint before the .Addline and see what you're passing in. I tested the code in Excel 2003 and 2007, and it works without any issues. Only when I give the Formula1 and invalid range reference do I get the error 1004.
你确定你的choice变量的值是你认为的吗？也许你应该.Add在行前设置一个断点，看看你传入了什么。我在 Excel 2003 和 2007 中测试了代码，它没有任何问题。只有当我给出公式 1 和无效范围参考时，我才会收到错误 1004。
Can you try to run this is a new untouched workbook and see if it works for you (sure did for me):
您可以尝试运行这是一个新的未修改的工作簿，看看它是否适合您（确实对我有用）：
Sub Test()
'Setup '
ActiveSheet.Range("B1:B2").Name = "SomeNamedRange"
ActiveSheet.Range("B1").Value = "Apples"
ActiveSheet.Range("B2").Value = "Oranges"
Dim lrownum As Long
lrownum = 1
Dim choice
choice = "=SomeNamedRange"
Dim rngRangeToCheck As Excel.Range
Set rngRangeToCheck = ActiveSheet.Range("A1:A10")
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
End Sub
(This should actually be a comment, not an answer, but I needed to post code so it's easier this way. I'll edit this to be my answer if I come up with one.)
（这实际上应该是评论，而不是答案，但我需要发布代码，所以这样更容易。如果我想出一个答案，我会编辑它作为我的答案。）
回答by Gruzzles
This probably should be a comment too, especially since this post is so old...I had the same problem, where it would work some of the time and not others. Using a dynamically named range. The solution that I found was to unlock the sheet temporarily.
这可能也应该是一个评论，特别是因为这篇文章太旧了......我遇到了同样的问题，它有时会起作用，而在其他时候不起作用。使用动态命名的范围。我找到的解决方案是暂时解锁工作表。
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
回答by Torhi
This is a pretty old post but for anyone getting here with the the same issue I did, here is how the problem was solved in my case:
这是一篇很老的帖子，但对于遇到同样问题的人来说，这里是我的案例中解决问题的方法：
I just replaced the
我刚换了
With Range(Cell1,Cell2).Validation
part by
部分
Range(Cell1,Cell2).Select
With Selection.Validation
And tadaaa! it works :)
和tadaaa！有用 ：）
回答by Nic Paul
The problem is almost certainly caused by the context of the named range being different from the current context in Excel.
该问题几乎肯定是由命名范围的上下文与 Excel 中的当前上下文不同引起的。
When Validation.Addis called, Excel evaluates the formula (which is returning a Rangeobject) in the current context of the application, not the workbook or worksheet. So, if the named range is sheet-based and that sheet is not currently active, it won't work. If the named range exists in one workbook, but a different workbook is active, then it won't work. This is why your workaround of selecting a random cell solves the problem, and also the solution of performing a .Selectfollowed by Selection.Validation.
当Validation.Add被调用时，Excel评估式（它返回一个Range在应用程序中，而不是工作簿或工作的当前上下文对象）。因此，如果命名范围是基于工作表的并且该工作表当前未处于活动状态，则它将不起作用。如果命名范围存在于一个工作簿中，但另一个工作簿处于活动状态，则它将不起作用。这就是为什么您选择随机单元格的解决方法解决了该问题，以及执行.Select后跟Selection.Validation.
回答by Frantisek
I have referenced named location using the INDIRECT function and everything run perfect. Users however reported some regular, seemingly random errors and from log file I saw that this Validation.add is causing the problem. The thing is, that the Excel tool is running in background while different Excel file operated by user may have focus when this operation is executed.
我已经使用 INDIRECT 函数引用了命名位置，并且一切运行正常。然而，用户报告了一些常规的、看似随机的错误，从日志文件中我看到这个 Validation.add 导致了问题。问题是，Excel 工具在后台运行，而在执行此操作时，用户操作的不同 Excel 文件可能具有焦点。
Didn't test the following Code. Hope it helps.
没有测试以下代码。希望能帮助到你。
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=INDIRECT(""[" & ThisWorkbook.Name & "]" & sht_sfDocumentation.Name & "!" & gstrRANGE_NamedTableRange & """)"
回答by Tony
Make sure that you are not passing into the Validation.Add a formula that is in row/column. For example "=R2C2:R3C3" caused me problems, as soon as I changed it to "B2:C3" it worked. Also the cells in the range MUST have values in them, or you will get application error.
确保您没有传递到 Validation.Add 行/列中的公式。例如，“=R2C2:R3C3”给我带来了问题，一旦我将其更改为“B2:C3”，它就起作用了。此外，范围内的单元格必须包含值，否则您将收到应用程序错误。
