Excel Easy
#1 Excel tutorial on the net Excel Introduction Basics Functions Data Analysis VBA 300 Examples Ask us
Workbook and Worksheet Object Search Entire Site Go
Object Hierarchy | Collections | Properties and Methods
Chapter < >
Learn more about the Workbook and Worksheet object in Excel VBA.
Workbook and Worksheet Object
Object Hierarchy
In Excel VBA, an 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. Learn more, it's easy
The mother of all objects is Excel itself. We call it the Application object. The application object contains other Path and FullName
objects. For example, 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 Close and Open
objects, such as the Range object.
Loop through Books and Sheets
Sales Calculator
The Create a Macro chapter illustrates how to run code by clicking on a command button. We used the
following code line:
Files in a Directory
Range("A1").Value = "Hello"
Import Sheets
but what we really meant was:
Programming Charts
Application.Workbooks("createamacro").Worksheets(1).Range("A1").Value = "Hello"
Note: the objects are connected with a dot. Fortunately, we do not have to add a code line this way. That is
because we placed our command button in createamacro.xls, on the first worksheet. Be aware that if you want
Download Excel File
to change things on different worksheets, you have to include the Worksheet object. Read on.
workbookworksheetobject.xls
Collections
You may have noticed that Workbooks and Worksheets are both plural. That is because they are collections.
The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection Follow Excel Easy
contains all the Worksheet objects in a workbook.
You can refer to a member of the collection, for example, a single Worksheet object, in three ways.
1. Using the worksheet name.
Worksheets("Sales").Range("A1").Value = "Hello"
2. Using the index number (1 is the first worksheet starting from the left).
Worksheets(1).Range("A1").Value = "Hello"
3. Using the CodeName.
Sheet1.Range("A1").Value = "Hello"
To see the CodeName of a worksheet, open the Visual Basic Editor. In the Project Explorer, the first name is the
CodeName. The second name is the worksheet name (Sales).
Note: the CodeName remains the same if you change the worksheet name or the order of your worksheets so
this is the safest way to reference a worksheet. Click View, Properties Window to change the CodeName of a
worksheet. There is one disadvantage, you cannot use the CodeName if you reference a worksheet in a
different workbook.
Properties and Methods
Now let's take a look at some properties and methods of the Workbooks and Worksheets collection.
Properties are something which an collection has (they describe the collection), while methods do something
(they perform an action with an collection).
Place a command button on your worksheet and add the code lines:
1. The Add method of the Workbooks collection creates a new workbook.
Workbooks.Add
Note: the Add method of the Worksheets collection creates a new worksheet.
2. The Count property of the Worksheets collection counts the number of worksheets in a workbook.
MsgBox Worksheets.Count
Result when you click the command button on the sheet:
Note: the Count property of the Workbooks collection counts the number of active workbooks.
1/8 Completed! Learn more about books and sheets >
Back to Top: Workbook and Worksheet Object | Go to Next Chapter: Range Object
EXCEL EASY • FREE EXCEL HELP • COPYRIGHT (C) 20102017 • ALL RIGHTS RESERVED
MICROSOFT EXCEL TUTORIAL | HOW TO USE VBA | EXCEL FORMULAS | DROPDOWN LIST
