Workbook opening

It is advisable to open and use only one EXCEL file and, especially, not to have blank new spreadsheets open at the same time. While the default exit procedure in the VBA code involves the instruction Application.quif, on occasions the presence of another open workbook can interfere with the closing sequence. 

To access the Visual Basic Editor, choose Macro from the Tools menu and then Visual Basic Editor from the submenu. The Visual Basic Editor screen contains two important windows the Code window and the Project window. Procedures are viewed or typed in the Code window, which corresponds to a module sheet in Excel 95. Use the Project window to select a particular code module from all the available modules in open workbooks. These are displayed in the Project window, which is usually located on the left side of the screen. If the Project window is not visible, choose Project Explorer from the... 

In the Project Explorer window you will see a hierarchy "tree" with a node for each open workbook. In the example illustrated in Figure 13-3, a new workbook. Workbooks, has been opened. The node for Workbooks has a node (a folder icon) labeled Microsoft Excel Objects click on the folder icon to display the nodes it contains — an icon for each sheet in the workbook and an additional one labeled ThisWorkbook. If you double-click on any one of these nodes you will display the code sheet for it, but these code sheets are for a special type of procedure called an automatic procedure (see Chapter 18). 

The Record Macro dialog box allows you to choose where the recorded macro will be stored. There are three possibilities in the "Store Macro In" list box This Workbook, New Workbook and Personal Macro Workbook. The Personal Macro Workbook (PERSONAL.XLS in Excel for Windows, or Personal Macro Workbook in Excel for the Macintosh) is a workbook that is automatically opened when you start Excel. Since only macros in open workbooks are available for use, the Personal Macro Workbook is the ideal location for macros that you want to have available all the time. 

In the preceding example, the macro was run by using a shortcut key. There are a number of other ways to run a macro. One of the most common is to use the Macro dialog box. Again, enter a number in a cell, select the cell, then choose Macro from the Tools menu and Macros... from the submenu. The Macro dialog box will be displayed (Figure 13-8). This dialog box lists all macros in open workbooks. To run the macro, select it from the list, then press the Rim button. 

If Deconvolution is the only open workbook, and Spectrum 1 is the active sheet, you can omit references to them and simply use the reference Range("E5"). 

A final warning when workbooks have customized macros embedded in their menus, make sure that only one workbook is open at the time. With several open workbooks, each with special additions to their toolbars, Excel tries to combine them, but may not always succeed, especially when the various workbooks contain non-identical macros that use the same name. 

Now open Workbook. Check the units to see if it is in SI imits. Otherwise, change the unit by clicking Tools/Preferences/Variables. Choose SI and click Clone and change the units so that it is most convenient to you. 

The locked cells in any worksheet could not be opened or altered by laboratory staff without the knowledge and agreement of the Study Director. Use of the locking function in the workbook, therefore, constrained the analytical laboratories to a single system for recording data and calculating results. 

Within this Workbook, the maximum pressure required to fully open the pressure relief device will be referred to as the "relief pressure". (Caution some papers on relief sizing refer to "set pressure" but mean "relief pressure"). For a bursting disc, the relief pressure will be the maximum specified bursting pressure and for a safety valve, it will be the set pressure plus 10% overpressure (or whatever percentage overpressure the valve has been certified at). 

The relief pressure is defined in this Workbook as that at which the relief device is certain to be fully open (see 5.2.2 ). 

Analysis and tabulations of data to be used in Gaussian plume formulas are also available. The report for the St. Louis Dispersion Study (13) gives further insight into tracer-spreading over urban areas in contradistinction to open areas where many measurements have been made. Detailed working charts and numerous examples in Turners workbook (14) aid practical estimation of atmospheric dispersions under the conditions outlined above. 

An Excel workbook is a document that appears in its own document window. Although you can have several workbooks open at the same time, and can see... 

To change the size of a workbook or worksheet, click and drag any of its borders or comers the mouse pointer changes shape when you click on a border or comer. You can adjust the document to any size you desire. If you click on the Minimize button (the "underline" symbol in the upper right comer of the document) the document will be minimized so that only the title bar is visible. To restore it to its full size, click the Maximize button (the open square in the upper right comer of the title bar). 

