Application.Cells VS Application.ActiveSheet.Cells

Question

The Macro Recorder generated the following statement:

Cells.Select

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?

Is it:

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

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?

Thank you so much!

Answer 1

Let's take the post apart:

Cells.Select

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

That's actually somewhat incorrect. While it is true that .Cells returns a Range.Cells object which returns all the cells, Cells.Select is actually a method of the Range object which - as you may have guessed - Selects the range (in our case, all the cells)

The Select method, as per MSDN actually returns a Variant and not a Range object. That it is a pretty important distinction to make, especially if you plan on passing that value to anything. So if we pretended to be a compiler

1. Cells -> ActiveWorkbook.ActiveSheet.Range.Cells returns Range of all the cells
2. Range.Cells.Select -> first we take our returned Range, we then select the cells in Worksheet and actually return a Variant (not Range)

As to the other part of the question. It depends where your module is placed. By default, Cells is shorthand for the following statement:

Application.ActiveWorkbook.ActiveSheet.Range.Cells

This however is subject to change depending on where your module is placed and if Application, workbook or sheet has been modified.

---

In general, it is a good coding practice to always specify at least a specific Worksheet object whenever you're referencing a Range, eg.

 Sheets("Sheet1").Range.Cells 

This is explicit and therefore less error prone and clearer to comprehend, be it for you or anyone working with your code.. You always know what exactly you're working with and not leave it to guesswork.

Obviously, the moment you start working with multiple workbooks, it's a good idea to incorporate Workbook objects statements before the Sheet. You get my point.

---

Last but not least, whatever you're trying to do, it's probably for the best you avoid using Select. It's generally not worth it and prone to unexpected behaviour.

Check this question here: How to avoid using Select in Excel VBA

Answer 2

It's complicated :)

As all of these access the same object in the end

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

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 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).

But Cells is a property of the Range class:

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.

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

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

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.

---

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

Answer 3

If you just type Cells - in and of itself it does nothing. It is the same as Range.Cells. The only advantage of Cells is that it can accept numeric value for column (second argument). It's very handy when you do complex manipulations.

Range.Cells just returns Range object. When you have Range object, think of it as a small Excel worksheet. Say, you have range Range("F3:J10"). Then following ranges all refer to H3 cell:

1. Range("F3:J10").Cells(3)
2. Range("F3:J10")(3)
3. Range("F3:J10").Cells(1, 3)
4. Range("F3:J10")(1, 3)
5. Range("F3:J10").Cells(1, "C")
6. Range("F3:J10")(1, "C")
7. Range("F3:J10").Range("C1")