Excel VBA 代码模块的源代码管理

Source control of Excel VBA code modules

提问by Jon Mills

I'd like to be able to source control my Excel spreadsheet's VBA modules (currently using Excel 2003 SP3) so that I can share and manage the code used by a bunch of different spreadsheets - and therefore I'd like to re-load them from files when the spreadsheet is opened.

我希望能够对我的 Excel 电子表格的 VBA 模块（目前使用 Excel 2003 SP3）进行源代码控制，以便我可以共享和管理一堆不同电子表格使用的代码 - 因此我想重新加载它们从电子表格打开时的文件。

I've got a module called Loader.bas, that I use to do most of the donkey work (loading and unloading any other modules that are required) - and I'd like to be able to load it up from a file as soon as the spreadsheet is opened.

我有一个名为 Loader.bas 的模块，我用它来完成大部分工作（加载和卸载所需的任何其他模块） - 我希望能够尽快从文件中加载它当电子表格打开时。

I've attached the following code to the Workbook_Open event (in the ThisWorkbook class).

我已将以下代码附加到 Workbook_Open 事件（在 ThisWorkbook 类中）。

Private Sub Workbook_Open()
    Call RemoveLoader
    Call LoadLoader
End Sub

Where RemoveLoader (also within the ThisWorkbook class) contains the following code:

其中 RemoveLoader（也在 ThisWorkbook 类中）包含以下代码：

Private Sub RemoveLoader()
    Dim y As Integer
    Dim OldModules, NumModules As Integer
    Dim CompName As String

    With ThisWorkbook.VBProject
        NumModules = ThisWorkbook.VBProject.VBComponents.Count
        y = 1
        While y <= NumModules
            If .VBComponents.Item(y).Type = 1 Then
                CompName = .VBComponents.Item(y).Name
                If VBA.Strings.InStr(CompName, "Loader") > 0 Then
                    OldModules = ThisWorkbook.VBProject.VBComponents.Count
                    .VBComponents.Remove .VBComponents(CompName)
                    NumModules = ThisWorkbook.VBProject.VBComponents.Count
                    If OldModules - NumModules = 1 Then
                        y = 1
                    Else
                        MsgBox ("Failed to remove " & CompName & " module from VBA project")
                    End If
                End If
            End If
            y = y + 1
        Wend
    End With
End Sub

Which is probably a bit overcomplicated and slightly crude - but I'm trying everything I can find to get it to load the external module!

这可能有点过于复杂且略显粗糙 - 但我正在尽我所能让它加载外部模块！

Often, when I open the spreadsheet, the RemoveLoader function finds that there's a "Loader1" module already included in the VBA project that it is unable to remove, and it also fails to load the new Loader module from the file.

通常，当我打开电子表格时，RemoveLoader 函数会发现 VBA 项目中已经包含一个无法删除的“Loader1”模块，并且它也无法从文件中加载新的 Loader 模块。

Any ideas if what I'm trying to do is possible? Excel seems very fond of appending a 1 to these module names - either when loading or removing (I'm not sure which).

如果我想要做的事情是可能的，有什么想法吗？Excel 似乎非常喜欢在这些模块名称后附加 1 - 无论是在加载还是删除时（我不确定是哪个）。

采纳答案by DaveParillo

Look at the VBAMaven page. I have a homegrown solution that uses the same concepts. I have a common library with a bunch of source code, an ant build and an 'import' VB script. Ant controls the build, which takes a blank excel file and pushes the needed code into it. @Mike is absolutely correct - any duplicate module definitions will automatically have a number appended to the module name. Also, class modules (as in Sheet and ThisWorkbook) classes require special treatment. You can't create those modules, you have to read the input file and write the buffer into the appropriate module. This is the VB script I currently use to do this. The section containing @ delimited text (i.e. @build file@) are placeholders - the ant build replaces these tags with meaningful content. It's not perfect, but works for me.

