Document Your Excel VBA Procedures

In a complicated Excel file, you might end up with several code modules, and it's easy to lose track of what's connected to what.

Recently, Professor Lee Townsend, from the University of Hartford, sent me the code that she uses to document a workbook's code. In her email to me, she warned that, "The code is not very polished…I am a professor and optical physicist by profession, not a programmer. I program Excel for fun and necessity."

I tested the code in one of my files, and it worked very well, showing a list of all procedures, plus a list of which procedures are called by others. Maybe you'll have suggestions for improving the code, but I didn't experience any problems with it.

townsendcode05 

Building the Code

On her web page, Professor Townsend described why she created this code, and how it works:

  • "In the process creating a somewhat complicated Excel VBA workbook I found I needed a code that would give me a procedure flow chart, i.e. what procedure calls or references what procedure."

To write the code, Professor Townsend says she relied heavily on Chip Pearson's "Programming The VBA Editor" – a great source of Excel information.

How the Code Works

The code runs in Excel 2010, and on the Mac, in Excel 2011. However, the code does not handle user-defined classes, because she doesn't use those.

When the code runs, it creates a sheet named MacroFlowChart, if one doesn't already exist. It lists the workbook's procedures and shapes, and the lists go across to column Z, so scroll across to see all the information.

There are buttons at the left of the sheet, which let you sort and update the sheet.

townsendcode02

Use the Code in Your Workbooks

If you're adding the code to your own workbook, read the instructions in the code comments, starting at line 39 in the genProcedureFlowChart module.

The instructions tell you how to:

  1. set an reference to the VBA Extensibility library
  2. enable programmatic access to the VBA Project (not required on a Mac)

townsendcode03

Then, go to the View tab on the Ribbon, and click Macros.

Select the genProcedureFlowChart_Main macro, and run it, to create the MacroFlowChart sheet.

townsendcode01

Click OK when the confirmation message appears.

townsendcode04

After the MacroFlowChart sheet has been created, you can click the button there, to update the lists.

townsendcode05 

Download the Sample File

Professor Townsend generously offered to share her code, so I've put a sample file on the Contextures website. Go to the Excel Sample Files page, and in the UserForms, VBA, Add-Ins section, look for "UF0018 - Excel VBA Code Documenter".

The sample file is in Excel 2007/2010 format, and is zipped. It contains macros, to be sure to enable macros if you want to test the file. There are instructions on the intro page, and comments in the code.

You can download another sample file from Professor Townsend's web page, in the Excel section. If you have any comments or suggestions, she would like to hear from you.

________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

2 comments to Document Your Excel VBA Procedures

  • Hi Debra,

    this looks great! I wrote a Code Documenter for Access (posted on my website) and planned to expand it to document code in Excel projects too ... maybe this does what I want :) am anxious to have some time to run this :)

    thanks

    Warm Regards,
    Crystal

    *
    (: have an awesome day :)
    *

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>