Microsoft Office Tutorials and References
In Depth Information
About Objects and Collections
About collections
Another key concept in VBA programming is collections. A collection is a group of objects of the
same class, and a collection is itself an object. As I note earlier, Workbooks is a collection of all
Workbook objects currently open. Worksheets is a collection of all Worksheet objects
contained in a particular Workbook object. You can work with an entire collection of objects 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:
Worksheets(“Sheet1”)
If Sheet1 is the first worksheet in the collection, you could also use the following reference:
Worksheets(1)
You refer to the second worksheet in a Workbook as Worksheets(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 Sheet1 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”)
 
Search JabSto ::




Custom Search