vba 错误：未设置对象变量或块变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5640635/
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: object variable or with block variable not set
提问by LFurness
I'm getting a "object variable or with block variable not set" error message when running the following code. It is in Access and refers to an Excel spreadsheet. What is wrong with the code?
运行以下代码时，我收到“对象变量或块变量未设置”错误消息。它在 Access 中，指的是 Excel 电子表格。代码有什么问题？
wsTest.Range("A11").Activate
Do Until IsEmpty(ActiveCell)
intX = intX + wsTest.Cells(ActiveCell.Row, "L") 'error occurs on this line
intY = intY + wsTest.Cells(ActiveCell.Row, "I")
ActiveCell.Offset(1, 0).Select ' Step down 1 row to the next cell.
Loop
The first time the code is run, there is no error, only the second time. Closing and reopening Access "fixes" the problem. How can that be related to this code?
第一次运行代码没有报错，只有第二次。关闭并重新打开 Access“修复”了该问题。这与这段代码有什么关系？
采纳答案by Tiago Cardoso
As the error occurs in the highlighted line, I believe you're trying to do an invalid sum. Maybe the value returned by wsTest.Cells(oCell.Row, "L")isn't a integer, or you should use wsTest.Cells(oCell.Row, "L").Valueinstead.
由于错误发生在突出显示的行中，我相信您正在尝试计算无效的总和。也许返回的值wsTest.Cells(oCell.Row, "L")不是整数，或者您应该使用它wsTest.Cells(oCell.Row, "L").Value。
Will be easier to identify the root cause of the problem adding a msgbox to check the cell value, as I added below.
添加一个 msgbox 来检查单元格值会更容易确定问题的根本原因，正如我在下面添加的那样。
Either way, I'd suggest you to avoid using references to ActiveCell, as they aren't reliable. Maybe this code helps avoiding this error (simply replacing the ActiveCell by a proper cell object).
无论哪种方式，我都建议您避免使用对 ActiveCell 的引用，因为它们不可靠。也许此代码有助于避免此错误（只需用适当的单元格对象替换 ActiveCell）。
Dim oCell as excel.range
set oCell = wsTest.Range("A11")
Do Until Len(oCell.Value) = 0
msgbox(wsTest.Cells(oCell.Row, "L"))
intX = intX + wsTest.Cells(oCell.Row, "L") 'error occurs on this line
intY = intY + wsTest.Cells(oCell.Row, "I")
Set oCell = oCell.Offset(1, 0) ' Step down 1 row to the next cell.
Loop
回答by Lance Roberts
You need to define the sheet, like so:
您需要定义工作表，如下所示：
Dim wsTest As Worksheet
Set wsTest = Sheets("Sheet1")
回答by mwolfe02
Try changing
尝试改变
wsTest.Range("A11").Activate
to
到
wsTest.Range("A11").Select
