什么是运行时错误 91，为什么它会出现在我的 Excel VBA 脚本中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14164950/
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
What is run time error 91, and why is it showing up in my Excel VBA script?
提问by Ajedi32
Okay, so I'm trying to write a simple script in VBA for Excel that changes an AutoFilter based on some value the user selects in a cell on the spreadsheet. So far it's been working out pretty well, but now I'm getting the following error and I can't figure out what's causing it:
好的，所以我正在尝试在 VBA 中为 Excel 编写一个简单的脚本，该脚本根据用户在电子表格单元格中选择的某个值更改自动筛选。到目前为止，它运行得很好，但现在我收到以下错误，我无法弄清楚是什么导致了它：
Run-time error '91': Object variable or With block variable not set
运行时错误“91”：未设置对象变量或块变量
Keep in mind that this is literally the first time I've ever tried to write anything in VBAso I'm not very familiar with the language. I am very familiar with Excel though and I know several other programming languages (Java, JavaScript, Ruby, LUA).
请记住，这实际上是我第一次尝试用 VBA 编写任何东西，所以我对这种语言不是很熟悉。我对 Excel 非常熟悉，而且我知道其他几种编程语言（Java、JavaScript、Ruby、LUA）。
Here's the code I wrote; the error is happening on line 9.
这是我写的代码；错误发生在第 9 行。
Private Sub Worksheet_Change(ByVal Target As Range)
'' Review Level Changed ''
If Target.Address = Worksheets("Sheet1").Range("review_level").Address Then
' MsgBox "You just changed " & Target.Address & " to " & Target.Value ' Debug
Dim oldProtection As Protection
If Worksheets("Sheet1").ProtectContents = True Then
oldProtection = Worksheets("Sheet1").Protection ' It errors out here
Worksheets("Sheet1").Unprotect
End If
If Target = "B" Then
ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
Criteria1:=Array("B", "C", "D"), Operator:=xlFilterValues
ElseIf Target = "C" Then
ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
Criteria1:="=C", Operator:=xlOr, Criteria2:="=D"
ElseIf Target = "D" Then
ActiveSheet.ListObjects("review_checklist").Range.AutoFilter Field:=2, _
Criteria1:="=D"
End If
If Not IsEmpty(oldProtection) Then ' Btw, this IS how you check that oldProtection isn't null, right?
Call ProtectWithProtection(oldProtection)
End If
End If
End Sub
Private Sub ProtectWithProtection(ByRef Protect As Protection)
Worksheets("Sheet1").Protect ' ToDo: Use attributes from Protect
End Sub
This code is located inside "Sheet1" in my Excel project. Note that I am running Excel 2007.
此代码位于我的 Excel 项目中的“Sheet1”内。请注意，我正在运行 Excel 2007。
回答by Kevin Pope
Whenever you have an object in VBA, you need to assign it a value using the Setoperator. For example:
每当您在 VBA 中有一个对象时，您都需要使用Set运算符为其分配一个值。例如：
Set oldProtection = Worksheets("Sheet1").Protection
