VBA简单范围/对象错误 [英] vba simple range/object error

问题描述

所以我认为我对VBA的掌握不错，但是现在这个错误确实困扰着我，我无法弄清问题所在.这是一个非常短的子例程(从模块运行):

So I thought I had an OK grasp of VBA but now this error is really bugging me and I can't figure out the problem. Here is an incredibly short subroutine (run from a module):

Sub test()
    Dim lr
    lr = Sheets(1).Range("a1", Cells(65, 1))
    MsgBox lr.Address
End Sub

无论出于何种原因，都会引发应用程序定义/对象定义的错误"

For whatever reason this throws up an "application defined/object defined error"

有人可以解释吗?

这也会引发相同的错误:

This also throws up the same error:

MsgBox Sheets(1).Range("a1", Cells(65, 1)).Address

Edit2:我将对问题有所了解，但我将如何使用查找"方法来解决这个问题.所以这是代码:

I'm going to change my question a bit as I understand about the object thing but I what to use it with the "find" method. So here is the code:

Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)

在这种情况下，它告诉我lookrange引起了问题.

In this case it tells me lookrange is causing a problem.

推荐答案

好.你有几个问题.

Option Explicit
sub test()
    Dim lr as Range
    set lr = Sheets(1).Range("a1", Cells(65, 1))
    MsgBox lr.Address
End Sub

首先，作为VBA的新手，几乎总是在模块顶部使用 Option Explicit ，因此您需要确定变量的尺寸.

First, as someone who is new to VBA, use Option Explicit nearly always at the tops of your modules so you are required to dimension your variables.

第二，您永远不要将 lr 声明为对象类型-确保将其声明为 Range 类型-但是，您不一定必须这样做.这是一个好习惯.

Second, you never declare lr as an object type - make sure you declare it as a type Range - you don't necessarily have to do this, however, it is good practice.

第三，在分配 Range 值之类的对象时，您需要像其他人指出的那样使用 set ，否则您总是会得到该错误.

Third, you need to use set as others have indicated when assigning objects such as Range values or you will always get that error.

问题的第二部分.

MsgBox Sheets(1).Range("a1", Cells(65, 1)).Address

将此更改为

With Sheets(1)
    MsgBox .Range(.Range("A1"), .Cells(65, 1)).Address
End With

如果您是从不同于 Sheet1 的工作表上运行此操作，则会出现错误，因为 Cells(65,1)引用将不在上Sheet1 -Excel将认为您的意思是当前工作表.

If you are ever running this from a sheet different than Sheet1 you will get errors, because the Cells(65,1) reference will not be on Sheet1 - Excel will think you mean the current worksheet.

第三部分

Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)

与第二部分存在相同的问题.另请注意，如果找不到 result ，并且很有可能在您运行错误，并且上面的第二条语句( Find 的结果)将导致错误，如果 Find 不成功.

Has the same issue as second part. Also note it is entirely possible you will run errors if result cannot be found and the second statement above (the results of Find) will cause errors if Find is unsuccessful.

这篇关于VBA简单范围/对象错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！