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.
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.
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:
- set an reference to the VBA Extensibility library
- enable programmatic access to the VBA Project (not required on a Mac)
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.
Click OK when the confirmation message appears.
After the MacroFlowChart sheet has been created, you can click the button there, to update the lists.
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.