How to document your macro code
and why use the Code Documentor for Microsoft Excel
There are several key steps one can do to document macro code and to make it more understandable, both when you are first creating it and when you look at it again months later:
- Add comment lines. Comments lines begin with a single quote: 'this is a comment
- Change the background color of comment lines to light blue. In the VB Editor, select Tools, Options, Editor Format and change there
- Add documentation at key steps in the module and in a module at the front. Typically we name the module "AAA-Documentation"
- Use very long variable names
- Use very long macro names
- Use many small routines called by one routine versus one very long macro. Pass variables back and forth
- Use the Code Documentor developed by Rob Bovey to analyze and document your project
||Customers who looked at this product also looked at:
||Excel 97 to Excel 2007
Macros Made Easy - A downloadable book (Windows help file format) on how to write macros for Microsoft Excel
Macro Examples - Get over 1200 macro examples for Microsoft Excel in a downloadable book (Windows help file format). These examples are new and not a repeat of the ones in Macros Made Easy.
Memory usage: Code Documentor is a VBA add-in. Such add-ins use less memory than COM add-ins and have 5-10 open at a time typically has little to no effect on Excel memory. Use of the Add-In Information Lister to manage add-ins, removing those not needed and then re-installing when needed is best.
The Code Documentor provides a list of the sheets, userforms, and modules in your project, how many procedures (macros) in each, and how many lines of code. It does not list the individual macro names or what variables are being used when calling them. Nor does it list any of the comments found in the macros. The key benefit is knowing if your lines of code in a module is exceeding 600 lines. Code that is more than 600 lines has sometimes been know to become corrupt. It is best to keep modules below 600 lines, and always below 1000 lines.
To use the Code Documentor after installing, open your workbook or add-in containing your code. If the code is password protected, access the project so that the code is viewable. Then, do the following:
- If you are using Excel 2007, select Office Button, Excel Options, Trust Center, Trust Center Settings, Macros. The following dialog appears:
To use the Code Documentor after installing :
- Make a backup copy of your project. This insures you can recover back to it in case use the Code Documentor causes a problem
- From a Microsoft Excel worksheet, if Excel 2003-2010, select Tools, Document Project. If Excel 2007, click on the add-ins ribbon and click on one of the "Document Project" button (there is a bug in the Code Documentor that shows two buttons)
- Select your project and click OK. A documentation worksheet is created.