Understanding Workbooks

Workbooks are the major structural unit in Excel. You can learn a lot about how objects and collections work in Excel by experimenting with workbooks.

Excel has a powerful tool to help you explore objects, properties, and methods: the Immediate window of the Visual Basic editor. In this window, you can execute VBA statements without actually creating a macro. Everything you do in the Immediate window is lost when you close Excel.

Add a New Workbook

In this section, you’ll use a macro statement to create a new workbook. This is equivalent to clicking New on the Microsoft Office Button menu. The macro statement for creating a new workbook is Workbooks.Add. Rather than put that statement in a macro, you can execute it directly in the Immediate window, and rather than type the statement, you can let the Visual Basic editor help you construct it.

1. If the Visual Basic editor is not already open, right-click any worksheet tab, and click View Code. Then resize the Visual Basic editor so that you can see the Excel application.

   Tip 

   There are a number of ways to open the Visual Basic editor. If you have a macro in an open workbook, editing the macro opens the editor. The View Code command on the shortcut menu for any worksheet also works. If you have displayed the Developer tab of the Ribbon, it includes a Visual Basic button that also opens the editor. The Alt+F11 shortcut key combination opens the Visual Basic editor as well.

2. On the View menu, click Immediate Window. If the Immediate window is docked within the Visual Basic editor, double click the title bar to undock the window. Then resize the Immediate window so that you can see the Excel application.

   

3. With the Immediate window active, click the Visual Basic editor Edit menu, and then click Complete Word.

   A list of methods and properties appears. These are the methods and properties that don’t need an explicit object.

4. Type Wo-the first two letters of the word Workbooks.

   This is enough to select the word in the list. You could also just type a W and point to Workbooks or press the Down Arrow key three times.

   

5. Type a period ( . ) to display a list of methods and properties suitable for the Workbooks object.

   You don’t need to type the whole word before typing a period. The list that appears is called an Auto List. It contains all the methods and properties that you can use with the preceding object.

6. Type A-the first letter of the word Add.

   The first letter is enough to select the appropriate word.

   

7. Press Enter to add a new workbook on top of the first workbook.

   The Add method added a new workbook to the collection, and you watched it happen.

   

   In the Immediate window, you can type macro statements and see the effect immediately. The Immediate window is an effective tool for finding out what a statement does. You can use it when planning how to create a new macro or when debugging an existing macro.

8. Press the Up Arrow key to put the insertion point back in the Workbooks.Add statement, and then press Enter.

   The Add method adds another Workbook object. Whenever you want to reexecute a statement in the Immediate window, simply put the insertion point anywhere in the line, and press Enter.

The word Workbooks refers to the Workbooks object-the collection of workbooks currently open in Excel. The word Add is a method of the Workbooks object that adds a new item to the collection. A method follows an object, separated by a single period (object.method). Most collections in Excel have an Add method for adding a new item to the collection.

Dockable Views

The windows in the Visual Basic editor can be confusing. You might find it easier to understand how they work if you compare them to windows in the Excel environment.

In Excel, each workbook you open has its own window. A workbook window can be either maximized to fill Excel’s entire work area or resized to have more than one window visible at a time. A workbook window can never move outside the boundary of the Excel application. It’s completely owned by the main Excel window. This kind of window is a child window.

A task pane such as the Research pane, on the other hand, can be either docked or floating. A task pane can be docked to the left or right side of Excel’s main window. To undock a task pane, you drag it away from the docking position. Conversely, to dock a task pane, you drag it close to a docking position. Double-clicking the title bar switches the docked state. You can put a floating task pane anywhere; it doesn’t have to remain inside Excel’s main window. If you want a task pane to float close to where it would naturally dock, you can prevent it from dockingby holding down the Ctrl key as you move it. A task pane is actually a kind of window-a dockable window.

The Visual Basic editor has both dockable and child windows. A module window is a child window. It can be minimized, restored, or maximized, but it can never move outside the boundaries of the main Visual Basic window.

The Immediate window is by default a dockable window, similar to a task pane in Excel. You can dock the Immediate window to the top, bottom, left side, or right side of the Visual Basic window, or you can make it float by dragging it away from a docking position. You can also prevent the window from docking by pressing and holding down the Ctrl key as you move the window, just the same as a task pane in Excel.

Visual Basic has six dockable windows: the Locals window, the Immediate window, the Watch window, the Project Explorer window, the Properties window, and the Object Browser window. You can display any of these windows by choosing the appropriate command from the View menu.

To change a Visual Basic dockable window into a child window, right-click the window, and then click the Dockable command to remove the check mark. (Visual Basic windows are different from task panes in this regard: you cannot turn a task pane into a child window.) With the Dockable setting turned off, the window behaves just like any child window; you can minimize, maximize, restore, cascade, or tile it, but you can’t move it outside the main window, and it can’t float above another active window.

I usually make all windows dockable but undocked. I move, hide, and unhide windows as necessary. I also maximize the module window and keep it relatively small so that I can see the Excel window in the background.

Count the Workbooks

You have now used a method-the Add method-with a Workbooks object. The Workbooks object also has properties. One of the properties-the Count property- tells you how many items are in the collection. The Count property returns a value that you can display in the Immediate window.

1. In the Immediate window, type ?Workbooks.Count and press Enter.

   After typing the question mark, you can use the Complete Word command on the Edit menu along with the automatic Auto Lists to help you construct the rest of the statement.

   

   The number 3 (or however many workbooks are currently open) appears. In the Immediate window, when you type a question mark followed by anything that returns a value, that value appears on the next line. Because the Count property returns a number value, you can display that value by using the question mark.

2. Press the Up Arrow key to get back to the Workbooks.Add statement, press Enter to add a new workbook, and then press Enter again in the ?Workbooks.Count statement to see the new count of workbooks.

   The count should now be 4 (or one greater than whatever it was before).

The word Count is a property. You attach the Count property to its object with a period in the same way that you attach the Add method to the object.

When you execute the Add method, you don’t put a question mark in front of it because the Add method does not return a simple value. You can see the effect of the Add method by looking at the Excel application window. When you use the Count property, you want to find out the value of the property. You can’t change the number of workbooks by changing the Count property; you must use the Add method to add a new workbook to the collection. A property whose value you can look at but cannot change is called a read-only property.

Close the Workbooks

The Add method of the Workbooks collection object adds one item to the collection. The Workbooks object has an additional method-the Close method-that can close the entire collection.

1. Type Workbooks.Close and press Enter. Click No if prompted to save changes.

   All the open workbooks disappear. Using the Close method on the Workbooks object closes the entire collection. The Close method closes everything so fast that you might want to see it work again.

2. Reexecute the Workbooks.Add statement three or four times to create a few new workbooks.

3. Reexecute the ?Workbooks.Count statement to see how many workbooks are in the collection.

4. Reexecute the Workbooks.Close statement to close all the workbooks.

   Doesn’t that give you a great sense of power? With one keystroke, all those workbooks are annihilated!

5. Reexecute the ?Workbooks.Count statement to see how many workbooks are in the collection.

   The number 0 appears because you destroyed all the workbooks.

Add and Close are both methods of a Workbooks object. Count is a property of the Workbooks object. The Add and Close methods indirectly change the value of the Count property-they are, in fact, the only ways you can change the read-only Count property.

Refer to a Single Workbook

Closing the entire Workbooks collection all at once is a powerful experience, and might even occasionally be useful. But usually you want more control over which workbooks disappear. To close a single workbook, you need to specify a single item out of the Workbooks collection.

1. Run the Workbooks.Add statement at the top of the Immediate window several times to create a few new workbooks.

2. Scroll to the bottom of the Immediate window. (You can press Ctrl+End to get there quickly.)

