Application.Cells VS Application.ActiveSheet.Cells [英] Application.Cells VS Application.ActiveSheet.Cells

问题描述

宏记录器生成以下语句:

The Macro Recorder generated the following statement:

Cells.Select

现在我了解到，没有对象限定符，它将以Range对象的形式返回所有单元格.

Now I understand that without the object qualifier this will return all the cells as a Range object.

但是，我想知道此声明的完全限定版本是什么吗?

However, I am wondering what the fully qualified version of this statement is?

是吗

1. Application.Cells.Select
2. Application.ActiveSheet.Cells
3. Application.ActiveWorkbook.ActiveSheet.Cells

1. Application.Cells.Select
2. Application.ActiveSheet.Cells
3. Application.ActiveWorkbook.ActiveSheet.Cells

换句话说，VBE在运行Cells.Select时实际上是由那些完全合格的语句中的哪一个执行的?

In other words, which one of those fully qualified statements is actually executed by VBE when it runs Cells.Select?

所有这些之间有什么区别???由于所有这些对象最终都访问同一个对象-如果我想显式限定所有对象，那么对使用哪种说法仅仅是个人喜好?

What is the difference between all of these??? As all of these access the same object in the end - is it just personal preference as to which statement I would use if I wanted to explicitly qualify all the objects?

非常感谢您！

推荐答案

这很复杂:)

因为所有这些对象最终都访问同一个对象

是的.关键字到底".区别在于到达那里需要多少步骤 ...

True. Keywords "in the end". The difference is how many steps it takes to get there...

不合格的Cells(或Range，Rows，Columns，Names等)不是魔术，它们是针对隐藏的全局范围的成员调用(Property Get)巧妙地命名为Global:

Unqualified Cells (or Range, Rows, Columns, Names, etc.) aren't magic, they're member calls (Property Get) against a hidden, global-scope object cleverly named Global:

的成员

您可以通过在标准模块中爆炸来验证是否包含此隐藏对象:

You can validate that this hidden object is involved, by blowing up in a standard module:

Sub GoesBoom()
    'throws error 1004 "Method 'Range' of object '_Global' failed"
    Debug.Print Range(Sheet2.Cells(1, 1), Sheet3.Cells(1, 1))
End Sub

_Global和Global是紧密相关的-无需深入研究COM，就可以考虑Global类和_Global它的接口(尽管实际上不是很像-查看"COM coClasses"有关更多信息).

_Global and Global are closely related - without diving deep into COM, you can consider Global the class, and _Global its interface (it's not really quite like that though - look into "COM coClasses" for more information).

但是Cells是Range类的属性:

我认为可以合理地假设Global调用几乎全部都重定向到Application，这会公开Global的所有成员，然后再公开一些.

I think it's reasonable to presume that Global calls are pretty much all redirected to Application, which exposes all members of Global, and then some.

现在您已经注意到，Application也具有Cells属性-但是Cells属于Worksheet，因此，无论我们做什么，我们都需要以Worksheet限定符结尾.然后，任何工作表都属于一个Worksheets集合，该集合属于一个Workbook对象-因此我们可以推断出，完全限定的Cells调用将等同于...(鼓式) :

Now as you noted, Application also have a Cells property - but Cells belong on a Worksheet, so no matter what we do, we need to end up with a Worksheet qualifier... and then any worksheet belongs in a Worksheets collection, which belongs in a Workbook object - so we can infer that an unqualified Cells call would be, in fully-explicit notation, equivalent to... (drumroll):

Application.ActiveWorkbook.ActiveSheet.Cells

但是您不必明确地显示 ，因为ActiveSheet的Parent总是将是ActiveWorkbook，因此这也是明确的，不会太过分:

But you don't need to be that explicit, because ActiveSheet has a Parent that is always going to be the ActiveWorkbook, so this is also explicit, without going overboard:

ActiveSheet.Cells

但这全都是假设全局的情况. 此答案解释了有关它的所有内容-要点是，如果您在工作表的代码背后，不合格的Cells成员呼叫不是Global.Cells，而是Me.Cells.

But that's all assuming global context. This answer explains everything about it - the gist of it, is that if you're in a worksheet's code-behind, then an unqualified Cells member call isn't Global.Cells, but Me.Cells.

现在，请注意，Cells返回一个Range.因此，无论何时在不提供参数的情况下针对Range调用它时，都在进行冗余成员调用:

Now, note that Cells returns a Range. Thus, whenever you invoke it against a Range without providing parameters, you're making a redundant member call:

ActiveSheet.Range("A1:B10").Cells ' parameterless Range.Cells is redundant

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