VBA and GIT
To avoid having duplicate files and ulgy names (name(1) copy): 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 Next End Sub
https://christopherjmcclellan.wordpress.com/2014/10/10/vba-and-git/ ****IMPORTANT**** You have to enable "Microsoft Visual Basic for Applications Extensibility 5.3" in "Tools" -> "References" -> Add from available ***************** 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:\Documents\Folder\" 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 ################### *.xlsm *.xlam *.accdb *.accde *.accdr *.laccdb 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. 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. 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.
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 Next 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