VBA API — GOOD ENOUGH GITHUB INTEGRATION

Several years ago a consulting client started writing VBA for an in-Excel prototype and asked us how to get their VBA code into Github.

We gave them 25 lines of VBA code (listed below) as a stopgap. This code simply exported all their workbook's code to a designated folder whenever they saved their workbook. They could then use Github's Windows client to commit and push the VBA code to Github.

A few days later we asked about building a more comprehensive, push/pull mechanism, and they said, "We don't need it. This is good enough."

Since then, we've observed this to be true of many Data Analysts who are using Excel as a secondary tool and only write VBA sporadically. Although there are products that integrate source control into the VBA IDE, many Data Analysts are happy pairing our bare-bones, 25 line, 'on save' exporter with Github's Windows client for 'good enough' Github integration.

' 4 steps to take before this code will work:
'
'   1)  From Excel, enable 'Excel Options' -> 'Trust Center' -> 'Trust Center Settings' ->
'       'Macro Settings' -> 'Trust access to the VBA project object model'
'
'   2)  On the VBA IDE menu bar, go to 'Tools' -> 'References' and select these two libraries
'
'           a) 'Microsoft Visual Basic for Applications Extensibility 5.3'
'           b) 'Microsoft Scripting Runtime'
'
'       Note that, unlike many references in the list, these two are bedrock libraries that
'       will not make your Excel model fragile or non-shareable with others.
'
'   3)  Copy this code into the 'ThisWorkbook' module for **YOUR WORKBOOK'S** VBAProject. There will
'       be individual 'VBAProject' items for all open workbooks and perhaps for open add-ins. Use the
'       Project Explorer (press {ctrl + r} when within the VBA IDE) to find the correct 'ThisWorkbook'
'       module by looking at the parenthetical workbook name, i.e., 'VBAProject(my_model.xlsx)'.
'
'   4)  Edit the 'githubFolder' variable assignment to reference a folder path your Github Windows
'       client monitors.
'
'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim x               As VBIDE.VBComponent
    Dim githubFolder    As String
    Dim exports         As Scripting.Dictionary
    Dim key             As Variant
            
    Set exports = New Scripting.Dictionary
    
    ' TODO: change this to a path your Github Windows client monitors
    githubFolder = VBA.Environ$("userprofile") & "\Documents\GitHub\optimalxl\vba\src\"
        
    For Each x In ThisWorkbook.VBProject.VBComponents
        
        ' Don't include modules that are empty except for 'Option Explicit' at top
        If x.CodeModule.CountOfLines > 1 Then
            Select Case x.Type
                Case VBIDE.vbext_ComponentType.vbext_ct_ClassModule
                    
                    exports.Add x.Name, githubFolder & x.Name & ".bas"
                
                Case VBIDE.vbext_ComponentType.vbext_ct_ClassModule
                    
                    exports.Add x.Name, githubFolder & x.Name & ".cls"
                                
                Case VBIDE.vbext_ComponentType.vbext_ct_Document
                        
                    ' skip this module itself
                    If x.Name <> "ThisWorkbook" Then
                        exports.Add x.Name, githubFolder & x.Name & ".cls"
                    End If
                    
                Case Else
                    ' add forms if needed
            End Select
        End If
    Next
    
    For Each key In exports
        Set x = ThisWorkbook.VBProject.VBComponents(key)
        x.Export exports(key)
    Next
    
    ' tell Excel that we did not handle the file save so it can do so itself
    Cancel = False
End Sub