Returning an Object from a Collection (Excel)

Office 2013 and later
GitHub-Mark-64px

Contribute to this content

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

The Item property of a collection returns a single object from that collection. The following example sets the firstBook variable to a Workbook object that represents the first workbook in the Workbooks collection.

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)

For more information about a specific collection, see the Help topic for that collection or the Item property for the collection.

Although you can usually specify an integer value with the Item property, it may be more convenient to 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. The following example creates a named worksheet in the active workbook and then refers to the worksheet by name.

ActiveWorkbook.Worksheets.Add.Name = "A New Sheet" 
With Worksheets("A New Sheet") 
 .Range("A5:A10").Formula = "=RAND()" 
End With

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.

The following example sets the bottom border of cells A1:G1 on Sheet1 to a double line.

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