Workbook and Worksheet Object

Object Hierarchy | Collections | Properties and Methods

Learn more about the Workbook and Worksheet object in Excel VBA.

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.

The mother of all objects is Excel itself. We call it the Application object. The application object contains other 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 objects, such as the Range object.

The Create a Macro chapter illustrates how to run code by clicking on a command button. We used the following code line:

but what we really meant was:

Application.Workbooks(“create-a-macro”).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 create-a-macro.xls, on the first worksheet. Be aware that if you want to change things on different worksheets, you have to include the Worksheet object. Read on.

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 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.

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:

Result when you click the command button on the sheet:

Note: the Count property of the Workbooks collection counts the number of active workbooks.

Path and FullName

The Path property in Excel VBA returns the complete, saved path to the workbook (Excel file). The FullName property in Excel VBA returns the complete, saved path, including the name of the workbook.

Download path-fullname.xls and add it to “C:test”

Place a command button on your worksheet and add the following code lines:

1. The following code line returns the complete path to path-fullname.xls.

Result:

2. The following code line returns the complete path, including the name of the active workbook.

Result:

For a practical example of the FullName property, see our example program Create a Footer Before Printing.

Close and Open

The Close and Open Method in Excel VBA can be used to close and open workbooks. Remember, the Workbooks collection contains all the Workbook objects that are currently open.

Place a command button on your worksheet and add the following code lines:

Note: you can only open sales.xls without specifying the file’s path if it’s stored in your default file location. The default file location is the folder you see when you open or save a file.

6. You can also use the GetOpenFilename method of the Application object to display the standard open Dialog box and select the file (without actually opening the file).

Result:

7. Next, you can open the workbook as usual.

Loop through Books and Sheets

Below we will look at a program in Excel VBA that loops through all open workbooks and worksheets, and displays all the names.

Situation:

Add the following code lines to the command button:

1. First, we declare two objects and one variable. One object of type Workbook we call book, one object of type Worksheet we call sheet, and a variable of type String we call text.

10. Test the program. Before you click on the command button, give your worksheets some descriptive names and open another blank workbook.
Result:

Sales Calculator

Below we will look at a program in Excel VBA that calculates the total sales of each employee over a period of three years.

Situation:

The other two sheets have the same setup, but with different combinations of months and employees, and different sales numbers. There are several ways to calculate the total sales of each employee in Excel, but we will see that it can be done in Excel VBA very easily.

Place a command button on your worksheet and add the following code lines:

1. First, we declare three variables and one Worksheet object. One variable of type String we call employee, one variable of type Integer we call total, one Worksheet object we call sheet, and one variable of type Integer we call i.

2. We initialize two variables. We initialize the variable total with value 0. We use the InputBox function to get the employee name from the user.

3. After the user has entered an employee name, we want to calculate the total sales of this employee. The workbook consists of three sheets. We want a program that can still be used if sheets are added in the future. Therefore we use the following code line:

4. We start another For Next loop.

5. If the entered employee name matches with the employee name in column B, Excel VBA adds the sales number to the variable total. Add the following code lines:

6. Don’t forget to close both loops.

7. Finally, we display the total sales of the employee using a msgbox.

8. Test the program.

Result for David:

Files in a Directory

Below we will look at a program in Excel VBA that loops through all closed workbooks and worksheets in a directory, and displays all the names.

1. First, we declare two variables of type String, a Worksheet object and two variables of type Integer.

2. To avoid screen flicker, turn off screen updating.

3. Initialize the variable directory. We use the Dir function to find the first *.xl?? file stored in this directory.

Note: the Dir function supports the use of multiple character (*) and single character (?) wildcards to search for all different type of Excel files.

4. The variable fileName now holds the name of the first Excel file found in the directory. Add a Do While Loop.

Add the following code lines (at 5, 6, 7, 8 and 9) to the loop.

5. Initialize the variables of type Integer and add the name of the Excel file to the first column of row i.

6. There is no simple way to extract data (or sheet names) from closed Excel files. Therefore, we open the Excel file.

7. Add all the sheet names of the Excel file to the other columns of row i.

8. Close the Excel file.

9. The Dir function is a special function. To get the other Excel files, you can use the Dir function again with no arguments.

Note: when no more file names match, the Dir function returns a zero-length string (“”). As a result, Excel VBA will leave the Do While loop.

10. Turn on screen updating again (outside the loop).

11. Test the program.

Result:

Import Sheets

Below we will look at a program in Excel VBA that imports sheets from other Excel files into one Excel file.

Add Book1.xls, Book2.xls to “C:test”

Situation:

Add the following code lines to the command button:

1. First, we declare two variables of type String, a Worksheet object and one variable of type Integer.

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

2. Turn off screen updating and displaying alerts.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

3. Initialize the variable directory. We use the Dir function to find the first *.xl?? file stored in this directory.

directory = “c:test”
fileName = Dir(directory & “*.xl??”)

Note: The Dir function supports the use of multiple character (*) and single character (?) wildcards to search for all different type of Excel files.

4. The variable fileName now holds the name of the first Excel file found in the directory. Add a Do While Loop.

Do While fileName <> “”
Loop

Add the following code lines (at 5, 6, 7 and 8) to the loop.

5. There is no simple way to copy worksheets from closed Excel files. Therefore we open the Excel file.

Workbooks.Open (directory & fileName)

6. Import the sheets from the Excel file into import-sheet.xls

For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks(“import-sheets.xls”).Worksheets.count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks(“import-sheets.xls”).Worksheets(total)
Next sheet

Explanation: the variable total holds track of the total number of worksheets of import-sheet.xls. We use the Copy method of the Worksheet object to copy each worksheet and paste it after the last worksheet of import-sheets.xls.

7. Close the Excel file.

Workbooks(fileName).Close

8. The Dir function is a special function. To get the other Excel files, you can use the Dir function again with no arguments.

fileName = Dir()

Note: When no more file names match, the Dir function returns a zero-length string (“”). As a result, Excel VBA will leave the Do While loop.

9. Turn on screen updating and displaying alerts again (outside the loop).

Application.ScreenUpdating = True
Application.DisplayAlerts = True

10. Test the program.

Result:

Programming Charts

Below we will look at two programs in Excel VBA. One program loops through all charts on a sheet and changes each chart to a pie chart. The other program changes some properties of the first chart.

1. Create some charts (or download the Excel file).

Place a command button on the worksheet and add the following code lines:

1. First, we need to declare a ChartObject object. The ChartObject object acts as a container for a Chart object. We call the ChartObject cht but you can use any name.

Dim cht As ChartObject

2. The ChartObjects collection contains all the embedded charts on a single sheet. We want to loop through all charts on the first sheet. Add the following For Each Next loop.

For Each cht In Worksheets(1).ChartObjects
Next cht

3. The Chart object represents a chart in a workbook. Add the following code line to the For Each Next loop to change each chart to a pie chart.

cht.Chart.ChartType = xlPie

Note: again, cht acts as a container for the Chart object. We use the ChartType property to change the chart type. We use the built-in constant xlPie to change each chart to a pie chart.

4. Result when you click the command button on the sheet:

Now we will change some properties of the first chart.

Place another command button on the worksheet and add the following code lines:

5. The ChartObjects collection contains all the embedded charts on a single sheet. Add the following code line to activate the first chart:

Worksheets(1).ChartObjects(1).Activate

We can now refer to this chart as the ActiveChart.

6. Add the following code line to change the Chart title.

ActiveChart.ChartTitle.Text = “Sales Report”

7. Add the following code line to move the legend to the bottom of the chart: