excelvbadistributed

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


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?


Solution

  • 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