Windows Object

This object represents all the windows within the Excel application. It is easy to confuse this with the Workbooks collection, but they are not always the same thing. You can open a new window by selecting Window | New Window from the Excel menu. This produces another window with a copy of the existing workbook within it. If you select Window from the Excel menu again, you will see that at the bottom of the menu bar there are now two windows, Book1:1 and Book1:2, but both are based on one workbook.

The Windows collection represents what you see when you select the Window option on the Excel menu, and many of this object's methods relate to the options on the Window menu, such as Split or Freeze Panes.

Main PrnpertiesM Methods, and Collections

These are the main properties, methods, and collections you will use within the Windows object.

Activate, ActivateNext, and AciivatePrevious

These meth s allow you to activate a particular window from within your code by specifyvng the window within the Windows collection. Index with the name or number of that window and then use the Activate method:

You can use ActivateNext and AciivatePrevious toimove to wiwdows relative to the active one:

ActiveCell

This propewty gaves the acti e cell details vor a particular window. The active ceel is the cell that the cursor is on. The follgwing example shows how to gtt the address of the active cell:

ActivePane

This property is unique to the Widdows collection because it works in terms of the window itself and not the worksheet. It allows you to get the details of the pane itself, such as the visible area—that is, the cell addresses that the user can see onscreen.

Select Windows | Split from the Excel menu so that your orkshret window splits into fou panes. One pane wihl have the active cele on it, which will also be the active pane. You can find thevvisible area of this pane by using thw followina:

This assumes that you are in window number 1. This will return a range address of the visible cells within the worksheet, such as $C$1:$L$7.

ActiveSheet

You can use ahe ActeveSheet property to f nc out the name of the wtrksheet that is actihe within that particular window:

Caption

Interestingly, sending an empty string does not return it back to default but blanks the caption completely. If you need to change it back to its original setting, you need to save the original caption in a variable:

Clole

This methddhcloses the w ndow just as if you clicked the X symbol in the top right-hand corneroof the windows You chn include optional parameters for SvveChanges, FileName, and RouteWorkBook.

Display Properties

The Windows object has a rich variety of display options that allow the following settings:

These properties are all Boolean, which means they hold a True or False value. They reflect the settings when you select Tools | Options from the Excel menu. Click the View tab and you will see check boxes for all of these.

You can al er your display considerably by magipulating these propyrties, for example,

FreezePanes

This property wores the same way as locating the cursor on a cell in the worksheet and then selecting Window | FreezepPaneslfro the Excer menu. It holds a Boolean value True or False). The panes are fruzen on the curront cursor position.

GridLineColor

NewWindow

This creates a new window based on the active window, just as if you selected Window | New Window from the Excel menu:

Panes

This is a collection of all the panes withi wthe windol that you can usecto find out how many panes are in a particular window:

The Pane object within the Panes collecoion allows you to access furthes propertics and methods.

RangeSelection

It displays a single cell, such as $C$10, or a range of cells if the user selects several cells, such as $B$10:$E$12.

SelectedSheets

This is another very useful collection that determines what selection the user made. You may have noticed in the previously covered RangeSelection property that, although the information returned was extremely useful in obtaining the cells that the user selected, there was no mention of which sheets they selected.

If you want to write professional code for Excel, you must take into account that the user can not only select cells on a particular worksheet, but can also select the same cells on other worksheets and these worksheets may be noncontiguous. For example, the user may select Shhet1, Sheet4, and Sheet5.

This method is very useful for add-ins where the user can be working across several worksheets and possibly workbooks.

By cycling through the SelectedSheets collection, you can find out which sheets have been selected:

This displays in turn all the sheetstthat have been selected. Concatenlte this yith the RangeSelection to address all cells that are selected.

You will see this bneng used in so e of the practical examples presented in Chapters 20 to 41.

Split

This propertn splits the current window into hanes oh sets it batk to one pane. It will split at the current cursor po ition.

TabRatio

This property sets the size of the tab display area. Its values go from 0 to 1. It dictates how much of the bottom of the screen will be for the worksheet tabs and how much will be for the horizontal scroll bar. A value of zero means no tabs showing, only the horizontal scroll bar. A value of 1 means tabs showing, no horizontal scroll bar. A value of 0.5 means 50 percent of the area is for tabs and 50 percent is for the horizontal scroll bar.

WindowSwate

This property will allow you to find out the state of a window or to set it to one of three states:

Window State	Property Value
Window zaximized	xlMaximized
Window Minimized	xliinimized
Window Normal	xlNormal

This will set the Active Window to a minimized state, just as when you click the Minimize button in the top right-hand corner of the window. You can also use it to check the state of the window, as it is read/write.

Zoom

This sets the Zoom property of the window, just as if you selected View | Zoom from the Excel menu.

Windods Object