如何使用中央存储库通过 VBA 代码管理多个分布式工作簿

Question

在 VBA for excel 中，我有多个依赖相同代码的工作簿。将它们全部更新是一件痛苦的事情。如何在运行时从网络文件夹导入模块，以便我可以保存更改一次，并让所有文件自动更新？

Answer 1

这是我想出的。如果您有改进我的实施的想法，请发表评论。如果您有自己认为更好的解决方案，请添加您自己的答案！谢谢！

在“ThisWorkook”模块中：

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