5.2 Workbooks, Windows, and Worksheets

5.2 Workbooks, Windows , and Worksheets

This section deals with workbooks, windows, and worksheets. All three of these concepts are represented by objects, where an additional distinction is made among types of worksheets (tables, charts , and forms). We first describe all important methods and properties for working with these objects, and then give several examples of their application.

Objects, Methods, and Properties

Application Object

Application represents the basic object within Excel. Application does not refer to a particular Excel file, but to the program Excel as a whole. The properties and methods of Application thus influence for the most part settings that hold for all open files (general options, for instance). In this section the object Application is of great importance to the extent that it is the starting point for practically all methods and properties used to control workbooks, windows, and worksheets. In this respect Application is frequently the default object: When methods such as Worksheets are used without an object being specified, then Application is automatically supplied as the object.

Workbooks

Workbooks (the Workbook object) are synonymous with Excel files. A workbook generally comprises several sheets and is displayed in one or more windows. There is also the possibility that the windows of a workbook are invisible. Though such a workbook may be invisible, its data are present, and procedures defined therein can be executed. There are three methods and properties for accessing open workbooks, all of which return as result a Workbook object.

Workbooks : This method enables access to all open workbooks. Individual workbooks can be accessed by supplying a numerical index (1 to Count ) or by providing the file name . If no workbook is open, then Count returns the value 0.

ActiveWorkbook : This method refers to the currently active workbook. The active workbook is the workbook that is located in the front window and can accept input.

ThisWorkbook : This property refers to the workbook that contains the code that is currently being executed. This workbook is not necessarily the same as the ActiveWorkbook , since a procedure in workbook A can process a sheet in workbook B . In particular, this property must be used when the code in question is located in an invisible workbook, since in this case it is always another workbook that is considered "active."

Methods for Processing Workbooks

Activate : This method transforms the given workbook into the active workbook. Note that the equivalent method Select used with other objects cannot be used here.

Add : This method must be used on Workbooks , and it returns a new, empty, workbook. An optional sheet type (such as xlChart ) can be given, in which case the new workbook will contain only one sheet.

Close : This method closes the workbook given as object. If the workbook contains unsaved data, then an alert automatically appears. (This alert can be eliminated with Application.DisplayAlerts=False .)

Open : This method must be used on Workbooks . It loads the workbook given by its file name. A host of optional parameters control a number of loading variants (such as transforming from another data format, password protection, read- only format).

Save : This method saves the given workbook (or the active workbook if Application is given as object) under its current name. If the file does not have a name, then a file-selection dialog box opens automatically.

SaveAs : As above, but now a valid file name must be given. If the file name already exists, then an alert appears that asks whether that file should be overwritten. SaveAs cannot be applied to the Application object, but rather to individual tables and charts.

SaveCopyAs : As above, but this method does not change the file name of the workbook. It is for Workbook objects only.

GetOpenFilename : This method displays the form for file selection. When a valid file name is selected, this name is returned by the method; otherwise , it returns the Boolean value False . However, the selected file is not opened. This method must be applied to the Application object.

GetSaveAsFilename : As above, but in this case an as yet nonexistent file name can be given.

Important Properties of Workbooks

Name , Path , FullName : These three properties give respectively the file name without the path, only the path, and, finally, the full file name with path. Path contains an empty character string if the workbook has not yet been saved and thus has no file name.

Saved : This property tells whether the file has been changed since the last time it was saved ( True ) or whether it must be saved ( False ).

Windows

Windows are provided for the display of workbooks, where the possibility exists of opening several windows for the same workbook. Even invisible windows or windows that have been reduced to icons are considered "normal" windows by Excel. They differ from visible windows only in the properties Visible and WindowState . The management of windows is similar to that of workbooks.

Windows : This method enables access to individual windows, which are given by an index number or by name. Note that this method also returns windows that have been reduced to icons or have been rendered invisible. When Application is given as the object, then Windows returns an enumeration object of all windows. However, this method can also be applied to a Workbook object, and then it returns only the windows of this workbook.

