Rubberduck is probably the most prominent free VBA add-in. It covers a lot of functionality with the most important ones being:
Find all referencesfunctionality that makes it easy to see where a certain function is called from.
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:
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.
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!