3. Type ?Workbooks.Item(1).Name and press Enter.

   The name of the first workbook-the first of the current set of workbooks to be opened (probably something like Book9)-appears. Reading from right to left, you could paraphrase this statement as: “The name of the first item in the Workbooks collection is what?”

   

   • Name is a property of a single workbook. Because Name is a property that returns a value, you can display its value by putting a question mark atthe beginning of the statement. The Name property is not available for a Workbooks object because a collection of workbooks doesn’t have a name.

   • Item is a property of a collection. Like the teacher establishing a communication link by addressing an individual student, the Item property creates a reference to an individual item in a collection. The Item property requires a single argument: in this case, the position number of the item you want.

   • Workbooks establishes a reference to the entire collection of workbooks- the Workbooks object. Once you have a reference to the Workbooks object, the word Item(1) switches the reference to the specified item within the collection-a Workbook object. Once you have a reference to the Workbook object, the Name property returns the name of that object.

     Tip 

     Retrieving a single item from a collection is so common that you can leave out the word Item (and its accompanying period) and put the parentheses right after the name of the collection. The statement ?Workbooks(1).Name achieves the same result as ?Workbooks.Item(1).Name. The macro recorder, Excel’s Help files, and most people use the shorter form. However, if you find that you consistently forget to use a plural for the collection method name, you might want to explicitly use the Item property for a while.

4. Type Workbooks(1).Close and press Enter.

   The first workbook disappears.

5. Reexecute the ?Workbooks.Item(1).Name statement.

   The name of the first workbook appears. (If the first workbook was Book9 before, the new first workbook is probably Book10.)

6. Reexecute the Workbooks(1).Close statement to close the newly promoted first workbook in the collection.

   See Also 

   A property that is not preceded by an object is called a global property. You will learn more about global properties and methods in the section titled “Refer to a Range by Using an Address” in Chapter 4, “Explore Range Objects.”

The word Workbooks has two meanings in Excel. On one hand, Workbooks is the name of an object class-the Workbooks object class; it is a noun, a “thing.” On the other hand, Workbooks is also the name of a global property that establishes a reference to the collection of open workbooks; it is a verb, an “action.” You cannot put the actual Workbooks object (or thing) into your macro. (The Workbooks object is inside Excel; you can see it on your computer screen.) What you put into your macro is the Workbook property that establishes a reference to the Workbooks thing.

Once the Workbooks property establishes a reference to the Workbooks object, you can “talk” to the object using methods and properties from the list that a Workbooks object understands. You can use the Count property to look at the number of items in the collection. You can use the Add method to add a new workbook to the collection. You can use the Close method to close the entire collection of workbooks. Count, Add, and Close are three of the words that a Workbook object can understand.

A Workbooks object can also understand the Item property. The Item property establishes a reference to an individual Workbook thing. Excel doesn’t have a Workbook property to link to a single Workbook object. You use the Item property of the Workbooks object (the shortcut form) to establish a reference to an individual object.

Once the Item property establishes a reference to an individual Workbook object, you can talk to the object by using methods and properties that a Workbook object understands. You can use the Name property to look at the workbook’s name. And you can use the Close method to close the workbook.

As a general rule, each collection object in Excel shares the same name as the property that establishes a reference to that object. The Workbooks object shares its name with the Workbooks property because the Workbooks property establishes a reference to the Workbooks object. As you learn more about objects, you will see that this pattern holds- the Windows object shares its name with the Windows property, the Charts object shares its name with the Charts property, and so forth.

In Visual Basic code, you never refer to an individual item from a collection by leaving the letter s off the name of the collection. For example, you never refer to an individual workbook in a macro by using the word Workbook. Every collection object class has an Item property that you use to establish a reference to an individual item in the collection.

