Excel (VBA) - Collection

2 - Methods

2.1 - Get

2.1.1 - Indexing

Return an object by index

The Item property of a collection returns a single object from that collection.

2.1.1.1 - Explicit
Set FirstBook = Workbooks.Item(1)

The Item property is the default property for most collections, so you can write the same statement more concisely by omitting the Item keyword.

Set FirstBook = Workbooks(1)
2.1.1.2 - Predefined

Some collections have predefined index values you can use to return single objects. Each predefined index value is represented by a constant. For example, you specify an XlBordersIndex constant with the Item property of the Borders collection to return a single border.

Worksheets("Sheet1").Range("A1:A1"). _ 
 Borders.Item(xlEdgeBottom).LineStyle = xlDouble

2.1.2 - Naming

Return an object by name

Before you can use a name with the Item property, you must name the object. Most often, this is done by setting the object's Name property.

ActiveWorkbook.Worksheets.Add.Name = "A New Sheet" 
With Worksheets("A New Sheet") 
 .Range("A5:A10").Formula = "=RAND()" 
End With
db/excel/collection.txt ยท Last modified: 2017/09/13 21:42 by gerardnico