ActiveWindow : This property of the Application object refers to the active window.

Methods for Processing Windows

Activate : This method activates the window given as object. Note that the often equivalent method Select cannot be used on windows.

ActivatePrevious , ActivateNext : These methods activate the previous and next windows, respectively, and place the window given as object at the end of the list of windows.

Close : This method closes the given window. If the window in question is the last window of the workbook and this workbook contains data that have not yet been saved, then an alert appears automatically to ask whether the workbook should be saved.

NewWindow : This method (applied to an existing window or to a Workbook object) creates a new window. This window contains a copy of the given window or, respectively, of the active window of the workbook. Note that the method Add is, in fact, defined for almost all other objects, but not for windows!

Important Properties of Windows

WindowState : This property determines the appearance of a window. Possible values are xlMaximized , xlMinimized (icon), xlNormal .

Visible : This property tells whether the window is visible ( True ) or invisible ( False ). Invisible windows are said to be "hidden" (command WINDOWHIDE).

Caption : This property gives the title of the window.

DisplayGridlines , DisplayHeadings : These properties determine whether the gridlines, respectively row and column headings, should be displayed.

Zoom : This property determines the zoom factor (10 to 400 percent).

ScrollColumn , ScrollRow : These properties determine the column and row numbers in the upper left-hand corner of the window.

Split , FreezePanes : These properties tell whether the window is split and whether the division is fixed.

SplitRow , SplitColumn : These properties determine the position of the window division lines.

Width , Height , Left , Top : These properties give the size and position of a window in points (1 point = 1/72 inch).

UsableWidth, UsableHeight : These properties give the internal dimensions of a window (without window border, title bar, scroll bars, and so on).

Windowpanes

Divided windows can exhibit up to four sections (panes). Windowpanes are controlled with individual Pane objects. Access to these objects is made either with the Window property ActivePane or with the Window method Panes.ActivePane . The enumeration of Panes can also be done with unsplit windows, though in this case there exists only a single pane.

The currently active pane can be altered with Activate . The two most important properties of a Pane object are LineColumn and SplitColumn , which are defined similarly to windows.

Worksheets

Access to sheets is accomplished with enumeration methods and with individual ActiveXxx properties. Excel recognizes three types of sheets: worksheets (also for saving Excel 4 macros), charts, and forms in the format of Excel 5/7.

Tip  

To be precise, there is also a fourth type of sheet, which, however, has not been supported since Excel 97: module sheets (object type Module ). Modules were displayed in Excel 5/7 as worksheets, but since Excel 97 they can be edited only in the VBA development environment. Note, however, that in the loop For Each s In Sheets all modules will be run through, even though this object type supposedly no longer exists.

Sheets : enables access to all sheets of a workbook, or all sheets of the currently active workbook when the Application object is given. This method returns the result as, depending on the type of sheet, a Worksheet , Chart , or DialogSheet object.

Caution  

There is no general object type for sheets (thus no Sheet object). If x is defined, as in the following example, as a Worksheet (instead of a more general object variable), then an error will result if another sheet type is assigned to the variable. Solution: Define ws as a general Object and fix the object type of the variable with Typename .

 ' Warning, this example produces an error if the workbook   ' contains sheets other than worksheets!   Dim ws As Worksheet   For Each ws In ActiveWorkbook.Sheets     Debug.Print ws.Name   Next ws 

Worksheets , Charts , DialogSheets , Excel4MacroSheets , Excel4IntlMacroSheets : These are like Sheets , but these six methods return sheets only of the given type.

SelectedSheets : This enables access to all selected sheets of a window. This method is useful for working with groups of sheets, that is, after several sheets have been simultaneously selected.

ActiveSheet , ActiveChart , ActiveDialog : These three properties refer to the currently active sheet of the corresponding type. (In the case of the first property, all three sheet types are possible.)

