vba 为什么 Range 有效，而 Cell 无效？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17733541/
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
Why does Range work, but not Cells?
提问by user2597163
I'm trying to move some data from one workbook into another by assigning the values from one range to another. When I use the normal Range syntax to specify the destination range (Range("A1:B2")) my code works, but if I try to use the Range, Cells syntax (Range(Cells(1,1),Cells(2,2))) my code doesn't work.
我试图通过将值从一个范围分配到另一个范围来将一些数据从一个工作簿移动到另一个工作簿。当我使用正常的 Range 语法来指定目标范围 (Range("A1:B2")) 时，我的代码可以工作，但是如果我尝试使用 Range, Cells 语法 (Range(Cells(1,1),Cells(2) ,2))) 我的代码不起作用。
I activate the destination workbook (ActiveWorkbook) and have the code running in the source workbook (ThisWorkbook).
我激活目标工作簿 (ActiveWorkbook) 并使代码在源工作簿 (ThisWorkbook) 中运行。
This code works:
此代码有效：
ActiveWorkbook.Worksheets(1).Range("A1:B2").Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value
But This code does not:
但是这段代码没有：
ActiveWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value
The error I get is Run-time error '1004': Applicaton-defined or object-defined error.
我得到的错误是运行时错误“1004”：应用程序定义或对象定义错误。
Does anyone know why using the cells object is causing me problems, or if there is some other problem I'm not aware of?
有谁知道为什么使用 cell 对象会导致我出现问题，或者是否还有其他一些我不知道的问题？
回答by Dick Kusleika
The problem is that Cellsis unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Rangeor Cellsor Rowsor UsedRangeor anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to:
问题是这Cells是不合格的，这意味着这些单元格所引用的工作表因您的代码所在的位置而异。任何时候你调用RangeorCells或RowsorUsedRange或任何返回 Range 对象的东西，并且你没有指定它在哪个工作表上，工作表会根据以下情况进行分配：
- In a sheet's class module: that sheet regardless of what's active
- In any other module: the ActiveSheet
- 在工作表的类模块中：该工作表与活动无关
- 在任何其他模块中：ActiveSheet
You qualify the Rangereference, but the Cellsreference is unqualified and is likely pointing to the Activesheet. It's like writing
您限定了Range引用，但Cells引用未限定并且可能指向 Activesheet。这就像写作
ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value
which of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure everything is fully qualified.
这当然没有任何意义，除非 ThisWorkbook.Worksheets(1) 恰好处于活动状态。我经常喜欢使用 With 块，以确保一切都是完全合格的。
With Sheets(1)
.Range(.Cells(1,1), .Cells(2,2)).Value = "something"
End With
But you refer to two different sheets, so you'll be better off using short sheet variables like:
但是您指的是两个不同的工作表，因此最好使用短工作表变量，例如：
Dim shSource As Worksheet
Dim shDest As Worksheet
Set shSource = ThisWorkbook.Worksheets(1)
Set shDest = Workbooks("myBook").Worksheets(1)
shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _
shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value
But really, if you're going to hardcode the Cellsarguments, you could clean that up like
但实际上，如果你要对Cells参数进行硬编码，你可以像这样清理它
shDest.Cells(1, 1).Resize(2, 2).Value = shSource.Cells(1, 1).Resize(2, 2).Value
