AET VBE Tools v1.7

They are finally ready!

This verson includes 3 additional tools, another option to the existing Cleanup Project Code tool, and there are also versions for Word and PowerPoint.

Here’s a description of what’s new.

1. Rename Userform Controls
This tool allows you to batch rename all controls on a userform via a simple Find and Replace interface. In addition to changing the control names, it also changes code with the old control name to the new control name.

So, if you want to change all controls that start with “CommandButton” to “btn” or “cb”, etc, this is the way to do it all at the same time!

2. Make Project Variables List
The bigger the project, the more complex it becomes.

This tool analyses your code and makes a list of variables and constants.

Details include:

  • Module Name (Where the variable or constant is)
  • Scope (Public, Private, Type or Procedure)
  • Location (Declaration or procedure name)
  • Name (Variable or constant name)
  • Type (Variable or constant type, eg String, Long, etc)
  • Const (True or null, to discern between variables and constants)
  • Const Value (Value if constant)
  • Found in Project (Count within project)
  • Found in Report (Count within report)
  • Modules Count
  • Procedures Count
  • Unique Values
  • Duplicate Values

    More features are planned in the near future.

    3. Copy Code for Web
    If you need to show your code on the web, normal indenting won’t show. The way to get around it is to convert indent spaces to “ ”.

    This tool does that and copies the code to the clipboard so you can paste it where required.

    4. Delete Orphan Code
    This feature was added at the request of Kellsens Willamos, who has supported me and the development of these tools from the beginning.

    What’s “orphan code”?

    When developing, did you ever click userform controls by mistake? Maybe you get something like this.

    Private Sub lblSelectProject_Click()

    End Sub

    Chances are you don’t want the code, and if there’s nothing between the first and last line, it’s a fair bet that you don’t. The Delete Orphan Code tool looks for code like this and gets rid of it.

    I’ve added it as an option on Cleanup Project Code. To include it, tick the checkbox on the Settings form.

    As mentioned previously, I’ve made AET VBE Tools shareware. But when you see the price, I hope you will agree they are well worth the cost. (And you will get future versions free of charge!)

    You can download them here.

    P.S. I’m looking for affiliates and translators. If interested, email me at aengwirda at gmail dot com.

  • AET VBE Tools v1.6.1

    Over the last week, I made some changes to my AET VBE Tools.

    Although still free, I want to ensure they are as good as I can make them for you, before I release a paid version.

    This is what has been done –

    An indenting bug was fixed for Select Case constructs when working with Projects and Modules.

    I also improved the indenting of Add Line Numbers code.

    When adding the date and time to text files being exported, the code has been adjusted to show the correct time.

    I made a change to the Copy Code and Compare Code userforms. Sometimes duplicate file names were showing.

    I edited Highlight Code In Excel so that individual Case statements are also highlighted with Select Case constructs.

    Case Statements

    Note: It is a big help with regards to visibility to see them highlighted this way. Unfortunately I had to hard code that part, so it will only work if your tab settings are set to 4. (In the VBE, Tools, Options, Editor, Tab Width). If I find a way to determine this programatically, I will adjust the code to suit. Alternatively, if you know a way, please leave a comment.

    As a small bonus, I also added the functionality to delete Debug.Assert and Stop to the Cleanup Project code.

    Download the new version (now v1.7) here.

    AET VBE Tools

    Late last year, I started work on a new set of VBE tools. It’s an extension of a code indenter that I made several years ago. I thought it would be nice to update it.

    So far I’m up to Version 1.6. It’s freeware (sorry, now shareware), but I’m starting work on a shareware version (v1.7) that will have more stuff. Anyway, here’s what I blogged about it on my site. Give it a try if you like!

    Access AET VBE Tools by right clicking within the active code pane.

    Here are the tools.

    Indent Code
    Indent code within the active VB project, module, procedure or selected text.

    Add Line Numbers
    Add line numbers to code within the active VB project, module, procedure.

    Delete Line Numbers
    Delete line numbers from code within the active VB project, module, procedure.

    Export Code
    Export code from the active VB project, module, procedure to text files.

    Compare Code
    Select modules from projects.

    Code from both modules will be exported to worksheets in a new workbook.

    Code that exists in one module, but not the other (and vice versa) will be highlighted.

    Copy Code
    You can copy code/modules between projects.

    Standard modules, class modules and userforms will be be replaced if they exist (have the same name), or added if they don’t exist.

    ThisWorkbook code will be replaced.

    Sheet module’s code will be replaced if the sheet exists, or worksheets will be added with the new code if they don’t exist.

    Macro Comment Tools
    A handy way to add generic comments to all macros in the active project or module.

    Insert Code Snippets
    Tired of entering the same code all the time? This makes it easier.

    Run Favourite Macros
    Yes, you can already run them from your Personal workbook.

    But now you can also export your favourite code to text files. Note: Not all code will run. This is a experimental tool, but I have found it quite useful when coding myself.

    Multiple Find And Replace
    You can find and replace code with several fields at once. Fields are saved between sessions.

    Cleanup Project
    Just 2 options at the moment. You can delete lines of code that have “Debug.Print”, and also delete excess blank lines. (Only a single blank line will remain)

    Highlight Code In Excel
    Export your code to a worksheet in a new workbook. Selecting cells in Column A that have keywords like If, With, Select, etc will be highlighted so you can see where that part of the code begins and ends. This is very beneficial to your mental health when trying to figure out what connects with what in those long, long procedures.

    Last, but not least, there are various settings available.

    Download AET VBE Tools v1.6 (v1.7) here.

    AET Excel Utilities

    Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.

    Update
    So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!


    I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.

    But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.

    So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).

    Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).

    Worksheet Tools

    Rows And Columns

    Formula Tools

    Deletion Tools

    Object Tools

    Export Tools

    Text Tools

    Number Tools

    Time And Date

    Chart Tools

    Path And Folder

    Workbook Tools

    Developer Tools

    Fun And Games

    Other Utilities

    Cell Menu

    Row Menu

    Column Menu

    Sheet Menu

    Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.

    In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.

    Pimpin’ My Site

    When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.

    Having a vivid imagination, this is what went through my head.

    Yo Dawg!

    Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)

    Here’s some stuff I’ve been working on recently.

    A multi-field Find and Select/Replace tool.

    AET Find and Replace

    Although a bit old, (like me), some of the code came from this.

    AET Cell Watch Form

    Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)

    An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!

    AET Status Bar

    And some games. (For the kids, but you can play too)
    Grrr...
    That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?

    Andrew’s Excel Tips

    Just a quick post to let everyone kind enough to visit – my site address has now changed to www.andrewsexceltips.net (formerly www.andrewsexceltips.com).

    I’ve been blogging for over 4 years now, and although I don’t post as often as I used to, I’m still cooking up new stuff to add.

    Thanks to Dick and my other great Excel friends for their support and inspiration!

    DIY Context Menu

    I use 2 computers at work. One has Excel 2003, the other has Excel 2007. I’ve been using the one with 2003 longer and had a few macros stored in my Personal workbook. I was in the process of copying and pasting them into the other computer after sending them there by email. (Yeah, I know I can install both Excel versions in the same computer but this is how things turned out and I find it handy for testing)

    Anyway, I wondered how I was going to call them. I had the macros in the 2003 computer assigned to toolbar buttons with icons but how about 2007? I could use the QAT but realized the face ids were going to be different. Not such a big problem, but I didn’t feel like going through the drama of selecting a whole lot of new ones, not to mention updating personal workbooks for both computers every time I added any new code. So I came up with the below right click menu addin that I use for both computers on a shared drive.

    Just paste or write any macro into the addin and update the menu by pushing “Make Right Click Menu”, either in the VBE or right click menu itself to add and save. The module names in the addin act as sub menu names and the macro names get added as menu items. Numbers get added to act as keyboard shortcuts.

    Also, there is some code to add spaces to module and macro names so that Private Sub DeleteAllFilesInFolder() in module MiscellaneousMacros would show in the menu as DIY Context Menu – Miscellaneous Macros – 1. Delete All Files In Folder

    It’s a dynamic menu and a pretty simple one to use. Here is the download link if you want to try it. Some example macros are also included. I think it should work with Excel versions 97 – 2007.