Methods for Working with Sheets

Select , Activate : These two methods activate the given sheet. As long as only one sheet is being processed , the two methods are equivalent. With Select , however, it is possible to set an optional parameter to False . The selected sheet then does not replace the sheet that has been active up to now. Rather, there results a multiple selection. In this way groups of sheets can be worked on together.

Add : This method inserts a new, empty, sheet. The position, number, and type of new sheets can be set with four optional parameters. Without these optional parameters, VBA inserts an empty worksheet in front of the currently active worksheet. The new sheet is made into the active sheet. The name of the worksheet can be set with the property Name .

Copy : This method copies the sheet given as an object into a new, otherwise empty, workbook. If a sheet is given in Copy in an optional parameter, then the new sheet will be inserted before this sheet. In this way the new sheet can also be duplicated within the workbook. Together with the sheet, all objects contained within it and all program code belonging to it are copied as well.

Caution  

In copying an Excel 97 worksheet with embedded MS Forms controls these controls will indeed be copied, but they receive new names ( CommandButton1 , CommandButton2 , etc.). However, the program code is not correspondingly changed, for which reason the link between controls and code is lost.

In the case of controls that were inserted in Excel 2000 into a worksheet this problem no longer occurs. On the other hand, if you use an existing Excel 97 file under Excel 2000, then this error is present as before. Solution: Under Excel 2000 change the names of all controls. Then Excel will "notice" this change. (The best thing to do is to change the names twice. The second time you simply restore the original name. Then you will not have to change the code.)

Delete : This deletes the sheet specified as object. There appears an alert, in which the user must confirm that the sheet is really to be deleted. In the current version this box cannot be prevented from appearing! In the following section we shall show how with SendKeys the message can be acknowledged at once without action on the part of the user.

The Most Important Sheet Properties

Name : This property determines the name of the sheet.

Visible : This property tells whether a sheet is visible or hidden. Hidden sheets can be made visible with the program code ( Sheets( ).Visible=True ). There is no equivalent menu command for this operation! Invisible sheets cannot be activated with Select .

Programming Techniques

Obtaining File Names and Opening Workbooks

VBA offers two methods for obtaining a file name, namely, GetOpenFilename and GetSaveAsFilename . These methods lead to the display of a dialog box for selecting a file, and then they return the file name or False . The only difference between the two methods is that with GetSaveAsFilename it is permissible to give the name of a file that does not yet exist.

The following example code requests the user to select an Excel file, which then is opened. The parameter of GetOpenFilename gives the file filter; in the dialog box only those file names are displayed that match the pattern *.xl? . Further details on dealing with GetOpenFilename and GetSaveAsFilename are to be found later in this chapter.

 ' example file Sheets.xls Sub  LoadExcelFile  ()   Dim result As Variant   result = Application.GetOpenFilename("Excel files,*.xl?", 1)   If result = False Then Exit Sub   Workbooks.Open result End Sub 

Transforming Windows into Icons

If you can no longer see what is going on due to a superfluity of windows, then it can be advantageous to shrink all windows to icons at the push of a button. The only thing special in the following example is the test If win.Visible . This prevents the attempt at making invisible windows become smaller (which would lead to an error).

 ' example file Sheets.xls Sub  ShowWindowsAsIcons  ()   Dim win As Object   For Each win In Windows   If win.Visible Then win.WindowState = xlMinimized   Next win End Sub 

Splitting Windows at the Current Position of the Cell Pointer

The following example program splits a window at the current position of the cell pointer. If the division of the window was previously fixed, then it will again be fixed in the new division. We will use win as an abbreviation for accessing the active window. The location at which the window is split is a result of the row and column difference between the active cell and the cell visible in the upper lefthand corner (whose position is determined with the window properties ScrollRow and ScrollColumn ).

 ' example file Sheets.xls Sub  SplitWindow  ()   Dim freezeMode as Boolean, win As Object   If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub   Set win = ActiveWindow   freezeMode = win.FreezePanes   win.FreezePanes = False     ' otherwise, the division                               ' cannot be changed   If win.Split Then win.Split = False: Exit Sub ' join split parts   ' set new split position   win.SplitRow = ActiveCell.Row - win.ScrollRow   win.SplitColumn = ActiveCell.Column - win.ScrollColumn   win.FreezePanes = freezeMode   ' reinstitute freezing End Sub 

