• Home
• Products
  Our Product List Productivity Suite New Products Analysis Products Charting Products Data Products Downloadable Books Macro Products Specialty Products Time Saving Products Suggest a product or feature Workbooks and Templates Products by Others Windows Products iPhone Applications
• Save
  Volume discounts Past customers discounts
• Download
  Download Information Download Purchased Software Download Upgrades
• Support
  Support How to Contact Us Top Support Questions Ordering FAQ's Money Back Guarantee Product FAQ's How to Open and Use Our Products Support via GoToMeeting Excel Problems & Solutions How to repair Microsoft Excel Provide feedback
• Company
  About Add-ins.com LLC How to Contact Us Customer Feedback Policies License Agreement
• Blog

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

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:

• For excel 200-2003, select Tools, Macro, Security.  the following dialog appears:

• 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.