Document Your Excel VBA Procedures

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. Here’s how you can document your Excel VBA procedures.

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.

________________

6 thoughts on “Document Your Excel VBA Procedures”

  1. 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 🙂
    *

  2. Added the code to a large xlsm file but I am running into a problem with ‘subscript out of range’. Since the code can’t be run in debug mode, I don’t know where to look for the problem in the documenter code – it does seem to be quitting about the same place….

    1. If you didn’t find the error, let me know. I will be happy to help. Note – I only run Excel on my Mac. I suspect it was developed in Excel 2011.
      Lee
      [email protected]

      Note: The website was created by UHa so my Excel files are not there. I still have the old website if you are interested. Also, since I retired last June, the website might disappear. You never know!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.