With the TypeName test, the macro is immediately exited if no worksheet is currently active. (Splitting of a window is possible only with worksheets.)

Turning Gridlines and Cell Headings On and Off

There are two window properties that are used particularly often in daily work with tables: showing gridlines and showing row and column headings. The program below tests the current state of both settings and changes into the next mode from among the four possible modes: both window elements visible, only gridlines, only cell headings, neither of them.

The macro can be linked to a new tool in the toolbar. Then with a single tool (once again saving toolbar space) it is possible to switch among four different settings. Even if you have to click three times to achieve the desired mode, this is still more convenient than using the command TOOLSOPTIONSVIEW.

 ' example file Sheets.xls Sub  ToggleHeadingsGrids  ()   Dim gridMode&, headingsMode&   On Error Resume Next   headingsMode = ActiveWindow.DisplayHeadings   gridMode = ActiveWindow.DisplayGridlines   If headingsMode And Not gridMode Then     headingsMode = False   ElseIf Not headingsMode And Not gridMode Then     gridMode = True   ElseIf Not headingsMode And gridMode Then     headingsMode = True   Else     gridMode = False   End If   ActiveWindow.DisplayHeadings = headingsMode   ActiveWindow.DisplayGridlines = gridMode End Sub 

Tip  

With On Error Resume Next the macro can be executed without the error message that would appear if no worksheet were present (and therefore the macro makes no sense).

Deleting a Sheet

For deleting a sheet, all that is necessary is to execute the Delete method. The problem is in the safety alerts that Excel displays before deletion. In some applications it could be an irritant to the user to be confronted suddenly with an alert generated not by the user, but by the program. For this reason the property DisplayAlerts can be used to deactivate such alerts during macro execution.

 Sub  DeleteActiveSheet  ()   Application.DisplayAlerts = False   ActiveSheet.Delete   Application.DisplayAlerts = True End Sub 

One final suggestion: This procedure is not able to delete a single module sheet. If you launch the procedure in a module with F5 , then nothing happens. VBA is equipped with a security mechanism that prohibits a sheet with executing code from being deleted.

Jumping from the First to the Last Sheet of a List

With the keyboard combinations Ctrl+Page Up and Ctrl+Page Down you can switch to the next or previous sheet. However, there is no keyboard combination for jumping to the first or last worksheet. The following four procedures, which you may copy into your personal workbook Personal.xls (see also the section on configuration files below), use the keyboard combinations Ctrl+Shift+Page Up and Ctrl+Shift+Page Down .

In the procedure Workbook_Open , which is automatically executed when a file is loaded, the event procedures for these keyboard combinations are recorded (see Chapter 4 on the subject of auto and event procedures). Workbook_Before- Close then deactivates both macros when the file is closed. GotoFirstSheet and GotoLastSheet are more complicated than seems at first glance necessary. The instructions

 Sheets(1).Select            ' select first sheet Sheets(Sheets.Count).Select ' select last sheet 

