Objects as a collection, and changing the attributes of the entire collection

This recipe will take us one step further down the path of discovering objects.

It is common knowledge that a workbook is made up of any number of worksheets. These worksheet objects are of the same type and form a collection. Collections are objects in themselves.

There are, of course, more objects that form collections than just worksheets. Here is a list of other commonly used workbook objects:

• Workbooks: All currently open workbook objects form a collection.
• Worksheets: All worksheet objects contained in a particular workbook object constitute another collection.
• Charts: All chart objects/chart sheets contained in a particular workbook object are yet another collection.
• Sheets: All sheets, regardless of type, contained in a particular workbook object also form a collection.

Having this understanding of collections will be useful for all future recipes.

Getting ready

Open Excel and make sure that Book1 is active.

How to do it…

Now let's proceed with the following steps:

1. With Book1 open, insert three more sheets by clicking on the add button (+) to the right of Sheet1. We need a total of four sheets in Book1.
2. Now, create a new workbook – Book2. Leave it with only one worksheet.
3. Once done, activate the VBA Editor by pressing Alt + F11.
4. The Project window displays all the recently created objects. It is important to take note of the collections. Book1 and Book2 are in bold because they belong to the same collection. Similarly, all the sheets belong to another collection:
   

   Figure 3.2 – Objects in the Project window

5. While still in the VBA Editor, select Book1. Then, on the menu bar, click Insert | Module.
6. In the code window for Module1, type the following code:

   Sub ChangeFont()

         Sheets(Array("Sheet1", "Sheet2", "Sheet3", _

         "Sheet4")).Select

         Sheets("Sheet1").Activate

         Cells.Select

         With Selection.Font

               .Name = "Arial"

               .Size = 12

         End With

         Selection.Font.Size = 11

         Range("A1").Select

   End Sub

7. Once done, run the macro. (To do this, press F5 and then Alt + F11 to switch back to Excel. Alternatively, press Alt + F11 to switch back to Excel, and then click Developer Tab | Code Window | Macros | Run Macro | ChangeFont).
8. Observe that all four sheets on Book1 have been selected, and that the font for each sheet is now Arial set to size 11.

How it works…

When we run the ChangeFont macro, the following happens:

1. Line one of the code selects and groups all four sheets in Book1. At the same time, Book1 is activated. Because these sheets are grouped, any action taken on Sheet1 will also be applied to the rest of the group.
2. Next, all cells on the sheets are selected.
3. The font is changed to Arial, while the font size stays at 12 points for the moment.
4. Now, the size is reduced to 11 points.
5. Finally, cell A1 is selected.

You can check any of the four sheets to confirm that the font has been changed to Arial, size 11.