vba错误：未设置对象变量或with块变量

发布于 2024-10-31 17:27:56 字数 437 浏览 1 评论 0原文

运行以下代码时，我收到“对象变量或块变量未设置”错误消息。它位于 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

第一次运行代码没有错误，第二次才出现错误。关闭并重新打开 Access 可“修复”该问题。这怎么可能与这段代码相关呢？

原文

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?

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一念一轮回 2024-11-07 17:27:56

您需要定义工作表，如下所示：

Dim wsTest As Worksheet

Set wsTest = Sheets("Sheet1")

You need to define the sheet, like so:

Dim wsTest As Worksheet

Set wsTest = Sheets("Sheet1")

回复收藏 0 原文

梦在深巷 2024-11-07 17:27:56

由于错误发生在突出显示的行中，我相信您正在尝试进行无效的求和。也许 wsTest.Cells(oCell.Row, "L") 返回的值不是整数，或者您应该使用 wsTest.Cells(oCell.Row, "L")。值来代替。

添加消息框来检查单元格值将更容易确定问题的根本原因，如我在下面添加的。

不管怎样，我建议您避免使用对 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

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").Value instead.

Will be easier to identify the root cause of the problem adding a msgbox to check the cell value, as I added below.

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).

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

回复收藏 0 原文