Introducing the Visual Basic Editor


When you clicked OK in the Record Macro dialog box, Excel created a container, called a module, for the new VBA code, storing the module in the active workbook. As you carried out the actions for the recorder to record, Excel transcribed those actions into the new module.

The module doesn't appear with the other worksheets and chart sheets in the workbook. To view it, click Visual Basic in the Code group on the Developer tab. This takes you to the Visual Basic Editor. In the upper-left corner of the Visual Basic Editor, you'll find a small window known as the Project Explorer:

image from book

Yours will look different depending on how you've named the current workbook and what other workbooks happen to be open. But in any case, you should see the entry Modules with an outline control (a plus sign) beside it. Click the outline control, and then double-click the entry Module1 that appears. To the right of the Project Explorer, you will see the Code window for Module1, which is displayed in Figure 26-4.

image from book
Figure 26-4: Excel stores the VBA code for each action you recorded in the module. You can inspect and edit it in the Visual Basic Editor.

As you can see, a module looks like a window you might see in a word processor. The menu bar above the module includes menus for editing, debugging, and running VBA code. In the module, you can review, type, copy, move, insert, and delete VBA statements and comments, just as you might manipulate text in a word processor. From the Visual Basic Editor, you can switch back to your workbook by clicking the Excel icon at the left edge of the toolbar. From there, you can return to the Visual Basic Editor by clicking Visual Basic in the Code group on the Developer tab. From either place, you can switch to the other by pressing Alt+F11.

The Visual Basic Editor is a big place, full of interesting details, but for now we'll focus only on the code we've recorded. The first and last lines act as the beginning point and the endpoint for the macro. A Sub statement starts the macro and names it, and an End Sub statement ends it. You'll notice that special VBA terms, called keywords, appear in dark blue. (You can view and change the colors assigned to various elements of a macro by clicking Tools, Options in the Visual Basic Editor and then clicking the Editor Format tab.)

Below the Sub statement are two comment lines, displayed in green. (If you typed a description for your macro in the Record Macro dialog box, you'll find an additional comment line.) Comment lines begin with the apostrophe character and are ignored when you run the macro. Their only purpose is to help you (or anyone else looking at your code) understand what you've done and why.

The statements following the comments are the meat of the macro-the code that does the (presumably) useful work. Our simple macro includes four lines that select cells and three lines that enter data.



Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net