vba 简单范围/对象错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12711519/
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 simple range/object error
提问by harryg
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):
所以我认为我对 VBA 有一个很好的掌握，但现在这个错误真的让我很烦恼，我无法弄清楚问题所在。这是一个非常短的子程序（从模块运行）：
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"
无论出于何种原因，这都会引发“应用程序定义/对象定义错误”
can anyone explain?
谁能解释一下？
Edit: 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:
Edit2：当我对对象事物的理解时，我将稍微改变我的问题，但我如何将它与“查找”方法一起使用。所以这里是代码：
Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)
In this case it tells me lookrange is causing a problem.
在这种情况下，它告诉我 lookrange 导致了问题。
回答by enderland
Ok. You've got several problems.
好的。你有几个问题。
Option Explicit
sub test()
Dim lr as Range
set lr = Sheets(1).Range("a1", Cells(65, 1))
MsgBox lr.Address
End Sub
First, as someone who is new to VBA, use Option Explicitnearly always at the tops of your modules so you are required to dimension your variables.
首先，作为刚接触 VBA 的人，Option Explicit几乎总是在模块的顶部使用，因此您需要对变量进行标注。
Second, you never declare lras 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.
其次，您永远不要声明lr为对象类型——确保将其声明为类型Range——您不一定必须这样做，但是，这是一种很好的做法。
Third, you need to use setas others have indicated when assigning objects such as Rangevalues or you will always get that error.
第三，set在分配诸如Range值之类的对象时，您需要按照其他人的指示使用，否则您将始终遇到该错误。
Onto the second part of your question.
进入你问题的第二部分。
MsgBox Sheets(1).Range("a1", Cells(65, 1)).Address
Change this to
将此更改为
With Sheets(1)
MsgBox .Range(.Range("A1"), .Cells(65, 1)).Address
End With
If you are ever running this from a sheet different than Sheet1you will get errors, because the Cells(65,1)reference will not be on Sheet1- Excel will think you mean the current worksheet.
如果您从与Sheet1您不同的工作表运行此操作，则会出现错误，因为Cells(65,1)引用不会打开Sheet1- Excel 会认为您指的是当前工作表。
The third part
第三部分
Set lookrange = Sheets(1).Range(Cells(2, 1), Cells(200, 1))
Set result = lookrange.Find(what:=searchTerm, lookat:=xlWhole)
Has the same issue as second part. Also note it is entirely possible you will run errors if resultcannot be found and the second statement above (the results of Find) will cause errors if Findis unsuccessful.
与第二部分有相同的问题。另请注意，如果result找不到，则完全有可能运行错误，并且Find如果Find不成功，上面的第二个语句（ 的结果）将导致错误。
回答by Daniel
Probably your range definition is not correct...Not sure what you are trying to achieve there but sheets(1).range(myrange) returns a range object.
可能你的范围定义不正确......不确定你想在那里实现什么，但 sheet(1).range(myrange) 返回一个范围对象。
Sub tetPop()
Call popAddress(10, 12)
Call popAddress(14, 21)
Call popAddress(11, 18)
End Sub
Sub popAddress(ByVal row As Integer, ByVal col As Integer)
Dim lr As Range
Set lr = Sheets(1).Range("a1", Cells(row, col))
MsgBox lr.Address
' or better and direct
MsgBox Sheets(1).Range("a1", Cells(row, col)).Address
End Sub
Would popup different address Cheers
会弹出不同的地址
Daniel
丹尼尔
回答by KekuSemau
The point is that lr = Sheets(1).Range("a1", Cells(65, 1))returns a range object.
Emphasis on object. So you need to use setat the start, as Daniel did.
As you assign that object without set, Excel uses the default property, which is Range.Value. So the assignment does not throw an error, only using lr as an object does.
关键是lr = Sheets(1).Range("a1", Cells(65, 1))返回一个范围对象。
强调对象。所以你需要set在开始时使用，就像丹尼尔所做的那样。
当您不带 分配该对象时set，Excel 使用默认属性，即 Range.Value。所以赋值不会抛出错误，只是将 lr 用作对象。