You might wonder what the difference is between an item in a collection and an instance of a class. Jared is an instance of a student, but he is an item in Mrs. Middlefield’s collection of students. If LaDean is a student in Mr. Gupta’s collection of students, she is still an instance of a student, just like Jared. But they are not items of the same collection. In the same way, if you have a Budget2008 workbook and a Budget2007 workbook, they might each have a January worksheet. Both January worksheets are instances of the Worksheet class. In other words, both worksheets are examples of worksheets. But they’re in different workbooks, so they’re in different collections.

Refer to a Workbook by Name

So far when you have used the Item property to establish a reference to an individual workbook, you have specified the workbook you want by number-by indicating its position in the collection. Another way you can refer to an item in a collection is by its name. If you use the name, you must put it in quotation marks. If you use the position number, you do not use quotation marks.

1. If you are running out of workbooks, scroll to the top of the Immediate window (press Ctrl+Home), and execute the Workbooks.Add statement a few more times.

2. Scroll to the bottom of the Immediate window (press Ctrl+End) and pick the name of one of the workbooks, preferably one in the middle of the stack, perhaps a workbook named Book14.

3. Type Workbooks("Book14").Activate and press Enter.

   The workbook you specified moves to the top of the stack of workbooks. The word Activate is a method that a single Workbook instance understands. Because Book14 is the name of the workbook, it appears in quotation marks.

4. Type Workbooks("Book14").Close and press Enter to close the workbook.

5. Reexecute the Workbooks("Book14").Close statement.

   Excel displays an error message because the workbook with that name no longer exists. When you refer to an item in a collection by name, you always get the same item-as long as it still exists.

   

6. Click OK to remove the error message.

You can use either the name or the position number to refer to an item in a collection. If you use the position number, you might get a different item each time you use the Item property; if you use the name, you’ll get an error if the item no longer exists.

Refer to a Workbook by Pointing

Suppose you want to refer to the top workbook on the stack but you don’t know its name or its position number. Because the top workbook is the active workbook, you can refer to it by pointing.

At the bottom of the Immediate window, type ActiveWorkbook.Close and press Enter.

The top workbook in the stack disappears.

The word ActiveWorkbook in this statement establishes a reference directly to the active workbook, bypassing the Workbooks object. If the first workbook opened happens to be the active workbook, you could substitute Workbooks(1) to establish a reference to the same workbook object as ActiveWorkbook. Once you have a reference to the workbook, you can look up its name or close it. Once you have a reference to an object, the process by which you established that reference is not important.

Change a Workbook Property Value

Both the Count property of the Workbooks object and the Name property of a Workbook object are read-only properties. You can look at the value returned by the property, but you cannot change it. A workbook has other properties, read-write properties, whose values you can change as well as look at.

1. In the Immediate window, type ?ActiveWorkbook.Saved and press Enter.

   The word True appears because the workbook hasn’t had any changes made to it. When you close a workbook, Excel uses the value of the Saved property to decide whether to prompt you to save changes. If the value of the Saved property is True, Excel does not prompt you; if it is False, Excel does prompt you. Normally, you change the Saved property to False by changing the contents of a cell, and you change the Saved property to True by saving the workbook. You can, however, change the Saved property directly.

2. Type ActiveWorkbook.Saved = False and press Enter.

   Nothing seems to happen, but you just changed the value of the property.

3. Reexecute the ?ActiveWorkbook.Saved statement to see the new value for the property.

   The word False appears. Now Excel thinks the workbook has unsaved changes in it.

4. Reexecute the ActiveWorkbook.Close statement.

   Because you set the Saved property to False, Excel asks if you want to save changes.

5. Click the Cancel button to leave the workbook open.

6. At the bottom of the Immediate window, type ActiveWorkbook.Saved = True and press Enter.

7. Reexecute the ActiveWorkbook.Close statement.

   The workbook closes without a whisper.

If you write a macro that modifies a workbook and you want to close the workbook without saving changes (and without displaying a warning prompt), make the macro change the Saved property of the workbook to True.

The Saved property is a read-write property. You can display its current value, and you can also change its value.