5. 
Editing Macros
A word in advance: when you open the macros you recorded in the VBA Editor, you will probably not fully understand what you see. You will need to read the tutorial sections on Objects and their Members in order the interpret the macro code.
5.1 The VBA Editor
To edit a macro (or write a macro from scratch rather than recording it) you must start the VBA Editor. This can be done in two ways: You
can access the list of macros, select a macro and click Edit, which will start the VBA Editor and open the
macro you selected; or you can click on Tools/Macro/VBA Editor (2003), or click on Visual Basic on the Developer tab of the ribbon (2007/2010). (If the Developer tab is not visible on the ribbon, follow these instructions. This will start the VBA Editor.
There are several areas in the VBA Editor, but for our purposes we will deal with just two: the "Project Explorer," which is the area in the upper left, and the "Code Window," which is the main area to the right. If you open the VBA Editor by editing a macro, the macro will be open in the Code Window. If you open the VBA Editor by starting the program, what appears in the code area when the Editor program starts depends on which workbooks are open, whether they contain macros, and other factors.
The Project Explorer lists all open workbooks in a tree view. In the VBA Editor these are referred to as "projects." Each project has several parts, which you can see if you expand the project.
5.2 Modules and Macros
For our purposes we will be dealing with a
project's "modules," which is where macros that are to be run by users are stored. Modules are
just a tool for organizing macros. If a project has no macros, it has no modules. You can have one or many modules, and for our purposes it does not matter in which module a macro is located.
When you record a macro, the VBA Editor will automatically create a new module and place the macro in that module. By default modules are named
Module1, Module2, etc., or NewMacros. You can change the name of the module.
Each macro in a module starts with the word "Sub" (for subroutine), and the macro name, followed by a pair of parentheses. The macro commands, or "code," follows. The macro ends with the phrase "End Sub." The VBA Editor automatically inserts a separating horizontal line between the macros in a module.
To look at the macro code of the macros you recorded in Chapter 3, start the VBA Editor, then navigate to the Personal Macro Workbook (personal.xls or personal.xlsb) in the Project Explorer. Make sure "Modules" under that project is expanded. There maybe be one or several modules. Double-click on each module until you open the module that contains the macros you recorded. (You may need to scroll down in the Code Window to see which macros are in each module). The code for the macros will appear in the Code Window.
5.3 Macro Code
A macro actually consists of only the macro commands (and optionally VBA language statements). Inserting formatting such as tabs, indentation, and blank lines into the macro code while you edit it will format the visual presentation of the macro when it is opened for editing. However, this formatting will have no effect when the macro is run. Extra spaces will also be ignored, except where they are part of text enclosed in quotation marks.
You can type, delete, move and copy text in the macro code, as you would in any document. Commands that you insert in the macro when editing or programming must be in the exact form required by the macro language, otherwise the commands will not be recognized and you will get an error. VBA macros are not "case-sensitive." This means that for commands and variable names, upper- and lower-case characters are considered identical. The VBA Editor likes to see the macro commands and VBA language statements (covered later in the tutorial) in initial caps, and will automatically change them if they are typed in a different case configuration.
5.4 Compiling a
Macro
"Compiling" is the process by which a macro is translated into
"computer language" (i.e., a form the computer can understand).
When a macro is first recorded, it is automatically compiled by
VBA. But when a macro is created from scratch by typing
the commands into the macro document, or the macro is edited, the
macro must be compiled before it can run. You do not have to manually
compile a macro, since VBA will compile the macro if necessary when the macro is run. However, if there is
a compilation error when you try to run a macro, you will get an error message. If you
manually compile your macro before you save it, the VBA Editor will catch any compilation errors and they
can be fixed at that point.
To compile a macro, click on Debug/Compile. If there is a problem you will get an error message. There is no indication when the macro compiles successfully (no news is good news), so if you do not get an error message that means the macro has compiled successfully.
5.5 Comments in Macro Code
It is useful to include comments in your macro: you might want to explain how it works, what its purpose is, call attention to certain features, etc. Comments are a great help when editing a complex macro, particularly one that was programmed some time ago or by someone else. Macro code whose purpose and method seem obvious at the time when the macro was originally written can be bewildering when looked at later. Comments appear as text within the macro when the macro is edited, but they are ignored when the macro is compiled and have no effect when the macro is run.
To insert comments in a macro, precede the comment text with an apostrophe (" ' "). When the macro is compiled everything from the apostrophe to the end of the line will be ignored. On multi-line comments, each line must start with an apostrophe.
5.6 Saving Macros
Macros are not saved individually. Instead, all the macros in a workbook are saved when the workbook is saved. So, when you click on File/Save in the VBA editor, you are actually saving the workbook that contains the macro. This has the same effect as switching to the open workbook in Excel and saving it. Since the macros are part of the workbook containing them,
while the workbook is open any changes you make
to the macros in the workbook will be in effect, but they will disappear when the workbook is closed unless you save the workbook.