Share via

Listing all variables in a code module and their frequency of use

Anonymous
2011-12-08T17:23:20+00:00

Over time, I have added over 100 variables to my code module.  Although I'm not certain, I believe some of those variables are no longer used.  Before I proceed and write a subroutine that will list all the variables contained within a code module and the number of times each variable is used, I was wondering if anyone knew of such a subroutine that already existed.

For example, Chip Pearson has put together some excellent subroutines and functions for programming the VBA Editor, but none of them appear to accomplish what I'm trying to do.

Not only do I want to remove variables that are no longer used, I also want to consolidate those variables that are used only 1 or 2 times.  Hence, the reason for wanting to have a frequency count for each variable.

Any guidance would be greatly appreciated.  Thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-03-19T14:06:07+00:00

    I believe I finally found a solution.  VBA MZ-Tools 3.0 (http://www.mztools.com) is an add-on that contains numerous helpful tools for VBA developers.  In particular, there is a tool called "Review Source Code" that, among other things, identifies all the unused variables at both the module level and procedure level.

    To the Excel Moderator, could you kindly mark my question as Answered (even though I answered it myself)?

    Thanks.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-12-08T21:53:57+00:00

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-12-08T20:44:20+00:00

    Might give this a try:

    http://www.thecodecage.com/forumz/view.php?pg=vbacleanup

    It requires you to register for The Code Cage, but that's not such a bad thing.

    HTH,

    Eric

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-12-08T19:50:41+00:00

    Thanks for your solution.  Yes, I have Option Explicit at the top of my module.  Unfortunately, I have multiple variables on a single DIM line (e.g., Dim i2 As Integer, j2 As Integer, k2 As Integer, r2 As Integer).  Also, I currently have over 100 variables in my module, so using your solution would take a considerable amount of time.

    Thanks all the same.

    Was this answer helpful?

    0 comments
  4. Steve Rindsberg 99,161 Reputation points MVP Volunteer Moderator
    2011-12-08T19:20:10+00:00

    One simple trick is to comment out the variable's DIM statement, then see if the code compiles.  If not, it'll land you on the spots where the variable is used.

    This assumes that you've got Option Explicit at the top of each of your modules.  If not, it's good practice to add it.

    Was this answer helpful?

    0 comments