vba Range.Item 和 Range.Cells 有什么区别？

What's the difference between Range.Item and Range.Cells?

提问by feelthhis

For example, in the code below, Itemand Cellscan be used interchangeably:

例如，在下面的代码，Item并且Cells可以互换使用：

Dim rRange As Range
Set rRange = ThisWorkbook.ActiveSheet.Range("A1")

With rRange
    Debug.Print .Item(1, 1).Value   ' Outputs value of "A1"
    Debug.Print .Cells(1, 1).Value  ' Outputs value of "A1"

    Debug.Print .Item(2, 1).Value   ' Outputs value of "A2"
    Debug.Print .Cells(2, 1).Value  ' Outputs value of "A2"
End With

---

---

In the developer reference, they are defined as:

在开发人员参考中，它们被定义为：

Range.Item Property (Excel)

Range.Item 属性 (Excel)

Returns a Range object that represents a range at an offset to the specified range.

返回一个 Range 对象，该对象表示与指定范围偏移的范围。

~

~

Range.Cells Property (Excel)

Range.Cells 属性 (Excel)

Returns a Range object that represents the cells in the specified range.

Remarks

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword.

返回一个 Range 对象，该对象表示指定范围内的单元格。

评论

因为 Item 属性是 Range 对象的默认属性，所以您可以在 Cells 关键字之后立即指定行和列索引。

From that remark, does it mean that Cells(1, 1)is actually a short for Cells.Item(1, 1)? Thus Cells(1, 1)is actually equivalent to Item(1, 1)? What am I missing?

从那句话中，这是否意味着Cells(1, 1)实际上是 的缩写Cells.Item(1, 1)？因此Cells(1, 1)实际上相当于Item(1, 1)? 我错过了什么？

采纳答案by Siddharth Rout

The best way to understand this is via the below example

理解这一点的最佳方法是通过以下示例

When .Itemand .Cellsare used with respect to a range then, YES, they are same. For example

当.Item和.Cells用于一个范围时，是的，它们是相同的。例如

Sub Sample()
    Dim rRange As Range
    Set rRange = ThisWorkbook.ActiveSheet.Range("B1:C10")

    With rRange
        Debug.Print .Item(1, 3).Address '<~~ $D
        Debug.Print .Cells(1, 3).Address '<~~ $D
    End With
End Sub

In the above they both depict the address of the cell in that Range

在上面它们都描述了单元格的地址 Range

They are different when Cells()is used independently of a range.

当Cells()独立于范围使用时，它们是不同的。

Sub Sample()
    Dim rRange As Range
    Set rRange = ThisWorkbook.ActiveSheet.Range("B1:C10")

    With rRange
        Debug.Print .Item(1, 3).Address  '<~~ $D
        '~~> DOT before Cells missing
        Debug.Print Cells(1, 3).Address  '<~~ $C
    End With
End Sub

In the above .Itemdepicts the address of the cell in that Range, where as Cellsdepicts the address of the cell in the ActiveSheet

在上面.Item描绘的单元格的地址中Range，其中 asCells描绘的单元格的地址在ActiveSheet

回答by Alan Elston

I can see no use of the Cells Property other than for referring to all the cells in a Worksheet. I would put it like this, ( based on my experiments and research) :

除了引用工作表中的所有单元格之外，我看不到 Cells 属性的任何用途。我会这样说，（基于我的实验和研究）：

The Cells Property returns a Range Object containing all the cells of the Object to which it is applied. For the case of a Worksheet it is clearly useful so as to be able to use all the Properties of the Range Object on the entire Worksheet. For the case of a Range Object I am not sure if it has any use. I cannot find, for example, any extra Property that I cannot get from the Range Object's Properties without first applying the Cells Property to that Range Object

Cells 属性返回一个范围对象，其中包含应用它的对象的所有单元格。对于工作表的情况，它显然很有用，以便能够在整个工作表上使用范围对象的所有属性。对于 Range 对象的情况，我不确定它是否有任何用处。例如，如果不首先将 Cells 属性应用于该 Range 对象，我就无法找到无法从 Range 对象的属性中获取的任何额外属性

I am wondering if some of its use in code has just “crept in” over the years. For example , I believe this type of code line is one possible Explicit way to refer to the Range Object of the second cell in a worksheet using the Range Object Item Property Ws.Range("A1").Areas.Item(1).Item(1, 2) Along the lines of what feelthhis said, I can rely on the implied implicit of first Area and Default Property of Item to rewrite that code line thus: Ws.Range("A1")(1, 2)

我想知道这些年来它在代码中的某些用途是否刚刚“悄悄进入”。例如，我相信这种类型的代码行是使用范围对象项属性 Ws.Range("A1").Areas.Item(1) 引用工作表中第二个单元格的范围对象的一种可能的显式方式。 Item(1, 2) 按照感觉上所说的，我可以依靠隐含的第一个区域和 Item 的默认属性来重写该代码行： Ws.Range("A1")(1, 2)

If I drop in a Cells Property , I do no harm Ws.Range("A1").Cells(1, 2)

如果我加入 Cells Property ，我不会伤害 Ws.Range("A1").Cells(1, 2)

But I would suggest: _(i) use of Cells here is totally redundant _(ii) I am still using here the Range Item Property. _(iii) There is no Cells Item Property _(iv) A code part like this Cells(1, 2) has “crept in” as a so called “Cells Property that will accept one or two arguments........”..and/ or ...”cells has a Item Property...”...etc... ,. I think those statements may be incorrect. I think the Cells Property has no argument. ( I am not too sure if Cells may have an Item Property. I am not a computing professional but experts have told me that intellisense offering it , or Microsoft Help suggesting it is no guarantee that it exists. I expect there is no Cells Item Property)

但我建议：_(i) 在这里使用 Cells 是完全多余的 _(ii) 我仍然在这里使用 Range Item 属性。_(iii) 没有 Cells 项属性 _(iv) 像 Cells(1, 2) 这样的代码部分已经“悄悄进入”作为所谓的“ Cells 属性”，它将接受一个或两个参数...... ..”..and/ or ...”单元格有一个 Item 属性.. ..”...etc... ,. 我认为这些陈述可能是不正确的。我认为 Cells 属性没有任何争议。（我不太确定 Cells 是否可能有一个 Item 属性。我不是计算专家，但专家告诉我，intellisense 提供它，或者 Microsoft 帮助暗示它不能保证它存在。我希望没有 Cells Item 属性)

In all case a code part like this, Cells(1, 2), is explained as follows: Cells is returning a Range Object. That Range Object is having its default Property, the Range Object Item Property applied to it. Unless I am using Cells independently to a Range, I should probably omit it. I suggest this as, in this case, I am not being explicit. Rather I am contributing to a, possibly false, idea that I can refer to a Range Object via a _.. _.. “Cells(_argument/s_) type” Property, .. which possibly does not exist.

在所有情况下，像 Cells(1, 2) 这样的代码部分解释如下： Cells 返回一个 Range 对象。该 Range 对象具有其默认属性，即应用于它的 Range 对象项属性。除非我将单元格独立用于一个范围，否则我应该忽略它。我建议这样做，因为在这种情况下，我不是明确的。相反，我提出了一个可能是错误的想法，即我可以通过 _.. _.. “ Cells(_argument/s_) 类型”属性来引用范围对象，这可能不存在。

Alan

艾伦