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.
I am able to add-in and use AET VBE Tools on a Vista machine with Excel 2010, but on a Windows 10 machine with Excel 2010 it will not run (does not show on menu). FYI, I was able to add AET VBE Tools to “add-ins” and it showed up correctly on the list, and I did add the directory to the Trust Center.
Is this a known problem?
Thank you for your help
Hi Ron,
2 thoughts. Is it in the AddIns folder or did you browse to another location? I had somebody else ask about the same thing, and it was fixed after he moved all addins, including mine, to the AddIns folder.
Also, adding the folder to Trusted Locations should work, but also try right-clicking the addin within the folder and selecting Properties. If there is an Unblock checkbox, check it, then click Apply, then OK.
Let me know if either of the above is a solution. Thanks!
Apparently the second suggestion works. The extra step is due to a recent security measure by Microsoft. I have updated my read-me file accordingly.
Andrew,
Thanks for your quick response, much appreciated, and it worked! For other folks perhaps just reading this post:
If you’re on a Windows 10 machine with Excel 2010 and you can’t load the addin:
“try right-clicking the addin within the folder and selecting Properties. If there is an Unblock checkbox, check it, then click Apply, then OK.” Fixed the problem.
These are great utilities, I highly recommend them to all Excel VBAers. Andrew, when you do release these as shareware, I will gladly pay to help support your fine work.
Ron
You’re welcome Ron, and I’m glad you like the tools.
I just released a patch and am writing a new post regarding the details. It’s uploaded so you can download it now. Just save over the older file.
I get following error (this content is form event viewer) after clicking ok in addins activation box. Did you encounter such problem?
Log Name: OAlerts
Source: Microsoft Office 14 Alerts
Date: 2017-02-19 20:43:05
Event ID: 300
Task Category: None
Level: Information
Keywords: Classic
User: N/A
Computer: RWAMW35247.emea.roche.com
Description:
Microsoft Excel
Microsoft Excel cannot access the file ‘C:\Users\sasinskm\AppData\Roaming\Microsoft\AddIns\AET VBE Tools.xlam’. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
P1: 100099
P2: 14.0.7015.1000
P3: 2ono
P4:
Event Xml:
300
4
0
0x80000000000000
603
OAlerts
Microsoft Excel
Microsoft Excel cannot access the file ‘C:\Users\…\AppData\Roaming\Microsoft\AddIns\AET VBE Tools.xlam’. There are several possible reasons:
• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.
100099
14.0.7015.1000
2ono
I get an “Unable to load DLL” error when I try to run the add-in in Excel 2016 on a Windows 7 machine. Add-in is in addin folder and has no Unblock box in properties.
Mike,
Sorry, no idea what’s going on there, but perhaps my reply to Nolan below might shed some light.
Nolan,
It sounds like a reference is missing. As far as I know, I’ve only used standard references and have tested for backwards comparability on previous Excel versions going back to 2010.
Chip Pearson has some information on his site.
http://www.cpearson.com/excel/MissingReferences.aspx
My addin uses the 4 core references he mentions, as well as Microsoft Forms 2.0 and Microsoft Visual Basic for Applications Extensibility 5.3. Both have been standard for a very long time.
Should not be related, but have you checked “Trust access to the VBA project object model”? (Select the File tab or round Office button at the top left of Excel, then Options, Trust Center, Trust Center Settings, Macro Settings. The checkbox will be in the Developer Macro Settings section)