Microsoft Office Tutorials and References
In Depth Information
More about collections
More about collections
As I noted, a collection is a group of objects of the same class. A collection is itself
an object, and you can work with the entire collection or with an individual object
in a collection. To reference a single object from a collection, you put the object’s
name or index number in parentheses after the name of the collection, like this:
Charts(“Chart1”)
If Chart1 is the first Chart sheet in the collection, you may also use the
following reference:
Charts(1)
You refer to the second Chart sheet in a Workbook as Charts(2) , and so on.
There is also a collection called Sheets , which is made up of all sheets in a
workbook, whether they’re worksheets or Chart sheets. If Chart1 is the first sheet in
the workbook, you can reference it as follows:
Sheets(1)
Referring to objects
When you refer to an object using VBA, you often must qualify the object by
connecting object names with a period (also known as a “dot operator”). What if you
had two workbooks open and they both had a worksheet named Sheet1 ? The
solution is to qualify the reference by adding the object’s container , like this:
Workbooks(“Book1”).Worksheets(“Sheet1”)
Without the workbook qualifier, VBA would look for Sheet1 in the active
workbook.
To refer to a specific range (such as cell A1) on a worksheet named Sheet1 in a
workbook named Book1, you can use the following expression:
Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)
The fully qualified reference for the preceding example also includes the
Application object, as follows:
Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)
Search JabSto ::




Custom Search