Range()vs Cells()vs Range.Cells() [英] Range() vs Cells() vs Range.Cells()

问题描述

我知道Range()和Cells()属性是访问工作表上单元格的等效方法.但是，何时在此组合中使用Range.Cells()是明智的选择?

我遇到了一个他们使用Range("A1").Resize(2,3).cells.value的示例. 这等同于Range("A1").Resize(2,3).value吗?

如果没有，前者的优势是什么?

解决方案

从技术上讲，Range和Range.Cells不等效.有一个很小但很重要的区别.

但是在您的特定情况下(在您所在的位置)

1. 使用Range("something")构建范围，然后
2. 仅对该范围的.Value感兴趣

完全没有区别.

---

VB中有一个方便的子句，For Each，它枚举了集合中的所有元素.在Excel对象模型中，有方便的属性，例如Columns，Rows或Cells，它们返回各个单元格跨度的集合:列的集合，行的集合或单元格的集合. /p>

根据语言的流动方式，您自然希望For Each c In SomeRange.Columns一次枚举列，而For Each r In SomeRange.Rows一次枚举行.实际上，他们就是这样做的.
但是您会注意到，Columns属性返回Range，而Rows属性也返回Range.但是，前一个Range会告诉For Each它是列的集合"，而后一个Range会将其自身介绍为行的集合".

之所以可行，是因为显然在Range类的每个实例中都有一个隐藏的标志，该标志决定了此操作的方式. Range的实例将在For Each内部起作用.

从Range中查询Cells可以确保您获得Range的实例，该实例的For Each枚举模式设置为单元".如果您不打算开始For Each的范围，那么这种差异对您没有任何影响.

即使您确实关心For Each模式，在特定情况下Range("A1").Resize(2,3)和Range("A1").Resize(2,3).Cells也是相同的，因为默认情况下Range是使用单元格"枚举模式构造的，并且Resize不会更改其调整大小的范围的枚举模式.

---

因此，我唯一能想到的是从一个已经存在的Range中查询Cells的地方会有所作为，是当您有一个接受Range作为参数的函数时，您不知道Range的构造方式，您想枚举该范围内的单个单元格，并且要确保要枚举的是For Each单元格，而不是行或列:

function DoSomething(byval r as range)
  dim c as range

  'for each c in r ' Wrong - we don't know what we are going to enumerate

  for each c in r.cells ' Make sure we enumerate cells and not rows or columns (or cells sometimes)
    ...
  next
end function

I know that the Range() and Cells() properties are equivalent methods of accessing cells on a worksheet. However, when is it wise to use Range.Cells() in this combination?

I came across an example where they used Range("A1").Resize(2,3).cells.value. Would this be equivalent to Range("A1").Resize(2,3).value?

If not, what is the advantage of the former?

解决方案

Technically, Range and Range.Cells are not equivalent. There is a small but important difference.

However in your particular case, where you

1. Construct the range with Range("something"), and
2. Are only interested in the .Value of that range,

it makes no difference at all.

---

There is a handy clause in VB, For Each, that enumerates all elements in a collection. In the Excel object model, there are convenient properties such as Columns, Rows, or Cells, that return collections of respective cell spans: a collection of columns, a collection of rows, or a collection of cells.

From how the language flows, you would naturally expect that For Each c In SomeRange.Columns would enumerate columns, one at a time, and that For Each r In SomeRange.Rows would enumerate rows, one at a time. And indeed they do just that.
But you can notice that the Columns property returns a Range, and the Rows property also returns a Range. Yet, the former Range would tell the For Each that it's a "collection of columns", and the latter Range would introduce itself as a "collection of rows".

This works because apparently there is a hidden flag inside each instance of the Range class, that decides how this instance of Range will behave inside a For Each.

Querying Cells off a Range makes sure that you get an instance of Range that has the For Each enumeration mode set to "cells". If you are not going to For Each the range to begin with, that difference makes no difference to you.

And even if you did care about the For Each mode, in your particular case Range("A1").Resize(2,3) and Range("A1").Resize(2,3).Cells are the same too, because by default the Range is constructed with enumeration mode of "cells", and Resize does not change the enumeration mode of the range it resizes.

---

So the only case that I can think of where querying Cells from an already existing Range would make a difference, is when you have a function that accepts a Range as a parameter, you don't know how that Range was constructed, you want to enumerate individual cells in that range, and you want to be sure that it's cells For Each is going to enumerate, not rows or columns:

function DoSomething(byval r as range)
  dim c as range

  'for each c in r ' Wrong - we don't know what we are going to enumerate

  for each c in r.cells ' Make sure we enumerate cells and not rows or columns (or cells sometimes)
    ...
  next
end function

这篇关于Range()vs Cells()vs Range.Cells()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！