VBA Utilities

I’ve mentioned MZ-Tools on this blog many times. It’s a great set of utilities for the VBE. You also probably know about Smart Indenter. I don’t use that one myself, but many people do. Ken Puls commented that neither of those work in 64 bit Excel. I’m some ways away form using 64 bit Excel, but it got me thinking…

They say that 95% of the people use only 5% of Excel’s features. Others say that, while that’s true, the 5% is different for everyone so you still need all the features. I’m interested to know which MZ-Tools features you use. For those of you that use it, leave a comment.

I use extensively:

I use sparingly:

  • Add Line Numbers
  • Remove Line Numbers
  • Statistics
  • Review Source Code

Wow, that’s less than I thought it would be. I couldn’t live without these procedures, though. If MZ-Tools went away, I would be writing these myself. So why don’t I? Maybe because everyone uses different procedures and that’s what makes MZ-Tools so universally great. Let’s see. Leave a comment.

20 thoughts on “VBA Utilities

  1. I use SmartIndenter every time I write code, so that’s a real big loss to me. I find that one really helpful.

    As for MZTools, I use Add Module Header, Add Procedure Header, Add Error Handler and Add Both (I use each of these quite frequently.) I also use Review Source Code quite a bit as well. Not so much on the other stuff though.

  2. This is what I’m using:
    Close All Windows
    Add Code Template
    Add Module Header
    Add Procedure Header
    Add Both
    Collapse Projects
    Review Source Code

  3. It varies but mainly

    Procedure and Module headers
    Error Handler
    Review Source code

    I wouldn’t want to be without it – invaluable.

  4. I use mainly:

    Procedure Callers
    Add Line Numbers / Remove Line Numbers
    Add Error Handler

    Used occasionally:

    Review Source code
    Sort Procedures
    Convert Public variable to property
    Split lines / Combine lines

    It is a very cool tool.

  5. On a regular basis:

    Copy controls with code
    Paste controls with code
    Procedure header
    Module header
    Clear Immediate window
    Review source code

    Others occasionally

    I don’t use:

    Line numbering (on/off)
    Split/Combine Lines
    Use of favourites


  6. And my list:
    Close All Windows
    Error Handler
    Add Module Header
    Add Procedure Header
    Add Both
    Add Code Template
    Add and Remove Line Numbers
    Procedure Callers
    Copy/Paste Multiple Chunks
    Erase Immediate Window

    Never tried Review Source Code – neat – will use that one

  7. I use Smart Indenter every 30 seconds or so.

    M-Z Tools is wonderful!

    Procedure Callers
    Add Error Handler
    Convert Public Variable to Property
    Rename Controls with Code
    Sort Procedures

  8. I don’t use Clear Immediate Window (Cnt+G, Cnt+A, Delete – not better, just ingrained in my fingers). I forgot about Close All Windows – add to my sparingly list.

  9. Well, I’ll be the contrarian.

    I don’t use MZ-Tools. Tried it once a long time ago and had some problems with MZ-Tools versions and Excel/VB versions. Rather than give up one of my Excel/VB versions, I gave up on MZ-Tools.

    For the short time I used it, the only thing I really used was Procedure Callers. And, while it’s a PITA, I now make do with VBE’s ‘Find.’

    I can see a value for ‘Select Case Assistant For Enum Expressions’ and for ‘Line Numbering’ (together with Erl).

    But, as far as some of the other features go…

    Procedure header and module header. Why? It’s much better to write self documented code.

    Add Error handler? In a given procedure I may have no error handler, a ‘Resume Next’ handler, a ‘Go to ErrXIT’ handler, a simulated ‘Try…Catch…Finally…’ handler, or some combination of them.

    I imagine something like public var -> property might have some time savings but how much, especially with Intellisense and the VBE’s built-in capabilities? And, I don’t always have a get+let/set wrapper for a variable. The property procedures can, and do, more (or less) processing.

    [Actually, one of the things I’ve almost changed my mind about is the use of public vars in a class module. If I will have a prop get + prop let/set that do nothing more than get+let/set a private variable, why not just use a public var? If at some point in the future, I decide to replace the public variable with a prop get + prop let/set, none of the class clients will be any the wiser…or will they?]

    Smart Indenter? Huh? VBE does the job just fine. And, I ensure that the code is correctly indented as I add/edit it.

    Sort Procedures? Whoa! My procedures are always organized in a functionally logical way. So, thank you but no thank you! {grin}

    That said, given that MZ-Tools is apparently universally loved, maybe it’s time I gave it another try. ;-)

  10. […] use that one myself, but many people do. Ken Puls commented that neither of those work in… [full post] Dick Kusleika Daily Dose of Excel visual basic editor 0 0 0 […]

  11. Add/remove line numbers – brilliant for error handling so everything we do has line numbers
    Proc headers, error handlers – all standardised for all developers with module and proc names
    Sort procedures
    Review Source Code
    Copy controls with code
    Generate XML documentation

    And probably simplest, but very useful, set in the Shortcuts under Options, I have
    Toggle bookmark – ctrl shift B
    Next bookmark – Alt 2
    Previous bookmark – Alt 1

  12. I’ve always wondered why MSFT didn’t purchase the rights to use the features of MZToolz.
    They did “borrow” the Clear Intermediate Window, but only used it in Access 2007, not Excel 2007.

    Also, why doesn’t MZToolz create an updated version and charge for it ?
    Finally, I wish I had the source code to this fine set of tools !!!

  13. Just tried it.

    When I right click on a selection of vba code I see the context choices “indent module code”

    But nothing happens.

    Using Excel 14 (64bit) and vba 7.0

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.