Visual Basic and Source Control Made Easier
Visual Basic for Applications (VBA) doesn’t play well with source control, in particular because a VBA project is part of its parent document (Excel Workbook, PowerPoint Add-in, etc.). I’ll walk you through a few metaprogramming techniques that made it easier for me to keep a project under source control, build from source, and finally to automate the build.
Note: This solution only addresses code module and RibbonUI maintenance. Document-level changes (i.e., changes to a PowerPoint slide or Excel spreadsheet, etc.) might be another level of complexity, but I think it would not be terribly difficult to handle the entire file contents (as it’s essentially just text/XML) if needed.
If you’ve used other techniques to manage your VBA projects, I’d love to hear about them, so feel free to leave a comment below!
Problems with VBA and Source Control
While macro-enabled files and add-ins are nominally binaries (XLAM, PPTM, etc.), under the hood they’re just ZIP archives, which contain folders , which in turn contain XML and other resource files necessary to render in the host application.
Maybe the first thing you’ll try is to commit your “saved” PPAM or XLSM to source control. You’ll quickly learn that you can’t diff the binaries in any meaningful way. That means you can’t readily review what code has changed!
OK, so you save the ZIP archive instead. But the vbaProject.bin which contains the actual code modules is also a binary, so while you’re marginally closer, you still can’t diff your code modules.
Metaprogramming: Exporting Modules to Local Repo
One solution is to export your VBA code modules and keep them in a git repository. As you make changes to your code (e.g., while debugging or developing with the VB IDE in Excel or whatever), you’ll need to export the changed modules to your repo folder and then make your commits.
This isn’t particularly onerous if your application consists of one or two modules, but if you’ve more than a few .bas, .frm, and .cls modules, it becomes a major pain point to do this manually. To make this step a bit easier, use or modify Ron De Bruin’s code to ExportModules to your local repo.
Now your VBA code is reasonably under source control.
Building your VBA Project from Source Control
When you’re ready to build a new version, simply open a new Excel file (or PowerPoint Presentation, etc.), and use Ron’s ImportModules to bring in the latest code from your repo. Finally, compile and save the project down to the output XLAM/PPAM/etc.
I didn’t even like to do this part manually — if memory serves me, the inclusion of the RibbonXML CustomUI components were still problematic, as that had to be managed via another application. I kept the Ribbon XML under source control, but it still needed to be added manually to the file.
Automating your VBA Project Build with Python
The solution I came up with was a python script that handled the entire build of the PPTM/PPAM. (Note: This script was written in python 2.7 so some changes may be needed if you use python 3.)
At the top of the module, I’ve listed the path for the vba modules, and other required items that will be built into the output file. If I was still using this script, or wanted to really operationalize it, I’d probably send those via sys.argv, but those items were not likely to change for my single use-case. Likewise, this script is configured to build PPAM outputs, but with minimal change you could adapt it for Excel or Word.
# NB: include statements are omitted in this example, for brevity :param vba_source_control_path: specify the local folder which contains the modules to be imported :param output_path: specify the destination path & filename (.pptm) for the build file :param copy_path: a temporary copy of the .ZIP archive must be created for read/write :param ribbon_xml_path: specify the destination path & filename for the Ribbon XML :param ribbon_logo_path: specify the path & name of the logo file "jdplogo.jpg" :param CustomUI: boolean, define this as False if you do NOT want to add the CustomUI xml/etc. vba_source_control_path = r"C:\Repos\CB\ChartBuilder\VBA\ChartBuilder_PPT\Modules" ribbon_xml_path = r"C:\Repos\CB\ChartBuilder\VBA\ChartBuilder_PPT\Ribbon XML\ribbon_xml.xml" ribbon_logo_path = r"C:\Repos\CB\ChartBuilder\VBA\ChartBuilder_PPT\Ribbon XML\jdplogo.jpg" output_path = r"C:\debug\output.pptm" copy_path=r'C:\debug\copy.zip' customUI = True
The ref_dict function is a bit of a hack, and in hindsight I should determine the VBA Project References programmatically as well, but this part was also very unlikely to change, and the path of least resistance was just to hardcode the known references. You’ll definitely need to modify this function to include your specific Reference names & paths.
Ultimately, keeping your VBA under source control is still a bit of a pain, but by using some metaprogramming techniques to assist with managing the modules and building your output macro-enabled Microsoft files or add-ins, it is (hopefully) less painful!
A Quick Note on PowerPoint VBA Development
There are some additional difficulties if you’re developing PowerPoint. If you’re used to developing VBA in Excel (as most people do), these points may come as a surprise!
- By default, you can’t enter Debug mode in PowerPoint Add-Ins. This can be overridden by a registry setting.
- PowerPoint does not let you “Save” or “Save As” from a PPAM, even with the registry enabled to Debug Add-Ins.
- Going from memory here, Excel does allow you to save a project that won’t compile. PowerPoint strictly requires compilation. This is a good thing, but it’s another “gotcha” if you’re new to PowerPoint VBA.
What this means is that you’ll need to develop against a PPTM and save down to PPAM to distribute it. And while you can debug the PPAM and even make changes (except those altering state or the Ribbon’s callback hooks, or anything that requires reloading the RibbonUI generally), you’ll need to Export those changes to your local git repo in order to rebuild a new version of the Add-In.