would suffice for most situations. However, they have the disadvantage that they lead to an error if the first, respectively last, sheet is hidden. Furthermore, it is necessary to test that the sheet to be activated is not a module sheet, which can occur in the Sheets listing, but since Excel 97 is no longer considered a regular sheet.

 ' Sheets.xls, "This Workbook" Private Sub  Workbook_Open  ()   Application.OnKey "+^{PGUP}", "GotoFirstSheet"   Application.OnKey "+^{PGDN}", "GotoLastSheet" End Sub ' executed automatically when the file is closed Private Sub  Workbook_BeforeClose  (Cancel As Boolean)   Application.OnKey "+^{PGDN}", ""   Application.OnKey "+^{PGUP}", "" End Sub ' Sheets.xls, "Module1" Sub  GotoFirstSheet  ()' activates the first sheet   Dim i&   For i = 1 To Sheets.Count     If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then       Sheets(i).Select       Exit Sub     End If   Next i End Sub Sub  GotoLastSheet  ()     'activates the last sheet   Dim i&   For i = Sheets.Count To 1 Step -1     If Sheets(i).Visible And TypeName(Sheets(i)) <> "Module" Then       Sheets(i).Select       Exit Sub     End If   Next i End Sub 

Syntax Summary

All of these methods and properties can refer to the Application object, some of them also to Workbook or Window objects.

ACCESS TO WORKBOOKS, WINDOWS, AND SHEETS
Workbooks	access to all workbooks
Windows	access to all windows
Sheets	access to all sheets of a workbook
SelectedSheets	access to groups of sheets (with multiple selection)
Worksheets	access only to worksheets
Charts	access only to chart sheets
DialogSheets	access only to form sheets
Modules	access only to module sheets
Excel4MacroSheets	access only to Excel 4 macro sheets
Excel4IntlMacroSheets	access to international macro sheets
ActiveWorkbook	currently active workbook
ThisWorkbook	workbook in which the code is located
ActiveWindow	active window
ActiveSheet	active sheet of a window/workbook/application
ActiveChart	active chart of a window/workbook/application
ActiveDialog	active form of a window/workbook/application

WORKING WITH WORKBOOKS
workbk.Activate	determines the active workbook
Workbooks.Add	adds a new, empty, workbook
workbk.Close	closes the workbook
workbk.Open "filename"	opens the specified file
workbk.Save	saves the workbook
workbk.SaveAs "filename"	as above, but under the given name
workbk.SaveCopyAs "dn"	as above, without changing the name of the workbook
workbk.Name	contains the file name without the path name
workbk.Path	only the path
workbk.FullName	path plus file name
workbk.Saved	tells whether the workbook has been saved
Application.GetOpenFilename	select an existing file name
Application.GetSaveAsFilename	select a new file name

WORKING WITH WINDOWS
win.Activate	activates the specified window
win.ActivatePrevious	activates the previously active window
win.ActivateNext	activates the next window in the list
win.Close	closes the specified window
win.NewWindow	creates a new window
win.WindowState	xlMaximized/xlMinimized/xlNormal
win.Visible	makes visible or invisible (True/False)
win.Caption	gives the window caption
win.DisplayGridlines	show gridlines (True/False)
win.DisplayHeadings	display row and column headings (True/False)
win.Zoom	zoom factor (10-400 percent)
win.ScrollColumn	visible column number on the left border
win.ScrollRow	visible row number on the upper border
win.Split	tells whether a window is split (True/False)
win.FreezePanes	tells whether a window division is fixed
win.SplitRow	determines the number of rows in the upper pane
win.SplitColumn	determines the number of columns in the left pane
win.Width/Height	outside dimensions in points (1/72 inch)
win.UsableWidth/UsableHeight	internal dimensions in points
win.Left, win.Top	position in points

WORKING WITH WINDOWPANES
win.Panes	access all panes of a window
win.ActivePane	access the active pane of a window
pane.Activate	determines the active pane
pane.SplitColumn	row number on the upper border
pane.SplitRow	column number on the left border

WORKING WITH WORKSHEETS
sheet.Activate	selects a sheet
sheet.Select False	multiple selection
workbk.Add	adds an empty worksheet
workbk.Add before:=, type:=	as above, plus position type and sheet type
sheet.Copy	copies a sheet into a new workbook
sheet1.Copy sheet2	copies sheet 1 and inserts it before sheet 2
sheet.Delete	deletes the sheet (with alert)
sheet.Name	name of a sheet
sheet.Visible	make visible or invisible