Range.Cells vs Range.Index

[sijpie]

I was digging into VBA to see how I could optimise things, and discovered several ways of accessing cells of a range, two of which seem to be identical. Does someone know the difference?

Range("A1:G20").Item(2,4)
' is the same as 
Range("A1:G20").Cells(2,4)
'both pointing to cell D2

In fact I think the quickest notation to the top-left cell of a range is:

Range("MyRange").Item(1)

 

[pgc01]

Hi sijpie

.Cells gives you the cells in the range, so

Range("A1:G20")
Range("A1:G20").Cells

are the same thing.

The .Item() property is the default property for the range in this case, so

Range("A1:G20").Item(2, 4)
Range("A1:G20").Cells.Item(2, 4)

is the same as

Range("A1:G20")(2, 4)
Range("A1:G20").Cells(2, 4)

Since .Item() is the default property in this case, if you don't want you don't write it, the vba will automatically use it with the range.

Remark: many programmers think that you should always make explicit reference to a property, be it a default property or not.

 

[sijpie]

Thanks for that clear explanantion!