VBA and Git

We all know that we should be using source control, but many of us don’t. For those of us working with Visual Basic for Applications, our excuse is often, “I can’t. What am I supposed to do? Keep the whole document file in source control? Yeah.. right….”. The answer is an emphatic “No”.  We shouldn’t be keeping Excel workbooks or Access databases or Word documents under source control. We should be keeping our *.bas and *.cls files in a repository though.

This is easier said than done though. You may be aware that you can export and import files into a VBA Project. If you are, you are also aware that exporting and importing all of those files is going to be painful if you have more than a handful of classes or modules. Luckily, our old friend the Microsoft Visual Basic for Applications Extensibility 5.3 library is going to come to the rescue again. The VbComponents collection has a couple of really handy methods: Import and Remove.  We’ll find the Export method is part of the VbComponent class itself.  We’ll need to get all of the files out of our project so we can set up our repo, so let’s start with the export.

The following method will loop through all of the code modules in a VBA Project and export them to a specified file path.

Public Sub ExportSourceFiles(destPath As String)

Dim component As VBComponent
For Each component In Application.VBE.ActiveVBProject.VBComponents
If component.Type = vbext_ct_ClassModule Or component.Type = vbext_ct_StdModule Then
component.Export destPath & component.Name & ToFileExtension(component.Type)
End If

End Sub

Private Function ToFileExtension(vbeComponentType As vbext_ComponentType) As String
Select Case vbeComponentType
Case vbext_ComponentType.vbext_ct_ClassModule
ToFileExtension = ".cls"
Case vbext_ComponentType.vbext_ct_StdModule
ToFileExtension = ".bas"
Case vbext_ComponentType.vbext_ct_MSForm
ToFileExtension = ".frm"
Case vbext_ComponentType.vbext_ct_ActiveXDesigner
Case vbext_ComponentType.vbext_ct_Document
Case Else
ToFileExtension = vbNullString
End Select

End Function

This will work for any office application, but we only export standard modules and classes. I do this because we can’t import the code behinds of Forms, Worksheets, and the “ThisWorkbook” class back in. This is a drawback, but I find it to be acceptable because it encourages the use of classes and the separation of concerns. There shouldn’t be very much code in a code behind to begin with. Not being able to place that code into version control actively encourages us to put important code in a class or module that can be. Take note that the ToFileExtension function returns an empty string for objects that aren’t supported for import.

Now we can export all of the code in our project from the Immediate Window, very much like using a command line tool. Just type the name of the sub into the window (intellisense works here) and supply it a file path to export to. Pressing the Enter key will run the subroutine.


ExportSourceFiles "C:\Users\UserId\documents\MyProject\" in the Immediate Window


Now you can use this directory to set up a new Git Repository.  Seriously. Follow the link. I’ll wait.

Okay. You’re back? Good. Now you have your initial project under source control. Congratulations! You’re half way there! We just need to add a few lines to the .gitignore file. The .gitignore file tells the repository which files not to track. By ignoring office document files, we can keep our project in the directory with the rest of our repo, without worrying about Git tracking the changes. Here are the entries I’ve been using in my .gitignore file.

## Microsoft Office


The next thing we need to be able to do is remove all of the code from our project. If we don’t remove the code before importing from our local repo, we’ll end up with doubles of all of the modules. Things with names like “Car(1)” and “Extensions(1)”. It’s ugly, but it’s pretty easy to clean our project out with a routine very similar to ExportSourceFiles. The important thing to note here is that we don’t want to remove the DevTools.bas module that contains the code for importing and exporting our other code. I don’t know what would happen if we removed the module that was currently running, but I’d rather not find out. If you’re feeling brave, please let me know what happens.

Public Sub RemoveAllModules()
Dim project As VBProject
Set project = Application.VBE.ActiveVBProject

Dim comp As VBComponent
For Each comp In project.VBComponents
If Not comp.Name = "DevTools" And (comp.Type = vbext_ct_ClassModule Or comp.Type = vbext_ct_StdModule) Then
project.VBComponents.Remove comp
End If
End Sub

