The best free VBA developer tools

Despite all the new possibilities to program Excel (like JavaScript add-ins, VSTO and others), VBA remains the most popular language when it comes to automate and enhance Excel. And just because Microsoft hasn’t invested in either the language itself nor the VBA editor during the last 20 years, it doesn’t mean that you can’t pimp your VBA coding experience with a few extra tools!

Table of Contents

1. Productivity
2. Version Control
3. External editors
4. Unit tests

Productivity

Rubberduck

Rubberduck is probably the most prominent free VBA add-in. It covers a lot of functionality with the most important ones being:

• Refactoring: You can easily rename functions and add, remove and reorder function arguments.
• Indentation: Automatically indent your code either on function, module or project level.
• Navigation: Rubberduck’s Code Explorer is a modern replacement for the built-in Project Explorer. It doesn’t stop at the module level but allows to quickly jump to a specific function or variable declaration. Very useful if you have modules with a lot of code and many functions!
• Code Inspection: Rubberduck has multiple categories of code inspection that point out possible issues and allow you to improve your code quality with usually a single click. For example, it shows you if you use a variable that is not assigned, if you have unused procedures or if a parameter is implicitly passed by reference. Rubberduck also has a Find all references functionality that makes it easy to see where a certain function is called from.
• Bulk import and export of all VBA modules: Without Rubberduck, you have to export/import each VBA module separately. In Rubberduck’s Code Explorer, you can use Import... and Export Project... respectively. Alternative: If bulk import/export is all you need, you can also use the Excel VBA Developer Tools.

Tip: By default, Rubberduck shows a splash screen every time you startup the VBA editor. You can disable that by going to Rubberduck > Settings > General Settings, then uncheck Show splash screen at startup.

The following screenshot shows Rubberduck’s Refactor > Reorder Parameters dialog:

Version Control

SourceTree with Git XL

The fact that VBA code is embedded in the Excel workbook itself makes it a difficult task to get it under source control. Many users export/import their modules manually or halfway automatically (see also How to use Git hooks to version-control your Excel VBA code) and track the exported text file with a version control system like Git. However, for many professional programmers, this is too cumbersome and error prone.

Fortunately, Atlassian’s free Git desktop client SourceTree together with xltrail’s free Git extension Git XL proves to be a powerful combo: You just have to track your Excel workbook with Git and SourceTree will show you the diff - no error prone exporting/importing of VBA modules required! Instead of SourceTree you can also use the command prompt if you prefer (git diff etc.). To get started, you’ll need to

• install Git XL
• install SourceTree (min. version 3.1.3).

If you have setup things correctly, coding in VBA is suddenly a lot more fun:

External editors

Sometimes it may be easier to edit the source code outside of the VBA editor. This goes hand in hand with exporting/importing your source files (e.g. by using Rubberduck’s bulk import/export functionality, see above). One of the advantages is that you get line numbers with your VBA code, something that is impossible to do in the VBA editor. Some commercial add-ins write line numbers directly into the source code, a procedure that is not recommendable as they don’t automatically update and don’t play well with source control. Visual Studio code and Sublime Editor have VBA extensions that give your code syntax highlighting.

Visual Studio Code

In the Visual Studio Code Extensions menu, search for VSCode VBA by Scott Spence and click the button to install it. This will give you proper syntax highlighting:

Sublime Editor

If you prefer to work with Sublime Editor, you can achieve the same by installing the package VBScript.

Unit tests

VBA doesn’t provide any unit testing framework. Nowadays there are a couple of possibilities though to fill that gap. This section is merely a quick overview. Because of the importance of the subject in light of recent regulatory challenges around spreadsheet risk and governance, we will provide a detailed separate blog post on the topic.

xlwings

xlwings is a Python package to automate Excel. It allows you to easily leverage Python’s built-in unit testing framework with everything that comes with it. This makes writing unit and integration tests for Excel very easy. If you know a little Python or are willing to learn the very basics, this is the most powerful solution as you can leverage Python’s powerful ecosystem. xlwings allows you to keep the tests outside of the workbook in a separate Python file and you can go as far as to control Excel instances to get a clean test environment or open workbooks (e.g. to test Workbook_Open events). See Unit Tests For Microsoft Excel for an introduction. xlwings is also the only way that easily integrates into fully automated continuous integrations (CI) systems to make sure your tests are automatically run on a test server every time you commit a change to your Git repository.

Rubberduck

Rubberduck has a built-in test runner. It supports initializing and cleanup methods on a module and function level. Bernard Vukas wrote a good introductory tutorial about it. There is also a wiki page dedicated to the topic on Rubberduck’s GitHub repository.

vba-test

Tim Hall’s vba-test is inspired by Jasmine (a JavaScript test runner). It requires you to add a few classes to your Excel workbook and prints the test result to the immediate window. It also allows you to group tests into test suits with setup and teardown methods as well as a possibility to use test fixtures. To cope with floating point values it offers IsApproximate and NotApproximate test methods.

Any free tools for VBA developers that we missed? Let us know in the comments below!