Monday, 27 March 2017

AET VBE Tools 1.7.1

It has been a busy couple of weeks, but I added some new features to my VBE tools.

Line Continuation
Here's a pic of what's available.



My version of line continuation adds spaces and underscores after And and Or keywords.

So something like
If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then

becomes
If Left$(LTrim$(.Lines(lCodeLine, 1)), 4) = "Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 11) = "Public Sub " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 9) = "Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 17) = "Private Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 16) = "Public Function " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Property " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Private Property " Or _
Left$(LTrim$(.Lines(lCodeLine, 1)), 12) = "Public Property " Then


Much easier to read. But there are 2 things to watch out for.

1. If And or Or is between quotation marks, an error will occur. If this happens, click Undo last operation.

The best way to check for errors is to use Compile VBA Project at the top of the Debug menu after adding line continuation.

2. I haven't experienced problems myself, but I occasionally read about people whose code doesn't work properly if there is too much line continuation in a project. Something to keep this in mind anyway. Again, I recommend compilation of code and saving your work at regular intervals in case things go wrong.

New Cleanup Project Options
In addition to deleting excess blank lines (if you have several blank lines between code, only one will remain), you can now choose to delete all blank lines too. Not only that, you can delete comments and indenting.



Personally, I like to have blank lines between regular code and indenting for readability, as well as comments to keep track of what is going on. But when the code is ready for distribution, the 3 new options can drastically reduce the size of your project which is definitely a good thing.

Download the tools here.

P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

Sunday, 12 March 2017

AET VBE Tools v1.7

Finally they're ready!

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.



    AET VBE Tools are no longer free. But I'm keeping the price low so they are affordable. (And you will get future versions free of charge!)

    You can download them here.

    P.S. Tick the G+1 icon at the bottom of my post to tell me if you like what you see!

  • Monday, 6 March 2017

    AET VBE Tools v1.6.4 and Overall Progress

    Latest patch - 2 bugs have been fixed, and one change has been made to certain wording.

  • Fixed an indentation bug when the first procedure of a module was at the very top (No Option Explicit and no Declarations).

  • Also fixed a bug with Macro Comment Tools when more than one tool was used with the form still open.

    And I changed the wording of some messages to simplify translations for international versions!

    2 translations are being prepared - Portuguese and Japanese - and I want your help to translate into other languages!

    Please contact me the email address below.



    Other Progress
    It's been a super busy week getting the tools ready for translations, but there's more good news!

    I've started work on a version that works with Microsoft Word and plan to make a version for other Office products.

    Will keep you updated!

    For now, download AET VBE Tools v1.6.4 here.

    Ticking the G+1 icon at the bottom of my posts fuels my enthusiasm!

  • Tuesday, 28 February 2017

    AET VBE Tools v1.6.3

    This patch concerns another change to the Undo code, and is in case you unchecked "Trust access to the VBA project object model" after launching Excel. Perhaps unlikely, but I like to avoid potential bugs whenever possible.

    v1.6.3 is available here.

    Also, thanks to everybody who ticks the G+1 icon at the bottom of my posts. It gives me incentive to keep doing my best!

    Sunday, 26 February 2017

    AET VBE Tools v1.6.2

    Just a quick post to let you know the a new patch has been uploaded.

  • Indentation had a bug fixed when there were comments immediately after "Then" in If... Then code.

  • Cleanup Project Code had a minor tweak to alert you when no options were chosen.

  • I disabled Undo when a different tool is run.

    Undo should be used (if you wish to) immediately after you use a tool. So having it still enabled after using another tool may have caused confusion.

    Many thanks to those who have helped me improve these tools. Your support is appreciated!

    I'm happy to say the next version is already finished with 3 new tools, I'm just waiting for the online store to get things set up. Hoping for early March at this stage.

    Until then, v1.6.2 is available here.

    Update: I just released patch v1.6.3 that has another improvement to the Undo code.

    If you like my tools, please click the G+1 icon on this post and/or my blog's sidebar to let me know ;-)

  •