is Cells collection or class?

[lezawang]

Hi
I read under worksheet object, cells is defined as the following

Property Cells As Range

Ok what I understand from the above statement is that Cells is an object of type Range, in another word Cells=Range. But what I do not understand, what does "property" mean here?

Also what "s" in Cells? s gives me impression it is Collection. Thank you very much

 

[Yongle]

Cells is NOT a RANGE
Cells is NOT a COLLECTON
Cells is a PROPERTY which returns a RANGE OBJECT

It is confusing because we are used to seeing abbreviated notation being used like this..

Cells [I]
OR[/I] 
ActiveSheet.Cells 
[I]OR[/I] 
ActiveSheet.Range("A1:XFD1048576")

instead of..

ActiveSheet.Range("A1:XFD1048576").Cells

- Cells is a property of the specified range

Run VBA below which helps illustrate this
- the first message box returns 4 identical values
- the other message box returns adresses of the first and last cell of range B11:T55
- the last line activates the last cell in range B11:T55

Sub CellsProperty()
Dim s As String, r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range
'all cells
    Set r1 = Cells
    Set r2 = ActiveSheet.Cells
    Set r3 = ActiveSheet.Range("A1:XFD1048576")
    Set r4 = ActiveSheet.Range("A1:XFD1048576").Cells

    s = r1.Address(0, 0) & vbCr & r2.Address(0, 0) & vbCr & r3.Address(0, 0) & vbCr & r4.Address(0, 0)
    MsgBox s, , "All Cells"
    
'first and last cell in range
    Set r5 = ActiveSheet.Range("B11:T55")
    MsgBox "First" & vbTab & r5.Cells(1, 1).Address(0, 0) & vbCr & "Last" & vbTab & r5.Cells(r5.Rows.Count, r5.Columns.Count).Address(0, 0), , "Range" & vbTab & r5.Address(0, 0)

'select final cell in range
    r5.Cells(r5.Rows.Count, r5.Columns.Count).Activate
End Sub

 

[Yongle]

And the last line

    r5.Cells(r5.Rows.Count, r5.Columns.Count).Activate

could be also be written as

    r5.Cells(r5.Cells.Rows.Count, r5.Cells.Columns.Count).Activate

 

[lezawang]

Thanks for your help. But Cells is property inside Worksheet? does that mean Cells is property of worksheet and range as well?

If I say

 workbooks(1).worksheets(1).cells(1,1).value="hello"

does the line above is like the line below.

 workbooks(1).worksheets(1).range("a1:xfd1048576").cells(1,1).value="hello"

If yes, does that mean if I do not type range("a1:xfd1048576"), excel going to imply that anyway so the first line basically is the second line even when I do not type range("a1:xfd1048576"). Thank you once again.

Sorry one more thing, why few people user

Sheets.range("??:??").cells(?,?)

compare to this

Sheets.cells(?,?)

 

[Yongle]

Cells is property inside Worksheet? does that mean Cells is property of worksheet and range as well?

- yes (although I tend to think of the worksheet Cells property as a property of the "RANGE OF ALL CELLS of the worksheet")

why few people use
Sheets.range("??:??").cells(?,?)
compare to this
Sheets.cells(?,?)

Those 2 would be identical only when Range("??:??") begins with cell A1

But it is very useful being able to refer to specific cells of a specified range like this

Sub CellsProperty()
    Const t = vbTab
    Const n = vbCr
    Dim rng As Range, a1$, a2$, a3$, b1$, b2$, b3$
    Set rng = ActiveSheet.[COLOR=#000080]Range("A10:Z20")[/COLOR]

'top left cell of the range
    a1 = rng.Cells(1, 1).Address(0, 0) & t
    'same as
    a2 = rng.Cells(1).Address(0, 0) & t
    'same as
    a3 = ActiveSheet.Range("A10:Z20").Cells(1, 1).Address(0, 0)
    
'bottom right cell of the range
    b1 = rng(rng.Rows.Count, rng.Columns.Count).Address(0, 0) & t
    'same as
    b2 = rng.Cells(rng.Rows.Count, rng.Columns.Count).Address(0, 0) & t
    'same as
    With ActiveSheet.Range("A10:Z20")
        b3 = .Cells(.Rows.Count, .Columns.Count).Address(0, 0)
    End With

    MsgBox a1 & a2 & a3 & n & b1 & b2 & b3, , rng.Address(0, 0)

End Sub

Do not get too hung up about this
- here are some interesting examples which all return CV100

Sub UnexpectedPerhaps()
    Const n = vbCr
    Dim c1$, c2$, c3$, c4$, c5$, c6$, c7$
    c1 = Range("A1:B1").Cells(100, 100).Address(0, 0) & n
    c2 = Range("A1").Cells(100, 100).Address(0, 0) & n
    c3 = ActiveSheet.Cells(100, 100).Address(0, 0) & n
    c4 = ActiveSheet.Range("A1:XFD1048576").Cells(100, 100).Address(0, 0) & n
    c5 = ActiveSheet.Range("A1:XFD1048576")(100, 100).Address(0, 0) & n
    c6 = [a1].Cells(100, 100).Address(0, 0) & n
    c7 = Cells(1).Cells(100, 100).Address(0, 0)
    MsgBox c1 & c2 & c3 & c4 & c5 & c6 & c7
End Sub

And in case you are wondering

Dim a1$

is the same as

Dim a1 As String

Run the above from Excel not from inside VBA window

 

[lezawang]

Thanks for your reply and help. Could you please tell me what address(0,0) is used for here, why it is needed here. Thank you very much.

c1 = Range("A1:B1").Cells(100, 100).Address(0, 0) & n

 

[Joe4]

.Address by itself returns an absolute range reference for the address, i.e. "$A$1:$B$1"
Adding the (0, 0) removes the absolute (dollar) signs, i.e. "A1:B1"

This is easy enough to see with a simply example.

Sub Test()


    Dim rng As Range
    Set rng = Range("A1:B1")
    
    MsgBox "Address without (0,0): " & vbCrLf & rng.Address
    MsgBox "Address with (0,0): " & vbCrLf & rng.Address(0, 0)
    
End Sub