查看 VBAMaven 页面。我有一个使用相同概念的本土解决方案。我有一个带有一堆源代码、一个 ant 构建和一个“导入”VB 脚本的公共库。Ant 控制构建，它接受一个空白的 excel 文件并将所需的代码推送到其中。@Mike 是绝对正确的 - 任何重复的模块定义都会自动在模块名称后附加一个数字。此外，类模块（如 Sheet 和 ThisWorkbook 中的）类需要特殊处理。您无法创建这些模块，您必须读取输入文件并将缓冲区写入适当的模块。这是我目前用来执行此操作的 VB 脚本。包含@分隔文本（即@build file@）的部分是占位符——ant build用有意义的内容替换这些标签。它并不完美，但对我有用。

''
' Imports VB Basic module and class files from the src folder
' into the excel file stored in the bin folder. 
'

Option Explicit

Dim pFileSystem, pFolder,  pPath
Dim pShell
Dim pApp, book

Dim pFileName

pFileName = "@build file@"

Set pFileSystem = CreateObject("Scripting.FileSystemObject")

Set pShell = CreateObject("WScript.Shell")
pPath = pShell.CurrentDirectory

If IsExcelFile (pFileName) Then
    Set pApp = WScript.CreateObject ("Excel.Application")
    pApp.Visible = False
    Set book = pApp.Workbooks.Open(pPath & "\build\" & pFileName)
Else
    Set pApp = WScript.CreateObject ("Word.Application")
    pApp.Visible = False
    Set book = pApp.Documents.Open(pPath & "\build\" & pFileName)
End If


'Include root source folder code if no args set
If Wscript.Arguments.Count = 0 Then
    Set pFolder = pFileSystem.GetFolder(pPath & "\src")
    ImportFiles pFolder, book
    '
    ' Get selected modules from the Common Library, if any
    @common path@@common file@
Else
    'Add code from subdirectories of src . . .
    If Wscript.Arguments(0) <> "" Then
        Set pFolder = pFileSystem.GetFolder(pPath & "\src\" & Wscript.Arguments(0))
        ImportFiles pFolder, book
    End If
End If





Set pFolder = Nothing
Set pFileSystem = Nothing
Set pShell = Nothing


If IsExcelFile (pFileName) Then
    pApp.ActiveWorkbook.Save
Else
    pApp.ActiveDocument.Save
End If

pApp.Quit
Set book = Nothing
Set pApp = Nothing


'' Loops through all the .bas or .cls files in srcFolder
' and calls InsertVBComponent to insert it into the workbook wb.
'
Sub ImportFiles(ByVal srcFolder, ByVal obj)
    Dim fileCollection, pFile
    Set fileCollection = srcFolder.Files
    For Each pFile in fileCollection
        If Right(pFile, 3) = "bas _
          Or Right(pFile, 3) = "cls _
          Or Right(pFile, 3) = "frm Then
            InsertVBComponent obj, pFile
        End If
    Next
    Set fileCollection = Nothing
End Sub


'' Inserts the contents of CompFileName as a new component in 
'  a Workbook or Document object.
'
'  If a class file begins with "Sheet", then the code is
'  copied into the appropriate code module 1 painful line at a time.
'
'  CompFileName must be a valid VBA component (class or module) 
Sub InsertVBComponent(ByVal obj, ByVal CompFileName)
    Dim t, mName
    t = Split(CompFileName, "\")
    mName = Split(t(UBound(t)), ".")
    If IsSheetCodeModule(mName(0), CompFileName) = True Then
        ImportCodeModule obj.VBProject.VBComponents(mName(0)).CodeModule, _
                         CompFileName
    Else
        If Not obj Is Nothing Then
            obj.VBProject.VBComponents.Import CompFileName
        Else
            WScript.Echo  "Failed to import " & CompFileName
        End If
    End If 
End Sub

''
' Imports the code in the file fName into the workbook object
' referenced by mName.
' @param target destination CodeModule object in the excel file
' @param fName file system file containing code to be imported
Sub ImportCodeModule (ByVal target, ByVal fName)
    Dim shtModule, code, buf    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set buf = fso.OpenTextFile(fName, ForReading, False, TristateUseDefault)
    buf.SkipLine
    code = buf.ReadAll

    target.InsertLines 1, code
    Set fso = Nothing
End Sub


''
' Returns true if the code module in the file fName
' appears to be a code module for a worksheet.
Function IsSheetCodeModule (ByVal mName, ByVal fName)
    IsSheetCodeModule = False
    If mName = "ThisWorkbook" Then
       IsSheetCodeModule = False
    ElseIf Left(mName, 5) = "Sheet" And _
       IsNumeric(Mid (mName, 6, 1)) And _
       Right(fName, 3) = "cls Then
       IsSheetCodeModule = True
    End If
End Function

''
' Returns true if fName has a xls file extension
Function IsExcelFile (ByVal fName)
    If Right(fName, 3) = "xls" Then
        IsExcelFile = True
    Else
        IsExcelFile = False
    End If 
End Function

回答by CodeKid

There is an excellent solution to the vba version control problem here: https://github.com/hilkoc/vbaDeveloper

这里有一个很好的解决 vba 版本控制问题的方法：https: //github.com/hilkoc/vbaDeveloper

The nice part about this is that it exports your code automatically, as soon as you save your workbook. Also, when you open a workbook, it imports the code.

这样做的好处是，一旦您保存工作簿，它就会自动导出您的代码。此外，当您打开工作簿时，它会导入代码。

You don't need to run any build scripts or maven commands and you don't need to make any changes to your workbooks. It works for all.

您不需要运行任何构建脚本或 maven 命令，也不需要对工作簿进行任何更改。它适用于所有人。

It has also solved the import problem where modules such as ModName are being imported as ModName1 into a duplicate module. The importing works as it should, even when doing it multiple times.

它还解决了诸如 ModName 之类的模块被作为 ModName1 导入到重复模块中的导入问题。导入工作正常，即使多次执行也是如此。

As a bonus, it comes with a simple code formatter, that allows you to format your vba code as you write it within the VBA Editor.

作为奖励，它带有一个简单的代码格式化程序，允许您在 VBA 编辑器中编写 vba 代码时对其进行格式化。

回答by user220879

I've been working on exactly this for months. I think I figured it out.

我已经为此工作了几个月。我想我想通了。

If the VB Project is trying to remove a module containing something in the call stack, it delays the removal until the call stack pops the module being replaced.

如果 VB 项目试图删除包含调用堆栈中某些内容的模块，它会延迟删除，直到调用堆栈弹出被替换的模块。

To avoid a module being in the call stack, launch your code with Application.OnTime

为避免模块在调用堆栈中，请使用 Application.OnTime 启动您的代码

Private Sub Workbook_Open()

    'WAS: module_library (1)

    Application.OnTime (Now + TimeValue("00:00:01")), "load_library_kicker_firstiter"

End Sub

If you are self-healing your code like I am, you'll also have to launch your code that overwrites the 'calling' code with that same strategy.

如果你像我一样自我修复你的代码，你还必须启动你的代码，用相同的策略覆盖“调用”代码。

I did not perform extensive testing yet, I am in total celebration mode, but this gets me extremely close to straightforward 99.9% self-healing code within a standalone .xls file without any other tricks

我还没有进行广泛的测试，我处于完全庆祝模式，但这让我非常接近独立 .xls 文件中的直接 99.9% 自我修复代码，没有任何其他技巧

回答by Mike Woodhouse

Usually the "Loader1" thing happens when Excel is asked to import a module and a module already exists with the same name. So if you import "Loader", then load it again and you'll get "Loader1". This would be because Excel doesn't know (or maybe just doesn't care) if it's really the same thing or a new chunk of functionality that just happens have the same module name, so it imports it anyway.

通常，当 Excel 被要求导入一个模块并且已经存在同名模块时，会发生“Loader1”事件。因此，如果您导入“Loader”，然后再次加载它，您将获得“Loader1”。这是因为 Excel 不知道（或者可能只是不关心）它是否真的是相同的东西，或者刚发生的新功能块具有相同的模块名称，因此它无论如何都会导入它。

I can't think of a perfect solution, but I think I'd be inclined to try putting the load/unload logic in an add-in - that Workbook_Open thing looks a little vulnerable and having it in all workbooks is going to be a huge pain if the code ever needs to change (never say never). The XLA logic might be more complex (trickier to trap the necessary events, for one thing) but at least it'll only exist in one place.

我想不出完美的解决方案，但我想我倾向于尝试将加载/卸载逻辑放在加载项中 - Workbook_Open 的东西看起来有点脆弱，并且将它放在所有工作簿中将是一个如果代码需要更改（永远不要说永远），那将是巨大的痛苦。XLA 逻辑可能更复杂（一方面更难以捕获必要的事件），但至少它只存在于一个地方。

回答by Vasil

Can't leave comment to comment

不能发表评论发表评论

There is an excellent solution to the vba version control problem here: https://github.com/hilkoc/vbaDeveloper

这里有一个很好的解决 vba 版本控制问题的方法：https: //github.com/hilkoc/vbaDeveloper

About saving custom VBAProjects using this XLAM. Try this in Build.bas:

关于使用此 XLAM 保存自定义 VBAProject。在 Build.bas 中试试这个：

'===============
Public Sub testImport()
    Dim proj_name As String
    Dim vbaProject As Object

    'proj_name = "VBAProject"
    'Set vbaProject = Application.VBE.VBProjects(proj_name)

    Set vbaProject = Application.VBE.ActiveVBProject
    proj_name = vbaProject.name

    Build.importVbaCode vbaProject
End Sub
'===============
Public Sub testExport()
    Dim proj_name As String
    Dim vbaProject As Object

    'proj_name = "VBAProject"
    'Set vbaProject = Application.VBE.VBProjects(proj_name)

    Set vbaProject = Application.VBE.ActiveVBProject
    proj_name = vbaProject.name

    Build.exportVbaCode vbaProject
End Sub
'===============

This will export/import Active VBA Project.

这将导出/导入活动的 VBA 项目。

回答by Malick

The following is an easy-to-implement answer if you don't need to export your VBA code automatically. Just Call the following sub and it will export (as text) the VBA code of the current active workbook in a subfolder named "VC_nameOfTheWorkBook". If your project is a .xlam, you need to temporarily set the IsAddin property to false. Then you can easily add the new subfolder to Git. It is a slight modification of the code found heremade by Steve Jansen. For a more complete solution see Ron de Bruin post.

如果您不需要自动导出 VBA 代码，以下是一个易于实施的答案。只需调用以下子程序，它将在名为“VC_nameOfTheWorkBook”的子文件夹中导出（作为文本）当前活动工作簿的 VBA 代码。如果您的项目是 .xlam，则需要临时将 IsAddin 属性设置为 false。然后您可以轻松地将新的子文件夹添加到 Git。这是对Steve Jansen在这里找到的代码的轻微修改。有关更完整的解决方案，请参阅 Ron de Bruin帖子。

You need to set a reference to "Microsoft Visual Basic For Applications Extensibility 5.3" and to "Microsoft Scripting Runtime" in the VBE Editor.

您需要在 VBE 编辑器中设置对“Microsoft Visual Basic For Applications Extensibility 5.3”和“Microsoft Scripting Runtime”的引用。

Public Sub ExportVisualBasicCode()
    Const Module = 1
    Const ClassModule = 2
    Const Form = 3
    Const Document = 100
    Const Padding = 24
    Dim VBComponent As Object
    Dim path As String
    Dim directory As String
    Dim extension As String
    Dim fso As New FileSystemObject
    directory = ActiveWorkbook.path & "\VC_" & fso.GetBaseName(ActiveWorkbook.Name)
    If Not fso.FolderExists(directory) Then
        Call fso.CreateFolder(directory)
    End If
    Set fso = Nothing
    For Each VBComponent In ActiveWorkbook.VBProject.VBComponents
        Select Case VBComponent.Type
            Case ClassModule, Document
                extension = ".cls"
            Case Form
                extension = ".frm"
            Case Module
                extension = ".bas"
            Case Else
                extension = ".txt"
        End Select

        On Error Resume Next
        Err.Clear

        path = directory & "\" & VBComponent.Name & extension
        Call VBComponent.Export(path)

        If Err.Number <> 0 Then
            Call MsgBox("Failed to export " & VBComponent.Name & " to " & path, vbCritical)
        Else
            Debug.Print "Exported " & Left$(VBComponent.Name & ":" & Space(Padding), Padding) & path
        End If

        On Error GoTo 0
    Next
End Sub