How to manage multiple distributed workbooks with VBA code using a central repository

Question

Within VBA for excel, I have multiple workbooks that rely on the same code. It’s a pain to update them all. How can I import modules from a network folder at runtime, so that I can save changes once, and have all the files auto update?

Answer 1

Here is what I came up with. If you have an idea to improve my implementation, please comment. If you have your own solution you think is better, please add your own answer! Thanks!

In the "ThisWorkook" module:

Option Explicit
'Requires reference to "Microsoft Visual Basic for Applications Extensibility"
Const RepPath As String = "X:\MyNetworkLocation\"
Const Deprecator As String = "_DEP"
    
Private Sub Workbook_Open()
    'VBA has trouble managing the removal and adding of code modules
    'within a single procedure, so we'll handle this in two steps.
    LoadCode
    RemoveDeprecated

    'Using Application.Ontime to get around potential compile errors caused by
    'subs/functions being referenced in modules that don't get loaded until runtime
    Application.OnTime Now(), "DoSomething"
    Application.OnTime Now(), "DoSomethingElse"
End Sub

Private Sub LoadCode()
    'This sub loads code from a central repository directory
    Dim vbP As VBIDE.vbProject
    Dim vbC As VBIDE.VBComponent
    Dim FileName As String
    Dim CodeName As String
    
    Set vbP = Application.ThisWorkbook.vbProject

    FileName = Dir(RepPath)
        Do While Len(FileName) > 0
            'get the module name without the file extension
            CodeName = Left(FileName, InStrRev(FileName, ".") - 1)
            Select Case CodeName
                'Using Select Case to ignore certain modules...
                Case "ThisWorkbook"
                'do nothing
                Case "CodeLoader"
                'do nothing
                Case Else
                'test if module exists in VB Project
                    On Error Resume Next
                    Set vbC = vbP.VBComponents(CodeName)
                    On Error GoTo 0
                    'if the module already exists, we need to remove it
                    'VBA struggles with doing this within a single procedure,
                    'so for now, we'll just rename it
                    If Not vbC Is Nothing Then vbC.Name = vbC.Name & Deprecator
                    'load the new code module
                    vbP.VBComponents.Import RepPath & FileName
            End Select
            'reset variables
            Set vbC = Nothing
            CodeName = ""
            'next file
            FileName = Dir
        Loop

End Sub

Private Sub RemoveDeprecated()
    'This sub removes the deprecated code modules that we previously renamed
    Dim vbP As VBIDE.vbProject
    Dim vbC As VBIDE.VBComponent
    Set vbP = Application.ThisWorkbook.vbProject
    
    For Each vbC In vbP.VBComponents
        If InStr(1, vbC.Name, Deprecator) > 0 Then vbP.VBComponents.Remove vbC
    Next

End Sub