Question

In this exercise, you will write expressions that refer to some of the objects in the Excel object model, which is shown in the following figure.

$wcsYHHRyqAvvQAAAABJRU5ErkJggg==$

1. How many collections are shown in the above figure?
2. For each of these collections, what type of objects are the members of this collection. For example, if the collection is people, the type of its member would be person?
3. What expression can be used to refer to the running Application, i.e. Microsoft Excel?
4. What expression can be used to refer to the Workbooks collection?
5. What expression can be used to refer to the Workbook object whose name is “My Workbook.xlsm”?
6. What expression can be used to refer to the second Worksheet object contained in the Workbook object whose name is “My Workbook.xlsm”?

Answer 1

Answer a) A collections is a group of same object in excel. In the diagram there are 2 collection, one is Workbooks which is a collection of workbook,and the second one is Worksheets which is a collection of worksheet. So Application is not a collection because it is a single object.

Answer b) In the Workbooks collection, the member object is Workbook and in the Worksheets collection the member object is Worksheet.

Answer c) The expression which is used is Application.Workbooks()

Answer d) Suppose we want to close the workbooks, so the expression will be Workbooks.Close()

Answer e) To refer to the workbook object we can use, suppose we want to close the workbook. Application.Workbooks("My Workbook.xlsm").Close

Here Application, then the workbooks collection, then the workbook, then the close method is called. So we can refer as Application.Workbooks("My Workbook.xlsm")

Answer f) We can refer like this way. We are writing a word data in A2 cell.

Application.Workbooks("My Workbook.xlsm").Worksheets(2).Range("A2") = "Data"

Here we are referring to second worksheet of "My Workbook.xlsm", here we are using Worksheets collection.