5.14  Creating And Using Macros  

5.14.5 Documenting Macros  

It is a good idea to add comments or document the macros so that you or other user can understand how they work. Macros are stored on a special sheet in your workbooks called modules. A module is a sheet containing the visual basic code that the macro recorder writes when you record a macro. Modules can contain only macros and comments along with titles and descriptions. You can edit the macros on the module sheet to add additional comments to explain the particular steps and you can edit the steps themselves to change or correct them without recording the macro again. For example, if you misspell a word while recording a macro you can edit the spelling in the module rather that re-recording the entire macro.  

Examine A Macro

In this exercise, you open the module that contains the macros and take a look at them.

1.       Click on the tools menu bar and select Macro. Within Macro, choose Macros…

2.       Macro dialog box opens.

3.       Click on the Step Into Button.  

The Module1 sheet opens, with the Header_Info macro displayed. The menu bar changes to include menus and commands specific to working in a module. The code in front of you is Microsoft Visual Basic code, which Microsoft Excel uses for macros.  

Some text on the module sheet appears in green, some appears in blue and some appears in black
Please note that these colours are quite dim on few monitors and you may have to see closely to differentiate these colours.

The green text is reserved for comments and is ignored by Microsoft Excel when a macro is run. The blue text indicates keywords that Microsoft Excel recognizes which in this case are the beginning and end of a macro step.

The very first line is Sub Header-Info( ). This lines a currently highlighted and indicates the beginning of a macro called “Header-Info”.

In next line, the title “Header_Info Macro” and the description appear as comments in green. After this, there are four more lines of comments including two blank lines. Comments are always preceded by an apostrophe, which informs Microsoft Excel that they are simply the comments.

After the comments, everything else is in black, meaning that everything else is a step in the macro, until you reach the line that reads “End Sub” in blue. “End Sub” indicates the end of the macro commands.

In order to document your macro you can add comments to each section. Comments can be added in a way so that they describes a section immediately above the section or immediately following the section. It does not matter which you choose, as long as you are consistent.

To add a comment, type apostrophe and then the comment; the comment will turn green automatically after you click away from the line. The apostrophe tells the program that the text is a comment rather than a step.

Adding comments will not affect the macro when you run it, as long as your remember to add the apostrophe at the beginning of the line. If you do not, you will see an error message when you try to run the macro again.

Steps to add comments in a macro

1.  On the Module1 sheet, click at the end of the Sub Header_Info0 line, after the closed parenthesis.

2. Press ENTER twice, press TAB and then type ‘Adds heading to the worksheet’
   Remember to type the apostrophe first. Pressing ENTER twice adds extra line spacing, which makes the macro easier to read.

3. Click or press Enter at the end of this line. The new comment turns green. Your macrocode should look similar to figure 14.12.

4. In a similar fashion you can add more comments at the desired locations.

5. Once all the comments have been added, click on the Save button.

Copyright © 2001 Selfonline-Education. All rights reserved.