List variable usage in workbooks. Is this a usefull tool to have?


Active Member
Feb 18, 2015
Hello fellow developers.
I would like you to give feedback on this macro I coded.

  • educational value: the code contains comment lines to explain various coding techniques. Did it help to understand how some things can be done?
    • Working with a dictionary
    • Return multiple values from a function
    • finding files in a folder and its subfolders
    • navigate code modules
    • using named ranges
    • Ways to call subs in other modules
    • writing debug information to a text file
    • working with regular expressions
  • usefullness: do you think this helps to write higher quality code?
  • things to add to make it even more usefull

There is a practical problem. As you can see in the 2nd sheet below the code is 1214 lines long. So I decided to publish the workbook here!AmC_nB9H08y-gdk_qY3qh9EzHVMltg
If you prefer the code in a post let me know.

I designed a utility macro that reads all the code modules of all *.xlsm files in a folder and its subfolders.
Next it shows you, in an excel table, all the variables used.
To wet your apetite look at the following 3 excel sheets. Here I selected the folder on my pc where the workbook with this utility is located (the macro looks at its own code)

1. The start sheet to select the top folder and start the macro.

2. the modules sheet shows you the number of code lines found per module per workbook.
Excel Workbook
1total 1.214
2WorkbookProjectModuleCode lines
3build symbol tableprSymbolsmContSplit224
4build symbol tableprSymbolsmGlobal93
5build symbol tableprSymbolsmHelper127
6build symbol tableprSymbolsmReport84
7build symbol tableprSymbolsmScanFolder55
8build symbol tableprSymbolsmSymbolTable631

3. the Symbol table sheet with all variables found, listed in an excel table so it's easy to filter or reorder the results.
Excel Workbook
2arrayClauseStringbuild symbol tablemSymbolTablemodule
3ArraySpecStringbuild symbol tablemSymbolTablefunction tokanizelocal
4cLongbuild symbol tablemReportfunction reportSymboltableToSheetlocal
5callDepthLongbuild symbol tablemGlobalproject
6cCOLONString Constbuild symbol tablemGlobalproject
7cCOMMAString Constbuild symbol tablemGlobalproject
8cCONSTString Constbuild symbol tablemGlobalproject
9cDECLAREString Constbuild symbol tablemGlobalproject
10cDIMString Constbuild symbol tablemGlobalproject
11cDQUOTEString Constbuild symbol tablemGlobalproject
12cENUMString Constbuild symbol tablemGlobalproject
13cFUNCTIONString Constbuild symbol tablemGlobalproject
14cGLOBALString Constbuild symbol tablemGlobalproject
15charIndexLongbuild symbol tablemContSplitfunction maskCharInsideLiteralslocal
16closeFlagBooleanbuild symbol tablemSymbolTablefunction procesSpecifiedWorkbookslocal
17cMASKStringbuild symbol tablemGlobalproject

(just the first few lines. Last row=146)

This information can help you in several ways

  • Naming conventions: See if all variable names meet agreed naming rules
  • Possible scope issues: if a name is used local scope and also module scope, this can lead to unexpected results (bugs) and should be avoided
  • consistency: if a variable is declared as different types, some things are unclear and should be investigated

I'd appreciate all responses.

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics