Last updated on May 27, 2019
The best free VBA developer tools
Table of Contents
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 referencesfunctionality 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
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
General Settings, then uncheck
Show splash screen at startup.
The following screenshot shows Rubberduck’s
Refactor > Reorder Parameters dialog:
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
If you have setup things correctly, coding in VBA is suddenly a lot more fun:
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:
If you prefer to work with Sublime Editor, you can achieve the same by installing the package VBScript.
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 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 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.
NotApproximate test methods.
Any free tools for VBA developers that we missed? Let us know in the comments below!