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