Object Hierarchy
Now that you have seen the Range object, you can understand the Workbook and Worksheet object better. In Excel Visual Basic each object can contain another object, and that object can contain another object, etc. In other words, Excel VBA programming involves working with an object hierarchy. This probably sounds quite confusing, but we will make it clear.
The mother of all objects is Excel itself. We call it the Application object. The application object contains other objects. An example of an object of the Application object is the Workbook object (Excel File). This can be any workbook you have created. The Workbook object contains other objects, such as the Worksheet object. The Worksheet object contains other objects, such as the Range object.
We have used the following code line a lot:
Range("A1").Value
but what we really meant was cell A1 on the first worksheet of Book1. Thus we should actually add the following line in Excel VBA:
Application.Workbooks("Book1").Worksheets(1).Range("A1").Value
Fortunately we do not have to add a code line this way. This is because Excel Visual Basic knew we meant Book1 and the first worksheet because we placed our command button there (remember?). Now also remember the automatically created module when we recorded a macro with the Excel Macro Recorder. Code placed into a module is available to all workbooks and worksheets.
Place the Sub test into a module (In the Visual Basic Editor, click on Insert and then Module).
Sub test()
Range("A1").Value = "code placed here"
End Sub
1. Execute the code (Click on Macros and then Run, or click on Run from the Visual Basic Editor). The words "code placed here" will be placed into cell A1. No surprise.
2. Now go to the second worksheet. Execute the code again. You will see that the words will be placed on the second worksheet as well!
3. Now even open a new workbook and execute the macro again. You will see that the words will be placed there as well! That is because we didn't specify a workbook or worksheet name and Excel VBA automatically takes the active workbook and active worksheet. Be aware that if you want to change different things on different sheets to include the Worksheet object.
Properties and methods of the Workbook and Worksheet object
You may have noticed that worksheets and workbooks are both plural (see the complete code line mentioned earlier). That's because they are actually collections. The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection contains all the Worksheet objects in a workbook.
You can refer to a member of the collection, that is: a single workbook or single worksheet, in two ways. Using the index number, Worksheets(1) is the first worksheet starting from the left. Using the member's name: Worksheets("sheet1").
It probably doesn't surprise you that the collections and the members of the collections have properties and methods as well. Here are some examples.
1. The Count property of the Worksheets collection and Workbooks collection. The following code line counts the number of worksheets of a workbook. Place a command button on your worksheet and add the code line:
MsgBox Worksheets.Count
Result when you click the command button on the sheet:
You can also use the Count property to count the number of active workbooks.
2. The Add method of the Workbooks collection and Worksheets collection. The following code line creates a new worksheet.
Worksheets.Add
You can also use the Add method to add a new workbook.
3. The Worksheet object contains more interesting collections, such as the Rows collection. In Excel VBA you can use the Select method to select a row. The code line below selects row 2.
Worksheets(1).Rows(2).Select
In a similar way, you can select a column. The code line below selects column 7.
Worksheets(1).Columns(7).Select
