The Visual Basic Editor

VBA macros are located on module sheets. In Excel 5/95, module sheets were sheets in a workbook, just like worksheets and chart sheets you could click on a sheet tab and view the VBA code. But beginning with Excel 97, the VBA programming environment became much more sophisticated. It is more professional and has more features, but it s also much more confusing for the beginner. 

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 

View menu, or click on the Project Explorer toolbutton 

Sub or Function procedures must be created cn a module sheet. To insert a module sheet, choose Module from the Insert menu. A folder icon labeled Modules will be inserted if you click on this icon the node for Modulel will be displayed. 

ButtonsCustom ButtonsCustomVithUndo —ChemFormat L-4J ToolbarsDriver VBAProject (Vorkbook6) 

---

There are a number of ways of creating macros. The most straightforward is via the Visual Basic Editor, which can be accessed via the Macro option of the Tools menu. Depending on your set-up and the history of the spreadsheet, you may then enter a blank screen. If this is so, insert a Module . This contains a series of user defined procedures (organised into Subroutines and Functions) diat can be run from Excel. 

Figure 13-3. The Visual Basic Editor s Project Explorer window.

To rename a Sub procedure, access the Visual Basic Editor and click on the module containing the macro. The name of the macro is in the first line of code, immediately following the Sub ke)rword. Simply edit the name. 

Activate the Visual Basic Editor. The VBA code module must be visible. 

To protect an Add-In workbook, activate the Visual Basic Editor. Choose Properties... from the Tools menu and choose the Protection tab. Check the Lock Project For Viewing box, enter a password, and Save the workbook. 

As our introduction to using a macro we will write several macros and see how they operate. As our first example we will take a highlighted cell and read the value it contains. Open a new spreadsheet, open the Visual Basic editor, insert a new module, and in it type ... 

To examine the macro code that you have just recorded, choose Macro from the Tools menu and Visual Basic Editor from the submenu. Click on the node for the module in the active workbook. This will display the code module sheet containing the Visual Basic code. The macro should look like the example shown in Figure 13-7. 

If you use Excel 97, 98, or 2000, with the spreadsheet open, type Alt + Fll or select Tools —> Macro —> Visual Basic Editor. Then, select Insert —> Module. The monitor screen will now show an empty sheet, on which you can now type the function. When you are done typing, type Alt + FI 1 to get back to the spreadsheet. From then on, whenever you need to do so, you can go back and forth between the spreadsheet and the function with Alt + F11. 

Starting with Excel 97, the modules are also stored together with the workbook, but they are not as readily visible. To generate a new module in Excel 97, Excel 98, or Excel 2000 requires the command sequence Tools => Macro => Visual Basic Editor, followed by Insert => Module. From then on, you can switch between spreadsheet and module with Alt + F11 if your keyboard has an FI 1 key. Otherwise, use File => Close and Return to Microsoft Excel (or Alt + Q) to move from module to spreadsheet, and Tools => Macro => Visual Basic Editor to get from spreadsheet to module. Unfortunately, you can no longer use the spreadsheet tab to create or switch to a module. In summary, starting with Excel 97 the corresponding procedure is as follows ... 

Select the VBA Editor with Tools => Macro => Visual Basic Editor, which will show you a screen with its left-hand side not just empty but dark, and with its own menu bar. 

In Excel 2003, code can be inserted by going to Tools Macro Visual Basic Editor (Alt + Fll). In Excel 2007 or newer, code can be inserted by going to the View Ribbon and selecting the Macro icon and then View Macro. For both versions of Excel, in the window that appears, enter the name of the function that you desire to create (or edit) and press Create (Edit). If a new function is being created then, in the new window that opens, replace Sub with Public Function. This will allow the new code to be directly accessed from the spreadsheet by typing = FunctionName(Required Parameters). Below, some sample code has been provided that implements the Michaelis-Menten equation. 

Some examples of VBA objects are the Workbook object, the Worksheet object, the Chart object, and the Range object. To get an alphabetic list of objects, when in the VBA editor, click the question mark and then select Microsoft Visual Basic Reference/Objects. 

Fox DA, Boyes WK. 2001. Toxic responses of the ocular and visual system. In Klaassen CD, editor, Casarett DoulFs toxicology the basic science of poisons, 6th ed. New York (NY) McGraw-Hill, p. 565-595. 

---