Welcome to the Microsoft Office Excel 2007 Developer Reference
This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing
solutions based on Excel.
Press <Alt > < F11 > to view Visual Basic Window
OR Right Click on Sheet tab and click “View Code”
Returning an Object from a Collection
The Item property returns a single object from a collection. The following example sets the firstBook variable to a
Workbook object that represents workbook one.
Set FirstBook = Workbooks.Item(1)
The Item property is the default property (default property: A property that you can set for a control so that each time a
new control of that type is created, this property will have the same value.) 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
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()"
Predefined Index Values
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.