VBA：错误 91 对象变量或未设置块变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15996328/
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
VBA: error 91 Object variable or with block variable not set
提问by ShadowScorpion
I faced a weird issue of VBA error 91. I saw many other people have this problem because they didn't use keyword "Set" for object, whereas that is not my case.
我遇到了一个奇怪的 VBA 错误 91 问题。我看到很多其他人都有这个问题，因为他们没有对对象使用关键字“Set”，而我的情况不是这样。
Following is my code:
以下是我的代码：
Dim eventWS As Worksheet
Set eventWS = Worksheets("Event Sheet")
Dim eventRange As Range
Set eventRange = eventWS.Columns("A:A").Find(240, , xlValues, xlWhole)
If Not eventRange Is Nothing Then
Dim eventFirstAddress As String
eventFirstAddress = eventRange.Address
Do
If eventWS.Range("L" & eventRange.Row).Value = busId Then
If commuter = True Then
Count = Count + Affected(eventWS.Range("Q" & eventRange.Row).Value)
Else
Count = Count + 1
End If
End If
MsgBox("Before call move next: " & eventRange.Row )
Set eventRange = eventWS.Columns("A:A").FindNext(eventRange)
MsgBox("After call move next: " & eventRange.Row )
Loop While Not eventRange Is Nothing And eventRange.Address <> eventFirstAddress
End If
Affected() is a function I can call to do internal processing. And if I removed this "Count = Count + Affected(....)", the code was working fine. If I added it, "Loop While " would throw error 91. If I added a message box to print out the row number before and after moving eventRange, it turned out that "MsgBox("After call move next: " & eventRange.Row)" would throw error 91.
Affected() 是我可以调用以进行内部处理的函数。如果我删除了这个“Count = Count + Affected(....)”，代码就可以正常工作了。如果我添加它，“Loop While”会抛出错误 91。如果我添加一个消息框来打印移动 eventRange 前后的行号，结果是“MsgBox("After call move next:" & eventRange.Row )" 会抛出错误 91。
Hence, I'm confuse whether the issue is caused by the internal function or the eventRange now. Hope someone can point my mistakes out. Thank you very much.
因此，我现在很困惑问题是由内部函数还是由 eventRange 引起的。希望有人能指出我的错误。非常感谢。
Following are the codes of internal function:
以下是内部函数的代码：
Function Affected(markerId As Integer) As Integer
'initialized return value'
AffectedCoummters = 0
'get total financial sheets'
Dim totalFinancial As Integer
totalFinancial = 0
For Each ws In Worksheets
If InStr(ws.Name, "Financial") > 0 Then
totalFinancial = totalFinancial + 1
End If
Next
Dim i As Integer
'run through all financial sheets'
For i = 1 To totalFinancial
'get current financial sheet'
Dim financialWS As Worksheet
Set financialWS = Worksheets("Financial Sheet" & i)
'get total rows of current operation sheet'
Dim rowSize As Long
rowSize = financialWS.Range("A" & financialWS.Rows.Count).End(xlUp).Row
'if reach the maximum number of rows, the value will be 1'
'reInitialize rowSize based on version of Excel'
If rowSize = 1 Then
If Application.Version = "12.0" Then
'MsgBox ("You are using Excel 2007")'
If InStr(ThisWorkbook.Name, ".xlsx") > 0 Then
rowSize = 1048576
Else
'compatible mode'
rowSize = 65536
End If
ElseIf Application.Version = "11.0" Then
'MsgBox ("You are using Excel 2003")'
rowSize = 65536
End If
End If
'filter by marker id first inside current financial sheet'
Dim findMarker As Range
Set findMarker = financialWS.Columns("K:K").Find(markerId, , xlValues, xlWhole)
'if found any given marker id'
If Not findMarker Is Nothing Then
Dim firstAddress As String
firstAddress = findMarker.Address
'check all matched marker id'
Do
AffectedCommuters = AffectedCommuters + financialWS.Range("O" & findMarker.Row).Value
'move to next'
Set findMarker = financialWS.Columns("K:K").FindNext(findMarker)
Loop While Not findMarker Is Nothing And findMarker.Address <> firstAddress
End If
Next i
End Function
采纳答案by KacireeSoftware
Sorry I dont have enough rep to comment so I have to answer here :( Just want to say that although it is standard procedure to use
抱歉，我没有足够的代表发表评论，所以我必须在这里回答:( 我只想说，虽然这是使用的标准程序
Loop While Not eventRange Is Nothing And eventRange.Address <> eventFirstAddress
in this type of procedure, if eventRange is actually Nothing, the line will throw Error 91, because eventRange.address does not exists. What this means is that once you have found something, you can't modify the row in such a way that it will not be found again using .findnext.
在这种类型的过程中，如果 eventRange 实际上是 Nothing，则该行将抛出错误 91，因为 eventRange.address 不存在。这意味着一旦您找到了某些东西，您就无法修改该行，以免使用 .findnext 再次找到它。
After you exit the do...loop, you can modifiy the range to suit... Perhaps you want to use an array to hold all the rows from your .find...findnext results, and then manipulate them after the Do...loop
退出 do...loop 后，您可以修改范围以适应...也许您想使用一个数组来保存 .find...findnext 结果中的所有行，然后在 Do 之后操作它们。 ..环形