To open an existing workbook or worksheet from the desktop, simply double-click on it. This will open the document (and will start Excel as well if it wasn t already running). If you start Excel first, it will open a new blank workbook. 

You can use Save Workspace if you have to interrupt your work, when you have several workbooks open at once. The Save Workspace command saves the current configuration of the workspace. Excel for the Macintosh proposes the filename RESUME. When you open the RESUME file, the workbooks will be restored to their former size and position on screen. 

To make a workbook read-only, the document should be closed. In the Windows Start menu, choose Programs, and then Windows Explorer. In the Exploring window, open the drive or folder that contains the file and select the document name. Choose Properties from the File menu, choose the General tab, and check the Read-only check box. 

A custom function is used in a worksheet formula in exactly the same way as any of Excel s built-in functions. You can enter it in a formula by using Paste Function, or by typing it. The workbook containing the custom function must be open. 

If you create a macro to be used by other people, you can make it easy for them to use the macro by installing a custom menu command. As well, you can install the new menu command by means of an automatic procedure, so that all the users have to do is open the macro document. There are two ways you create an automatic procedure by creating an Auto Open macro in a module sheet, or by creating a Workbook Open event procedure. These are described in the sections that follow. 

In Excel 5/95, any macro with the name Auto Open will be run automatically whenever the workbook containing that macro is opened. You can still employ Auto Open procedures in Excel 97 or Excel 2000. 

To install a new menu command whenever Excel is started, create an Auto Open procedure in the Personal Macro Workbook. To install a new menu command when a particular workbook is opened, create an Auto Open procedure in that workbook. Use the MenuBars and Menus VBA keywords to specify where the new Menultem is to be added, with the Add keyword. The syntax of the Add method is ... 

Event-handler procedures are not written in the usual VBA modules, but in the code modules associated with a worksheet or workbook. For example, to install a new menu command when a particular workbook is opened, put the event-handler procedure in the code window for ThisWorkbook (use the Project Explorer to locate the node for ThisWorkbook, then click on it to display the Code window). 

Figure 18-3 illustrates a Workbook Open procedure that installs a new menu command in the Tools menu and assigns the Sub procedure named MyProcedure to it. In the preceding code, replace New Menu Command... and MyProcedure with the text of your menu command and macro name. In this example, MyProcedure, which simply beeps ten times, is a Sub procedure located in a module sheet (Figure 18-4). 

From the preceding examples it should be clear that you can create automatic procedures that install a menu command when a particular workbook is opened and remove the menu command when that workbook is closed (using the Workbook BeforeClose event procedure). Or you can install a command when a particular worksheet is activated and remove it when that worksheet is deactivated. 

Some of your macros will be written for a very specific purpose, such as to prepare a specialized report. The workbook containing the macro will be opened only when you want to assemble the report. Other macros automate tasks that you perform often, and you ll want to have them available whenever you re using Excel. These macros should be saved in the Personal Macro Workbook. To make a command macro even easier to use, you can add a custom toolbutton to a toolbar and assign the macro to it. The three macros described in this section — the NumberFormatConvert macro, the FullPage macro and the ChemicalFormat macro — are particularly convenient to use when they are assigned to a button. 

Object can be Window, Workbook or Workbooks. If SaveChangesLogicalis False, does not save changes if omitted, displays a "Save Changes " dialog box. Example Workbooks("BOOK1. XLS").Close See also Open, Save, SaveAs... 

Object must be Workbook. If filename is omitted, uses a default name. Example ActiveWorkbook.Save See also Close, Open, SaveAs... 

Circular.xls shows how to use an intentional circular reference with the Newton-Raphson method to find the roots of a pol)momial. A "Circular Reference" error message will be displayed upon opening the workbook. 

MenuDemo2.xls is a Workbook Open event procedure that installs a new menu command in the Tools menu. 

MenuDemo3.xls (not discussed in the text) is a Workbook Open event procedure that checks to see whether the new command has already been installed before installing the menu command in the Tools menu. 

---