Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    listing variables used in a vba routine

    Hi

    If I wanted to properly document a vba routine, I would find it very useful if there was a utility that could list all the variables used in each subroutine in a vba module. If everyone used Option Explicit and dimensioned every variable, I wouldn't need this of course.
    But there are some, er, persons who don't always Dim every variable.

    Does anyone know if there is such a utility out there????

    zeddy

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Zeddy:

    You might try http://www.mztools.com/v3/download.aspx, I found this on the Web but have never used the tool

    Good Luck

    Tom D

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The ones I have seen (at there is an example at http://answers.microsoft.com/en-us/o...d-b94bbdb2803c by MS MVP Chip Pearson) keys on the DIM to get the variable names.

    The code could probably be modified (but it would be a fair amount of work to do and get the details) to loop through all the code (ignoring the comments) parse the "Words", [space, comma, and parentheses as delimiters, brackets and quotations need to be kept), eliminating the reserved words, text, numbers, "Evaluation ranges" (eg [A1]), etc and then being left with variable names.

    I think it may be easier, to put option explicit, add dim the required variables and then you have the dim list in each. In my mind, if the variables are not DIMmed in the code, then the code should be evalauted anyway to other issues, before attempting to document it anyway. Which suggests fixing it up to some appropriate standard before using as something more than just a quick routine. If documentation is desired for it, to me, it should be also documented within the code as well.

    Just my 2 cents...

    Steve

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    I've written code to document both Excel & Access VBA and pretty print it in Word the Access version even has and Index if procedure & function names. However, I've yet to figure out how to find variables! Good Luck in your search.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Tom, Steve & RG

    Many thanks for your responses.
    I thought I might start with looking at what's on the left-hand side of any equals sign, as in..
    xxxxxxx = whatever

    ..and perhaps look at what's before any As, as in..
    Dim xxx As whatever

    ..then also include searching for stuff like..
    For Each xxxxx In whatever

    I'll check out the suggested links.

    zeddy
    RG: have a great trip to London! I was there last week. But now back for another Thoracentesis at the hospital on Thursday. I've got more drilling sites in my back than the fracking Texas oil companies. Well, eight so far that is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •