You have already seen in action, in some of the examples in this chapter, the Workbooks, and Worksheets collection objects, as well as the Workbook and Worksheet objects. The difference between collection objects and regular objects was discussed earlier. When working with these objects, keep in mind that the Workbook object is higher in the hierarchy than the Worksheet object. If you are familiar with Excel, this makes sense to you because a single workbook can hold multiple worksheets.
However, the Window object may be unfamiliar and/or a bit confusing. Window objects refer to instances of windows within either the same workbook, or the application. Within the Excel application, the Windows collection object contains all Window objects currently opened; this includes all Workbook objects and copies of any Workbook objects. The Window objects are indexed according to their layering. For example, in Figure 5.2, you could retrieve Book2 with the following code:
Application.Windows(2).Activate because Book2 is the center window in a total of three Window objects. After Book2 is retrieved and thus brought to the top layer its index would change to 1 when using the Windows collection object. This is different from accessing Book2 using the Workbooks collection object. As stated previously, Workbook objects are indexed according to the order of their creation after the value of 1, which is reserved for the selected, or top-level Workbook object.
You may be thinking that the Windows collection object within the Application object is essentially the same as the Workbooks collection object. This may or may not be true depending whether or not the user creates a new window by selecting New Window from the Window menu in the Excel application. This effectively makes a copy of the currently selected workbook. You may also use the NewWindow() method of either the Window or Workbook object in your code to accomplish the same task.
When a new window is created, the caption in the title bar from the original window is concatenated with a colon and an index number. For example, Bookl becomes Book1:1 and Book1:2 when a new window is created (see Figure 5.11). These captions can be changed in code by manipulating the Caption property of the Window object.
Do not confuse the creation of a new window from the Window menu with that of a new workbook. New workbooks are created when the user selects New from the File menu, or by using the Add() method of the Workbooks collection object. Of course, creating a new workbook also creates a new window, but the reverse is not true. If a new Window object is created through the use of the Window menu in Excel (or NewWindow() method in VBA), then this window does not belong to the Workbooks collection object and thus, cannot be accessed in code by using the following:
Creating a new window in Excel.
Creating a new window in Excel.
This code fails because Book1:2 does not belong to the Workbooks collection object but to the Windows collection object of either the Application object or the Workbook object named Book1. It could be accessed with either of the following lines of code:
These examples and the above descriptions demonstrate that there may be more than one path to retrieving an object of interest in your code, and that differences between some objects may be quite subtle. I recommend that you play with these examples and create instances of new windows and new workbooks in your code. Then access these objects through as many paths as you can think of. You will find that it doesn't take long to get comfortable working with the Workbooks collection, Windows collection, Workbook, and Window objects.
All properties, methods, and events for these objects can be viewed in the Object Browser. Let's take a closer look at a few of them via an example, starting with the Workbooks collection object, shown in Figure 5.4.
There are only a few properties and methods of the Workbooks collection object and their functions are straightforward. Add the following procedure to a standard module in a workbook.
Public Sub AddWorkbooks() Dim I As Integer For I = 1 To 3
If you execute this procedure by selecting AddWorkbooks from the Macro menu in Excel, you will immediately see three new workbooks opened in Excel. To select a specific workbook, insert the following line of code after the For/Next loop in the AddWorkbooks() sub procedure.
This is another example of nesting, and it will activate the last workbook to be opened in Excel. The statement Workbooks.Count returns the number of open workbooks in Excel and is then used as the index to activate the last workbook added. If you prefer, edit the above code to make it more readable:
Dim numWorkbooks as Integer NumWorkbooks = Workbooks.Count Workbooks(NumWorkbooks).Activate
Through the Object Browser, you will notice that the Workbooks collection object only has a few members. They are relatively straightforward to use, and you have already seen a couple of them (the Add() method and Count property). You may find the Open() and Close() methods and Item property useful as well. Some of these members will be addressed later, albeit with different objects. You will find that many of the collection objects share the same properties and methods. This is not unusual, but be aware that depending on the object you use, the parameters that are either available or required for these members may vary. Figures 5.12 and 5.13 show that the Workbooks collection object and the Workbook object both have Close() methods.
If you look at the bottom of the Object Browser windows displayed in Figure 5.12 and Figure 5.13, you will see that the Close() method of the Workbooks collection object does not accept any arguments, but the Close() method of the Workbook object can accept up to three arguments, all of which are optional (denoted by the brackets).
Consider the following VBA procedure illustrating the use of the Close() method of the Workbook object. The code can be placed in a standard or object module.
Public Sub CloseFirstLast()
Workbooks(Workbooks.Count).Close SaveChanges:=False Workbooks(1).Close SaveChanges:=False End Sub
The Close() method of the Workbooks collection object.
The Close() method of the Workbooks collection object.
The Close() method
The Close() method
This procedure will close the first and last workbooks opened in Excel without prompting the user to save changes. However, if this procedure is contained somewhere in a code module for the last workbook to be opened, then only the last workbook will be closed. This is because the module containing this code will close before the last line (Workbooks(l).Close SaveChanges:=False) is executed. In the example above, the Close() method of the Workbook object is used, not the Close() method of the Workbooks collection object. This must be the case because an index value was specified, and therefore only the Workbook object designated by an index of 1 is available. Because the Workbook object is used, optional arguments can be used with the method. In this case, the prompt to the user for saving changes to the workbook is set to false (the default is true), so the workbook closes immediately. If you want to close all workbooks simultaneously, then use the Close() method of the Workbooks collection object.
In this case, there are no optional arguments allowed, so the user will be prompted to save the currently selected workbook. All open workbooks will be closed using the line of code above. There is no way to close a single workbook using the Workbooks collection object. To close just one workbook, you need to use the Close() method for a Workbook object.
Now consider an example that sizes and centers the application in the middle of the user's screen such that one-eighth of the screen on every side is unused by Excel. In addition, the workbook is sized so that it just fits inside the available space provided by the application window.
The following code was added to an open workbook and saved as Center.xls on this book's CD-ROM.
Private Sub Workbook_Open()
Application.WindowState = xlMaximized CenterApp Application.Width, Application.Height CenterBook End Sub
Private Sub CenterApp(ByVal maxWidth As Integer, maxHeight As Integer) 'This procedure is used to center the application window Application.WindowState = xlNormal Application.Left = maxWidth / 8 Application.Top = maxHeight / 8 Application.Width = 3 * maxWidth / 4 Application.Height = 3 * maxHeight / 4 End Sub
Private Sub CenterBook()
'This procedure will center the workbook within the application with no extra space 'below or above the workbook window ActiveWindow.WindowState = xlNormal
Workbooks("Center.xls").Windows(1).Width = Application.UsableWidth Workbooks("Center.xls").Windows(1).Height = Application.UsableHeight Workbooks("Center.xls").Windows(1).Left = 0 Workbooks("Center.xls").Windows(1).Top = 0 End Sub
Private Sub Workbook_WindowResize(ByVal Wn As Window) 'Display 20-21 rows of the workbook.
If (Wn.VisibleRange.Rows.Count < 21) Then Do
Wn.Zoom = Wn.Zoom - 1 Loop Until (Wn.VisibleRange.Rows.Count >= 21)
Do Until (Wn.VisibleRange.Rows.Count <= 21) Wn.Zoom = Wn.Zoom + 1
Explicit variable declaration is turned on as usual in the general declarations section of the code window. The main procedure is the Open() event of the Workbook object to ensure that the program is executed immediately after the workbook is opened. You can access the object module for the workbook through the ThisWorkbook selection in the project explorer, as shown in Figure 5.14.
The name of the module ThisWorkbook can be change via the Name property in the properties window for the Workbook object.
ThisWorkbook object module.
ThisWorkbook object module.
The WindowState property (xlMaximized is a constant defined by VBA) of the Application object is used to maximize the Excel window (fill the user's screen). The application window is set to fill the user's screen so that its maximum size can be determined. The Width and Height properties of the Application object are then passed to the CenterApp() sub procedure while the application is maximized.
Different users will have different monitor resolution settings. To ensure consistency from one machine to another, you must first learn the dimensions of the user's screen. Most languages provide a Screen object from which to determine these properties. VBA has no Screen object; therefore, you have to be a bit less elegant about getting the desired width and height.
The CenterApp() sub procedure receives two arguments, maxWidth and maxHeight. The function of the CenterApp() procedure is to center the application window within the user's screen, leaving one-eighth of the screen (on all sides) unoccupied by Excel. The CenterApp() sub procedure begins by setting the WindowState property to xlNormal. This is the equivalent of the user clicking the middle window icon at the top-right corner of the workbook window. The application window must be returned to a normal state because you cannot move a maximized window; thus, trying to set the Left property of the Application object will cause an error and the program will crash. After returning the window state to normal, the application window is resized by setting the Left, Top, Width, and Height properties accordingly.
Next, the Open() event procedure calls the CenterBook() sub procedure without passing arguments. The CenterBook() procedure is called for the purpose of filling the workbook within the Excel application window. The workbook window is set to a normal state just like the application window so that it may be resized. The UsableWidth and UsableHeight properties of the Application object are used to set the values for the Width and Height properties of the Window object representing the workbook. The Windows property of the Workbook object is used to return the top-level window (Windows(l)). Finally, the position (Left, Top) properties of the window are set to the upper-left corner of the application window (0,0).
It is not necessary to use WorkbooksCCenter.xls") qualifier in the CenterBook() procedure. I did this only to illustrate the path to the desired object. If the reference to the Workbook object Center.xls were to be omitted, then VBA would simply use the default object path. The default object path is to the active window of the current workbook. Since this code runs immediately after opening Center.xls, it is the current workbook. An index of 1 is used to select the active or top-level window. As there is only one window in Center.xls, you don't have to worry about getting to the desired window; however, if you created multiple windows in the Center.xls workbook, then you might want to use the Window object's Caption property instead of an index number.
The last procedure in the Center.xls project is the WindowResize() event of the Workbook object. This event procedure accepts one argument representing the Window object associated with the workbook being centered. The WindowResize() event triggers whenever the workbook window is resized; thus, the previous code in the CenterBook() procedure will trigger this event. The code in the WindowResize() event serves to increase or decrease the Zoom property of the Window object such that approximately 21 rows of the worksheet are displayed in the window. The VisibleRange property of the Window object returns a Range object (discussed later) representing those cells that are visible to the user in the Excel application. The Rows property of the Range object then returns another Range object representing the visible rows. Finally, the Count property (a property common to collection objects) of the Range object returns the number of cells in the Range object returned by the Rows property. The entire object/property path effectively returns the number of rows in the range of cells visible to the user.
Was this article helpful?