55.

Understanding Workbooks

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

Excel also has a powerful tool to help you experiment with objects, properties, and methods: the Immediate window of the Visual Basic Editor. You can use the Immediate window to explore Workbook objects. The Immediate window is a place to do experiments without writing macros. 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 the New button on the toolbar. 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. Rather than type the statement, you can let the editor help you construct it.

Visual Basic Editor

1. Click the Visual Basic Editor button on the Visual Basic toolbar. Resize the Visual Basic Editor so that you can see the Excel application.

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

   

3. With the Immediate window active, point at the Edit menu and 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 at 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 AutoList. 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 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 worksheet 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 toolbar, on the other hand, can be either docked or floating. A toolbar can be docked to the top, bottom, left side, or right side of Excel's main window. To undock a toolbar, you drag the toolbar away from the docking position. A floating toolbar can be placed anywhere; it doesn't have to remain inside Excel's main window. A toolbar is actually a kind of window-a dockable window.

The Visual Basic environment 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, as are toolbars 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 the Ctrl key as you move the window.

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 turn off the check mark. 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 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 works on the Workbooks object by adding one item to it. 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 asked 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 the 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 possibly, 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 scroll quickly to the bottom of the Immediate window.)

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 at the 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 all 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.

1. Type Workbooks(1).Close and press Enter. The first workbook disappears.

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

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

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 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. The Workbooks property establishes a reference with 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.

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 and execute the Workbooks.Add statement a few more times.

2. Scroll to the bottom of the Immediate window (press Ctrl+End). 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 object 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.

• On a blank row 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.