Usefulness for VBA programmer

If you pronounce the word “macros” with a breath of horror and an accent on the second syllable, and the phrase “Visual Basic for Applications” sounds like a spell to you, then this article is not for you. In any case, for now 🙂

If you have at least some experience in programming macros in VBA in Excel, and you do not plan to stop, then the selection of useful add-ins and programs below should be (at least partially) useful to you.

MZ-Tools – “Swiss knife” for a programmer

After installation in the VBE editor in the menu tools submenu will appear MZ-Tools and a new toolbar for quick access to the same functions:

He knows how to do a lot. Of the most valuable, in my opinion:

• Automatically add “blank fish” to create procedures, functions, event and error handlers with the correct naming of variables according to the Hungarian system.
  
• Copy controls on userforms along with their code.
  
• Make bookmarks (Favorites) for procedures and quickly move to them in a large project.
• Split long lines of code into several and assemble back (split and combine lines).
  
• Issue detailed statistics on the project (number of lines of code, procedures, elements on forms, etc.)
• Check the project for unused variables and procedures (Review Source)
• Create your own base of code templates (Code Templates) for typical cases and quickly insert them into new macros later.
• Automatically create a long and scary string to connect to external data sources via ADO.
  
• Attach hotkeys to any function from the add-on.
  

An unambiguous must-have for a programmer of any level. If you have the latest version of Office, then be sure to download the latest version of MZ-Tools 3.00.1218 dated March 1, because. it fixed a bug when working with Excel 2013.  

Download link MZ-Tools

Smart Indenter – automatic indentation in the code

It does one simple but very necessary operation well – it automatically indents tabs in VBA code, clearly highlighting nested loops, condition checks, etc.

It is very convenient to assign this action to any convenient keyboard shortcut in the section Indenting Options and do it with one touch.

Unfortunately, the author of the program abandoned it in 2005 (why, Carl!?) and the latest version on the site is for Excel 97-2003. However, the program works quite well with newer versions. The only caveat: if you have Excel 2013, then before installing Smart Indenter, you must first install the latest version of MZ-Tools, because. it contains the dynamic library necessary for Indenter’s work.

Download link Smart Indenter

VBE Tools – micro-tuning elements in forms

Aligning controls (buttons, input fields, text labels, etc.) on a complex form can be a pain in the ass. Standard binding to the editor grid via the menu Tools — Options — General — Align Controls to Grid sometimes it doesn’t help much and even starts to get in the way, especially if you need to move, for example, the button just a little bit. The VBE Tools add-on will help in this matter, which, after installation, displays a simple panel where you can fine-tune the size and position on the form for the selected element:

Position shifting can also be done with Alt+arrows, and resizing with Shift+Alt+arrows and Ctrl+Alt+arrows.

Also, by right-clicking on an element, you can rename it immediately along with the code.

Download link VBE Tools

VBA Diff – Finding Differences in Code

This tool will probably be more useful for professional VBA programmers when creating large and complex projects or collaborative development. Its main function is to compare two projects and visually display the difference in code between them:

There is a 30-day free period, and then the add-on will ask you to pay 39 pounds for it (about 3.5 thousand rubles at the current exchange rate).

Frankly speaking, it came in handy in my life only 3-4 times on super-large projects, but then it saved me several days and a lot of nerve cells 🙂 Well, there is always, of course, a free alternative: export the code to a text file (right-click modulo – Export) and compare them later in Microsoft Word using the command Review – Compare Documents, but with the help of VBA Diff it is an order of magnitude more convenient.

Download link VBA Diff

Moqups and Wireframe Sketcher – interface prototyping

When creating complex interfaces for user interaction, it is very convenient to design in advance the approximate appearance of dialog boxes, i.e. execute prototyping. In fact, it turns out to be much easier than redoing ready-made forms and their code later. I remember once in one of the projects the customer asked to make a “menu”, meaning “tabs”. Half a day of work down the drain 🙁

There are a huge number of paid and free programs of different levels of complexity and power for these tasks. I have tried about a dozen such programs and services, and recently I most often use Moqups:

This is an online editor that:

• Does not require installation of separate programs. You can always come to the client’s office and open-show-correct the created interface right on the site.
• Contains all the main elements of dialog boxes (labels, buttons, lists, etc.) in versions for Windows and Mac.
• Allows you to export the created interface in PNG or PDF formats or send a link to the client for viewing online.
  
• Actually free. There are limits on the number of graphic elements, but I have never managed to go beyond them. If you run out of space or want to store several large projects at once, you can always upgrade to the premium version for $99 per year.

In general, for the tasks of a developer in VBA – more than enough, I think.

If anyone fundamentally needs an offline option (to work without Internet access on the seashore, for example), then I recommend Wireframe Sketcher:

After a free demo period for 2 weeks, he will ask you to buy for the same $99.

Link to Moqups

Download link Wireframe Sketcher

Invisible Basic – code obfuscator

Unfortunately, it is not possible to securely lock the source code of your macros with a password in Microsoft Excel. However, there is a whole class of programs called obfuscators (From the English. obfuscate – confuse, confuse), which change the appearance of the VBA code in such a way that it will be extremely difficult to read and understand it, namely:

• names of variables, procedures and functions are replaced with long meaningless character sets or, conversely, with short alphabetic incomprehensible designations
  
• visual tabulation indents are removed
• are removed or, conversely, line breaks are randomly placed, etc.

Frankly, I’m not a fan of using these methods. In particular, with PLEX, I decided that it would be better to give buyers of the full version open, understandable and commented source code – this seems to me more correct. Nevertheless, my fellow programmers have repeatedly had cases when such a program would be very useful (the programmer did the work, but the client did not pay, etc.) So if you need it, know where to get it. “We are peaceful people, but our armored train…” and all that.

Download Invisible Basic

Code Cleaner – code cleaning

In the process of working on a project (especially if it is large and long), “garbage” begins to accumulate in code modules and forms – scraps of VBE editor service information that can lead to unexpected and unwanted glitches. Utility Code Cleaner cleans this muck in a simple but reliable way: exports the code from modules to text files, and then imports it cleanly back. I highly recommend that when working on large projects, periodically carry out such a “cleaning”.

Download link Code Cleaner

Ribbon XML Editor

If you want to create your own tab with beautiful buttons on the Excel ribbon to run your macros, then you can’t do without an interface XML file editor. Definitely, the most convenient and powerful today is the domestic program in this regard. Ribbon XML Editorcreated by Maxim Novikov.

Absolutely wonderful software that:

• will allow you to easily add your own tabs, buttons, drop-down lists and other elements of the new Office interface to the ribbon
• fully supports language
• assists with editing by displaying contextual hints
• can be easily mastered by lessons
  
• completely free

Download link Ribbon XML Editor

PS

For many years, Microsoft has blatantly ignored VBA developers, considering it, apparently, to be an inferior programming language. Rumors periodically slip that the next version of Office will no longer have Visual Basic or it will be replaced by JavaScript. New versions of Visual Studio come out regularly with new goodies, and the VBE editor was stuck in 1997, still not being able to indent the code with standard tools.

In reality, thousands of people are saving hours and days thanks to VBA programmers creating macros to automate day-to-day office data processing routines. Anyone who has seen how a macro in 10 lines of code sends files to 200 clients in half a minute, replacing three hours of stupid work, will understand me 🙂

And more. 

All of the above programs are purely my personal choice and personal recommendation based on personal experience. None of the authors asked me for advertising and did not pay for it (and I would not take it, on principle). If you have something to add to the above list – welcome to the comments, grateful humanity will not remain in debt.