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:
If Sheet1 is the first worksheet in the collection, you could also use the following reference:
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:
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:
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:
Search JabSto ::

Custom Search