Excel VBA Macro Programming

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 Properties, Methods, and Collections

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

Activate, ActivateNext, and ActivatePrevious

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

Windows("Book1").Activate

You can also refer to the window by its index number:

Windows(1).Activate

You can use ActivateNext and ActivatePrevious to move to windows relative to the active one:

ActiveWindow.ActivateNext ActiveWindow.ActivatePrevious

ActiveCell

This property gives the active cell details for a particular window. The active cell is the cell that the cursor is on. The following example shows how to get the address of the active cell :

MsgBox Windows("Book1").ActiveCell.Address

ActivePane

This property is unique to the Windows 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 worksheet window splits into four panes. One pane will have the active cell on it, which will also be the active pane. You can find the visible area of this pane by using the following:

MsgBox Windows(1).ActivePane.VisibleRange.Address

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 the ActiveSheet property to find out the name of the worksheet that is active within that particular window:

MsgBox Windows(1).ActiveSheet.Name

This displays Sheet1 or whatever the active sheet is.

Caption

This property alters the caption in the window:

ActiveWindow.Caption = "MyWindow"

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:

ActiveWindow.Caption = ""

Close

This method closes the window just as if you clicked the X symbol in the top right-hand corner of the window. You can include optional parameters for SaveChanges , FileName , and RouteWorkBook .

Display Properties

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

DisplayFormulas DisplayGridlines DisplayHeadings DisplayHorizontalScrollBar DisplayOutline DisplayRightToLeft DisplayVerticalScrollBar DisplayworkBookTabs DisplayZeros

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 alter your display considerably by manipulating these properties, for example,

ActiveWindow.DisplayWorkbookTabs = False

This will remove the tabs from the bottom of the active window.

FreezePanes

This property works the same way as locating the cursor on a cell in the worksheet and then selecting Window Freeze Panes from the Excel menu. It holds a Boolean value (True or False). The panes are frozen on the current cursor position.

ActiveWindow.FreezePanes = True

GridLineColor

This property alters the color of gridlines in the window display:

ActiveWindow.GridLineColor = QBColor(14)

You can also use RGB (Red Green Blue) colors.

NewWindow

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

ActiveWindow.NewWindow

Panes

This is a collection of all the panes within the window that you can use to find out how many panes are in a particular window:

MsgBox ActiveWindow.Panes.Count

The Pane object within the Panes collection allows you to access further properties and methods.

RangeSelection

This very useful property tells you what range the user selected:

MsgBox ActiveWindow.RangeSelection.Address

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 Sheet1 , 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:

Dim MySheet As Worksheet For Each MySheet In ActiveWindow.SelectedSheets MsgBox MySheet.Name Next MySheet

This displays in turn all the sheets that have been selected. Concatenate this with the RangeSelection to address all cells that are selected.

You will see this being used in some of the practical examples presented in Chapters 20 to 41.

Split

This property splits the current window into panes or sets it back to one pane. It will split at the current cursor position.

ActiveWindow.Split = True

This is the same as selecting Windows Split from the Excel menu.

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.

ActiveWindow.TabRatio = 0.5

WindowState

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

ActiveWindow.WindowState = xlMinimized

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.

ActiveWindow.Zoom = 80

This will give a zoom of 80 percent.