Finally, we’ll need some code to import modules back into our project after we’re pulled an update from our remote repository. Again, this is called from the Immediate Window and needs to be supplied the path to your local repo.

Public Sub ImportSourceFiles(sourcePath As String)
Dim file As String
file = Dir(sourcePath)
While (file <> vbNullString)
Application.VBE.ActiveVBProject.VBComponents.Import sourcePath & file
file = Dir
End Sub

Of course, all of this code is available from my repo on GitHub. So you can download it and try it for yourself. Just import the DevTools.bas file manually, and you’ll be able to use the tools in this article to install the rest of the repository into any VBA Project. Hopefully now that it’s easy to get your code into and out of your repository, you’ll be more likely to use source control and code like a pro.

Update: Rubberduck now has Git integration.

, , , , , , , ,

  1. #1 by retailcoder on October 11, 2014 - 12:53 pm

    You can programmatically remove the module that’s running, without crashing your IDE; the only thing is that, well, the module being gone, you better only do this will throw-away code… literally. So, good idea to not remove the currently executing module, especially if you intend to run it again in the near future šŸ˜‰


  2. #2 by Bonjournal on June 20, 2015 - 8:36 pm

    I created a module for such a thing. Check it out!



  3. #4 by Joshua Sorkin on September 4, 2015 - 11:21 pm

    Regarding not deleting and importing the “DevTools” module, how do you implement source control on this module? Or would that require using the new Rubberduck source control functionality?


    • #5 by Christopher J. McClellan on September 5, 2015 - 4:59 pm

      I hadn’t really thought about that at the time I wrote this. I suppose you could manually manage that file. It really shouldn’t change often. Obviously, the “DevTools” module isn’t necessary with the Rubberduck’s integrated GUI, but I imagine you’d face the same problem if you were leveraging the library. Just to be entirely clear, I don’t quite trust the GUI I built yet (v1.4.3 at time of writing). Use at your own risk, I know there are a few bugs to work out still.


  4. #6 by Erica Warren on October 28, 2015 - 10:30 pm

    Thanks for these great tools! I was able to adapt them for my use with git. However, I have some userforms and every time I export them git tracks the binary .frx file as modified, even when I haven’t changed anything. It sometimes tracks the .frm as changed, and when I diff it usually is just showing a blank line added. Any insight into what’s going on here or how I can only track files that I’ve actually changed? It would be much easier to be able to just `git commit -a -m` than to have to individually add the files i want to commit.


    • #7 by Christopher J. McClellan on October 28, 2015 - 10:48 pm

      Hi! First off, thank you! I’ve worked hard on this problem and hope to finish creating a stable add-in for the editor over the winter (I hope). Anyway, I noticed the .frx problem too. It’s pretty easily solved by adding an *.frx entry to the .gitignore file before your first commit. Being you’ve already committed though, you’ll need to remove the binary file from the repository with the -rm switch. A couple of google queries or some searches on StackOverflow should get you on your way.


  5. #8 by bjoernstiel on December 2, 2017 - 7:19 am

    Thanks for Rubberduck! An alternative approach to export a copy of the VBA code is to exploit git’s pre-commit hook. The idea is that you leave it up to git to export the VBA code (and add it to your commit) when you do git commit: https://www.xltrail.com/blog/auto-export-vba-commit-hook


  6. #9 by barney49 on June 29, 2020 - 5:34 pm

    I know that this is an old positing but just found it. Tried to find DevTools.bas in your GitHub and couldn’t find it. Also discovered that I needed the VBA Extensibilty library enabled for the code to compile. Might want to mention that. Other than that it is working well in Access VBA


  7. #10 by barney49 on June 29, 2020 - 5:43 pm

    Woops Overlooked the VBA Extensibility reference in the 2nd paragraph


  1. The Professional VBA Developer | Christopher J. McClellan
  2. Copying columns and formats on condition | DL-UAT
  3. VBA and Git the Sequel: This Time it’s Integrated | Christopher J. McClellan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: