Range.Paste-对象不支持此属性或方法 [英] Range.Paste - Object doesn't support this property or method

问题描述

我有一个非常简单的过程，可以从一个工作簿复制一个范围并将其粘贴到另一个工作簿中.问题是，我在paste行的标题中遇到了错误.

I've got a very simple procedure that copies a range from one workbook and pastes it into another; the issue is, I'm getting the error in the title on the paste line.

下面是代码:

Sub copypasta()
Dim x As Workbook
Dim y As Workbook

Set x = ActiveWorkbook
Set y = Workbooks.Open("F:\Target\FTB\FTB.xlsx")

x.Sheets(1).Range("A1").CurrentRegion.Copy

y.Sheets("DTR").Cells.Delete '<~~~No Error Here...?
y.Sheets("DTR").[a1].Paste   '<~~~Error Here

我以为删除工作正常而感到奇怪，但粘贴无效.

I thought it was strange that the deletion works fine, but paste does not.

似乎我可能缺少粘贴方法的一些基础知识-有人可以解释一下我在这里缺少什么吗?

It seems that I may be missing some fundamentals of the paste method - could someone please explain what I'm missing here?

推荐答案

其他答案提供了使其工作的方法，但没有解释发生了什么.

Other answers have provided ways to make it work, but don't explain what's going on.

y.Sheets("DTR")

这实际上应该是y.Worksheets("DTR")，因为Sheets集合可以包含非工作表项，例如图表.

That should really be y.Worksheets("DTR"), because the Sheets collection can contain non-worksheet items, such as charts for example.

无论如何，Sheets和Worksheets集合的Item属性(任何集合类型的 default属性)都会产生Object，这使得每个链接的成员接下来的电话，就是一个迟到电话.

Regardless, both the Sheets and the Worksheets collections' Item property (which is the default property of any collection type) yields an Object, which makes every chained member call that follows, a late-bound call.

由于后期调用，您不会获得 IntelliSense ，因为根据定义，这些调用会在运行时得到解决:您可以在Object，并且代码将愉快地编译:

And you don't get IntelliSense on late-bound calls, since these calls get resolved at run-time, by definition: you can call anything on an Object, and the code will happily compile:

Sub Test()
    Debug.Print ThisWorkbook.Worksheets(1).Whatever
End Sub

与:

Sub Test()
    Debug.Print ThisWorkbook.Worksheets.Item(1).Whatever
End Sub

在运行时，如果VBA在检索到的对象的接口上找不到Whatever成员，则会引发运行时错误438，对象不支持此属性或方法".

At run-time, if VBA can't find the Whatever member on the retrieved object's interface, it raises run-time error 438, "Object doesn't support this property or method".

后期绑定功能强大且非常有用，但是它也会产生不必要的开销，您不一定需要.

Late-binding is powerful and very useful, but it also incurs overhead that you don't necessarily need.

代替处理Object，您可以将返回的对象引用投射到特定类型，当您知道该类型是什么时-在这种情况下，我们知道我们正在处理Worksheet对象:

Instead of working off an Object, you can cast the returned object reference to a specific type when you know what that type is - in this case we know we're dealing with a Worksheet object:

Dim target As Worksheet
Set target = y.Worksheets("DTR")

现在您有了一个早期绑定 Worksheet对象引用， IntelliSense 可以指导您:

Now that you have an early-bound Worksheet object reference, IntelliSense can guide you:

如果您尝试调用虚假成员(例如target.Whatever)，则会收到编译时错误，而不是运行时错误.

And if you try to call a bogus member (e.g. target.Whatever), you'll get a compile-time error instead of a run-time error.

执行此操作时:

target.[A1].Paste

您再次使用后期绑定来检索A1范围.相反，调用Worksheet.Range属性getter来检索早期绑定的Range对象-从那里您会看到，当您键入.paste时，在Range中没有诸如Paste方法之类的东西.对象:

You're using late-binding again, to retrieve the A1 range. Instead, call the Worksheet.Range property getter to retrieve an early-bound Range object - and from there you'll see that when you type .paste there is no such thing as a Paste method in a Range object:

键入时，您会得到所有内容的自动补全和工具提示:

And you get autocompletion and tooltips for everything as you type it:

这篇关于Range.Paste-对象不支持此属性或方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！